Блог

Я пытаюсь найти средние продажи, количество продаж и сумму продаж за каждый рабочий час в течение месяца. DepartmentID = 1 означает Обед и 3 означает доставку. Проблема, с которой я сейчас сталкиваюсь, заключается в том, что сводный результат добавляет нулевые строки, которые преобразуются в ноль из-за ISNULL. Я хочу преобразовать результат в двух строках в одну строку. Эта проблема возникает, когда я добавляю более одной агрегатной функции.

Любая помощь будет признательна.

Мой код таков:

 USE [sambapos]
GO
;with sales as (
SELECT
    DATEPART(YEAR, t.[Date]) as 'Year',
    DATEPART(MONTH, t.[Date]) as 'Month',
    DATEPART(HOUR, t.[Date]) as 'Hour',
    CASE
        WHEN t.DepartmentId = 1 THEN 'DineInSale'
        WHEN t.DepartmentId = 3 THEN 'DeliverySale'
    END AS 'DepartmentSale',
    CASE
        WHEN t.DepartmentId = 1 THEN 'DineInCount'
        WHEN t.DepartmentId = 3 THEN 'DeliveryCount'
    END AS 'DepartmentCount',
    CASE
        WHEN t.DepartmentId = 1 THEN 'DineInSum'
        WHEN t.DepartmentId = 3 THEN 'DeliverySum'
    END AS 'DepartmentSum',
    AVG(t.[TotalAmount]) as 'Avg Sale',
    SUM(t.[TotalAmount]) as 'Sum Sale',
    COUNT(t.[TotalAmount]) as 'Count'
FROM
    dbo.Tickets t
GROUP BY
    t.[DepartmentId],
    DATEPART(YEAR, t.[Date]),
    DATEPART(MONTH, t.[Date]),
    DATEPART(HOUR, t.[Date])
)   
SELECT 
    [Year], 
    [Month], 
    [Hour], 
    ISNULL([DineInSale], 0) as 'DineInSale', 
    ISNULL([DeliverySale], 0) as 'DeliverySale',
    ISNULL([DineInCount], 0) as 'DineInCount', 
    ISNULL([DeliveryCount], 0) as 'DeliveryCount',
    ISNULL([DineInSum], 0) as 'DineInSum', 
    ISNULL([DeliverySum], 0) as 'DeliverySum'
FROM
    sales
    PIVOT (
        SUM([Avg Sale])
        FOR [DepartmentSale] IN (
            [DineInSale], [DeliverySale]
        )
    ) as avg_pivot

    PIVOT (
        SUM([Count])
        FOR [DepartmentCount] IN (
            [DineInCount], [DeliveryCount]
        ) 
    ) as count_pivot

    PIVOT (
        SUM([Sum Sale])
        FOR [DepartmentSum] IN (
            [DineInSum], [DeliverySum]
        )
    ) as sum_pivot


WHERE
    [Year] = 2021
    AND 
    ([Hour] <= 3 OR [Hour] >= 21)
ORDER BY
    [Month] DESC, 
    CASE 
    WHEN [Hour] = 21 THEN -3
    WHEN [Hour] = 22 THEN -2
    WHEN [Hour] = 23 THEN -1
    ELSE [Hour] 
END ASC
GO