SQL Server:计算每月总销售额，包括空月 [英] SQL Server : calculate monthly total sales incl empty months

问题描述

我正在尝试计算某个产品在一个月内的总销售额，但我希望它包括任何空"月份(没有销售额)并且只选择最近 12 个月.

I'm trying to calculate the total sales of a product in a month, but I would like it to include any "empty" months (with no sales) and only select the latest 12 months.

这是我目前的代码.

declare
@ProductNo int

set @ProductNo = 1234

SELECT 
YEAR(o.OrderDate) as 'Year', MONTH(o.OrderDate) as 'Month', sum(Amount) as 'Units sold',[ProductNo]

  FROM [OrderLine] ol
  inner join [Order] o on ol.OrderNo = o.OrderNo
  where ProductNo = @ProductNo

  Group by ProductNo, YEAR(o.OrderDate), Month(o.OrderDate)
  Order by ProductNo, YEAR(o.OrderDate), Month(o.OrderDate)

返回

Year    Month   Units sold
2011    6       2
2011    10      1
2011    11      1
2012    2       1

但我希望它回来.

Year    Month   Units sold
2011    4       0
2011    5       0
2011    6       2
2011    7       0
2011    8       0    
2011    9       0
2011    10      1
2011    11      1
2011    12      0
2012    1       0
2012    2       2
2012    3       0

我使用的是 SQL Server 2008 R2 Sp1

I'm using SQL Server 2008 R2 Sp1

推荐答案

在我知道你有日历表之前我已经做了.我已经使用 master.dbo.spt_values 生成过去连续十二个月(包括当前).

I've done before I know that you have calendar table. I've used master.dbo.spt_values to generate last twelve consecutive months (including current).

    declare @ProductNo int

    set @ProductNo = 1234

select MONTH(d.date), YEAR(d.date), isnull(t.amnt, 0) as [Units sold] from (
    SELECT
        YEAR(o.OrderDate) as 'Year', 
        MONTH(o.OrderDate) as 'Month', 
        sum(Amount) as amnt,
        [ProductNo]
    FROM [OrderLine] ol
    inner join [Order] o on ol.OrderNo = o.OrderNo
    where ProductNo = @ProductNo
    group by ProductNo, YEAR(o.OrderDate), Month(o.OrderDate)
) t
right join (
    select dateadd(mm, -number, getdate()) as date
    from master.dbo.spt_values 
    where type = 'p' and number < 12
) d  on year(d.date) = t.[year] and month(d.date) = t.[month]
order by YEAR(d.date), MONTH(d.date)

这篇关于SQL Server:计算每月总销售额，包括空月的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！