SQL запрос для сравнения продаж продукта по месяцам

Заявление по делу - мой лучший sql друг. Вам также нужна таблица времени, чтобы сгенерировать ваш 0 rev в оба месяца.

Предположения основаны на наличии следующих таблиц:

продажи: категория / выручка |год / Месяц

и

ТМ: год | месяц (заполняется всеми даты, необходимые для представления отчетности)

Пример 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

RETURNS:

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

RETURNS:

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 имеет синтаксис pivot, который также будет выполнять эту функцию.

Вот некоторые дополнительные ресурсы. CASE http://www.4guysfromrolla.com/webtech/102704-1.shtml SQL SERVER 2005 PIVOT http://msdn.microsoft.com/en-us/library/ms177410.aspx

@Christian -- markdown редактор -- UGH; особенно когда предварительный просмотр и окончательная версия вашего поста расходятся... @Christian -- полное внешнее соединение -- полное внешнее соединение отменяется тем фактом, что в предложении WHERE есть ссылки на SP1, а предложение WHERE применяется после JOIN. Чтобы выполнить полное внешнее объединение с фильтрацией по одной из таблиц, вам нужно поместить ваше предложение WHERE в подзапрос, чтобы фильтрация происходила до объединения, или попытаться построить все ваши критерии WHERE на предложении JOIN ON, что безумно некрасиво. Ну, на самом деле нет никакого красивого способа сделать это.

@Jonas: учитывая это:

Кроме того, отчет фактически относится к финансовому году - поэтому я хотел бы иметь пустые столбцы с 0 в обоих случаях, если бы не было продаж, скажем, в месяце 5 за 2007 или 2008 год.

и тот факт, что эта работа не может быть выполнена с помощью красивого запроса, я бы определенно попытался получить результаты, которые вы действительно хотите. Нет смысла делать уродливый запрос и даже не получать точные данные, которые вы действительно хотите. ;)

Итак, я бы предложил сделать это в 5 шагов:
1. создайте временную таблицу в формате, который должен соответствовать вашим результатам
2. заполните его двенадцатью строками, с 1-12 в столбце месяц
3. обновить столбец "This Year" используя вашу логику с пакетом обновления 1
4. обновить столбец "Last Year" используя вашу логику с пакетом обновления 2
5. выберите из временной таблицы

Конечно, я предполагаю, что я работаю из предположения, что вы можете создать хранимую процедуру для достижения этой цели. Технически вы можете запустить весь этот пакет встроенным, но такое уродство очень редко встречается. Если вы не можете сделать SP, я предлагаю вам вернуться к полному внешнему соединению через подзапрос, но это не даст вам строку, когда месяц не имел продаж ни в одном году.

Хитрость заключается в том, чтобы сделать полное соединение, с 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 (у вас есть один из них , не так ли?).

Насчет markdown-Да, это расстраивает. Редактор сделал предварительный просмотр моей таблицы HTML, но после публикации она исчезла - поэтому пришлось удалить все форматирование HTML из сообщения...

@kcrumley я думаю, что мы пришли к аналогичным выводам. Этот запрос легко становится действительно уродливым. Я действительно решил эту проблему, прежде чем прочитать ваш ответ, используя аналогичный (но все же другой подход). У меня есть доступ к созданию хранимых процедур и функций в базе данных отчетов. Я создал табличную функцию, принимающую категорию продукта и финансовый год в качестве параметра. Исходя из этого, функция заполнит таблицу, содержащую 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 довольно грязная! Но, по крайней мере, это работает - что делает меня счастливым на данный момент ;)

Я могу ошибаться, но разве вы не должны использовать полное внешнее соединение вместо простого левого соединения? Таким образом, вы получите 'empty' столбцов из обеих таблиц.

http://en.wikipedia.org/wiki/Join_ (SQL)#Full_outer_join

Используя 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