У меня есть представление базы данных «Ежемесячный статус», на котором мне нужно построить отчет. Данные в представлении выглядят примерно так:
Category | Revenue | Yearh | Month
Bikes 10 000 2008 1
Bikes 12 000 2008 2
Bikes 12 000 2008 3
Bikes 15 000 2008 1
Bikes 11 000 2007 2
Bikes 11 500 2007 3
Bikes 15 400 2007 4
... И так далее
Представление имеет категорию продукта, доход, год и месяц. Я хочу создать отчет, сравнивая 2007 и 2008 годы, показывая 0 за месяцы без продаж. Поэтому отчет должен выглядеть примерно так:
Category | Month | Rev. This Year | Rev. Last Year
Bikes 1 10 000 0
Bikes 2 12 000 11 000
Bikes 3 12 000 11 500
Bikes 4 0 15 400
Главное, на что нужно обратить внимание, это то, как в 1-м месяце продажи были только в 2008 году, и, следовательно, 0 в 2007 году. Кроме того, в 4-м месяце продаж нет только в 2008 году, следовательно, 0, в то время как продажи в 2007 году все еще отображаются.
Кроме того, отчет на самом деле за финансовый год - поэтому я хотел бы иметь пустые столбцы с 0 в обоих, если не было продаж в скажем 5-м месяце в 2007 или 2008 году.
Полученный запрос выглядит примерно так:
SELECT
SP1.Program,
SP1.Year,
SP1.Month,
SP1.TotalRevenue,
IsNull(SP2.TotalRevenue, 0) AS LastYearTotalRevenue
FROM PVMonthlyStatusReport AS SP1
LEFT OUTER JOIN PVMonthlyStatusReport AS SP2 ON
SP1.Program = SP2.Program AND
SP2.Year = SP1.Year - 1 AND
SP1.Month = SP2.Month
WHERE
SP1.Program = 'Bikes' AND
SP1.Category = @Category AND
(SP1.Year >= @FinancialYear AND SP1.Year <= @FinancialYear + 1) AND
((SP1.Year = @FinancialYear AND SP1.Month > 6) OR
(SP1.Year = @FinancialYear + 1 AND SP1.Month <= 6))
ORDER BY SP1.Year, SP1.Month
Проблема с этим запросом состоит в том, что он не будет возвращать четвертую строку в данных моего примера выше, поскольку у нас не было продаж в 2008 году, но мы фактически сделали это в 2007 году.
Вероятно, это распространенный запрос / проблема, но мой SQL устарел после столь длительной разработки. Любая помощь с благодарностью!
О, кстати, я использую SQL 2005 для этого запроса, так что если есть какие-либо полезные новые функции, которые могут помочь мне, дайте мне знать.
Заявление о ситуации - мой лучший друг. Вам также нужна таблица времени, чтобы сгенерировать 0 оборотов за оба месяца.
Допущения основаны на наличии следующих таблиц:
продажи: Категория | Доход | Год | Месяц
и
тм: год | Месяц (заполняется всеми датами, необходимыми для отчетности)
Пример 1 без пустых строк:
select
Category
,month
,SUM(CASE WHEN YEAR = 2008 THEN Revenue ELSE 0 END) this_year
,SUM(CASE WHEN YEAR = 2007 THEN Revenue ELSE 0 END) last_year
from
sales
where
year in (2008,2007)
group by
Category
,month
ВОЗВРАТ:
Category | Month | Rev. This Year | Rev. Last Year
Bikes 1 10 000 0
Bikes 2 12 000 11 000
Bikes 3 12 000 11 500
Bikes 4 0 15 400
Пример 2 с пустыми строками: я собираюсь использовать подзапрос (но другие могут этого не делать) и будет возвращать пустую строку для каждого продукта и комбинации год-месяц.
select
fill.Category
,fill.month
,SUM(CASE WHEN YEAR = 2008 THEN Revenue ELSE 0 END) this_year
,SUM(CASE WHEN YEAR = 2007 THEN Revenue ELSE 0 END) last_year
from
sales
Right join (select distinct --try out left, right and cross joins to test results.
product
,year
,month
from
sales --this ideally would be from a products table
cross join tm
where
year in (2008,2007)) fill
where
fill.year in (2008,2007)
group by
fill.Category
,fill.month
ВОЗВРАТ:
Category | Month | Rev. This Year | Rev. Last Year
Bikes 1 10 000 0
Bikes 2 12 000 11 000
Bikes 3 12 000 11 500
Bikes 4 0 15 400
Bikes 5 0 0
Bikes 6 0 0
Bikes 7 0 0
Bikes 8 0 0
Обратите внимание, что большинство инструментов отчетности будут выполнять эту кросс-таблицу или матричную функциональность, и теперь, когда я думаю об этом, SQL Server 2005 имеет синтаксис сводной таблицы, который также сделает это.
Вот некоторые дополнительные ресурсы. CASE http://www.4guysfromrolla.com/webtech/102704-1.shtml PIVOT для SQL SERVER 2005 http://msdn.microsoft.com/en-us/library/ms177410.aspx
Я могу ошибаться, но разве вы не должны использовать полное внешнее соединение, а не просто левое? Таким образом, вы будете получать «пустые» столбцы из обеих таблиц.
@Christian - редактор уценок - UGH; особенно если предварительный просмотр и окончательная версия вашего сообщения не согласны ... @Christian - полное внешнее объединение - полное внешнее объединение отменяется тем фактом, что в предложении WHERE имеются ссылки на SP1, а также применяется предложение WHERE. после ПРИСОЕДИНЕНИЯ. Чтобы выполнить полное внешнее объединение с фильтрацией по одной из таблиц, необходимо поместить предложение WHERE в подзапрос, чтобы фильтрация выполнялась до объединения, или попытаться встроить все критерии WHERE в предложение JOIN ON, которое безумно некрасиво Ну, на самом деле нет красивого способа сделать это.
@Jonas: Учитывая это:
Кроме того, отчет на самом деле за финансовый год - поэтому я хотел бы иметь пустые столбцы с 0 в обоих, если не было продаж в скажем 5-м месяце в 2007 или 2008 году.
и тот факт, что эта работа не может быть выполнена с красивым запросом, я определенно попытался бы получить результаты, которые вы действительно хотите. Нет смысла иметь уродливый запрос и даже не получать точные данные, которые вы на самом деле хотите. ;)
Итак, я бы посоветовал сделать это в 5 шагов:
1. создать временную таблицу в формате, который вы хотите, чтобы ваши результаты соответствовали
2. заполнить ее двенадцатью строками, с 1-12 в столбце месяца
3. обновить «В этом году» столбец "с использованием логики SP1
4. обновите столбец" Прошлый год "с помощью логики SP2
5. выберите из временной таблицы
Конечно, я предполагаю, что я работаю с предположением, что вы можете создать хранимую процедуру для достижения этой цели. С технической точки зрения, вы можете запустить всю эту серию, но такое уродство очень редко встречается. Если вы не можете создать SP, я предлагаю вам вернуться к полному внешнему объединению с помощью подзапроса, но он не даст вам ссоры, если месяц не имел продаж ни в каком году.
По поводу уценки - Да, это расстраивает. Редактор сделал предварительный просмотр моей HTML-таблицы, но после публикации она исчезла - поэтому пришлось удалить все HTML-форматирование из публикации ...
@kcrumley Я думаю, что мы пришли к аналогичным выводам. Этот запрос легко становится очень уродливым. Я действительно решил это, прежде чем читать ваш ответ, используя аналогичный (но все же другой подход). У меня есть доступ к созданию хранимых процедур и функций в базе данных отчетов. Я создал функцию Table Valued, принимая в качестве параметра категорию продукта и финансовый год. Исходя из этого, функция будет заполнять таблицу, содержащую 12 строк. Строки будут заполнены данными из представления, если доступны какие-либо продажи, если нет, то строка будет иметь 0 значений.
Затем я соединяю две таблицы, возвращаемые функциями. Поскольку я знаю, что на всех столах будет двенадцать столов, это намного проще, и я могу присоединиться к категории продуктов и месяцам:
SELECT
SP1.Program,
SP1.Year,
SP1.Month,
SP1.TotalRevenue AS ThisYearRevenue,
SP2.TotalRevenue AS LastYearRevenue
FROM GetFinancialYear(@Category, 'First Look', 2008) AS SP1
RIGHT JOIN GetFinancialYear(@Category, 'First Look', 2007) AS SP2 ON
SP1.Program = SP2.Program AND
SP1.Month = SP2.Month
Я думаю, что ваш подход, возможно, немного чище, так как функция GetFinancialYear довольно грязная! Но по крайней мере это работает - что делает меня счастливым сейчас;)
Хитрость заключается в том, чтобы выполнить FULL JOIN с ISNULL для получения соединенных столбцов из любой таблицы. Я обычно заключаю это в представление или производную таблицу, в противном случае вам также необходимо использовать ISNULL в предложении WHERE.
SELECT
Program,
Month,
ThisYearTotalRevenue,
PriorYearTotalRevenue
FROM (
SELECT
ISNULL(ThisYear.Program, PriorYear.Program) as Program,
ISNULL(ThisYear.Month, PriorYear.Month),
ISNULL(ThisYear.TotalRevenue, 0) as ThisYearTotalRevenue,
ISNULL(PriorYear.TotalRevenue, 0) as PriorYearTotalRevenue
FROM (
SELECT Program, Month, SUM(TotalRevenue) as TotalRevenue
FROM PVMonthlyStatusReport
WHERE Year = @FinancialYear
GROUP BY Program, Month
) as ThisYear
FULL OUTER JOIN (
SELECT Program, Month, SUM(TotalRevenue) as TotalRevenue
FROM PVMonthlyStatusReport
WHERE Year = (@FinancialYear - 1)
GROUP BY Program, Month
) as PriorYear ON
ThisYear.Program = PriorYear.Program
AND ThisYear.Month = PriorYear.Month
) as Revenue
WHERE
Program = 'Bikes'
ORDER BY
Month
Это должно соответствовать вашим минимальным требованиям - строки с продажами в 2007 или 2008 году, или в обоих случаях. Чтобы получить строки без продаж в любой год, вам просто нужно ВНУТРИ СОЕДИНИТЬСЯ в таблицу чисел 1-12 (у вас есть одна из них , не так ли?).
Используя pivot и Dynamic Sql, мы можем достичь этого результата
SET NOCOUNT ON
IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
DROP TABLE #TEMP
;With cte(Category , Revenue , Yearh , [Month])
AS
(
SELECT 'Bikes', 10000, 2008,1 UNION ALL
SELECT 'Bikes', 12000, 2008,2 UNION ALL
SELECT 'Bikes', 12000, 2008,3 UNION ALL
SELECT 'Bikes', 15000, 2008,1 UNION ALL
SELECT 'Bikes', 11000, 2007,2 UNION ALL
SELECT 'Bikes', 11500, 2007,3 UNION ALL
SELECT 'Bikes', 15400, 2007,4
)
SELECT * INTO #Temp FROM cte
Declare @Column nvarchar(max),
@Column2 nvarchar(max),
@Sql nvarchar(max)
SELECT @Column=STUFF((SELECT DISTINCT ','+ 'ISNULL('+QUOTENAME(CAST(Yearh AS VArchar(10)))+','+'''0'''+')'+ 'AS '+ QUOTENAME(CAST(Yearh AS VArchar(10)))
FROM #Temp order by 1 desc FOR XML PATH ('')),1,1,'')
SELECT @Column2=STUFF((SELECT DISTINCT ','+ QUOTENAME(CAST(Yearh AS VArchar(10)))
FROM #Temp FOR XML PATH ('')),1,1,'')
SET @Sql= N'SELECT Category,[Month],'+ @Column +'FRom #Temp
PIVOT
(MIN(Revenue) FOR yearh IN ('+@Column2+')
) AS Pvt
'
EXEC(@Sql)
Print @Sql
Результат
Category Month 2008 2007
----------------------------------
Bikes 1 10000 0
Bikes 2 12000 11000
Bikes 3 12000 11500
Bikes 4 0 15400