2

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.

This is my code so far.

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)

This returns

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

But I would like it to return.

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

I'm using SQL Server 2008 R2 Sp1

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
gulbaek
  • 2,481
  • 13
  • 44
  • 65
  • possible duplicate: http://stackoverflow.com/questions/9691824/handling-non-existent-values-in-sql-query-expression-for-ssrs-chart/9692399#9692399 – Lee Tickett Mar 15 '12 at 07:49
  • 1
    Really don't like the idea of creating an extra table just to hold the Months. – gulbaek Mar 15 '12 at 07:53
  • What you could do is to create a table-valued function to return a table of months for given start and end dates. You could use MIN(OrderDate) and MAX(OrderDate) respectively. We use something similar in our environment. – tobias86 Mar 15 '12 at 07:55
  • And I havea Calendar table that is precalculated with dates, month, day of week etc. – TomTom Mar 15 '12 at 07:59

2 Answers2

2

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)
gulbaek
  • 2,481
  • 13
  • 44
  • 65
Michał Powaga
  • 22,561
  • 8
  • 51
  • 62
  • Getting this error. Cannot find either column "d" or the user-defined function or aggregate "d.year", or the name is ambiguous. where did you define it? – gulbaek Mar 15 '12 at 08:17
  • Thanks for your answer, I submittet and Edit of your answer, where I modified it to work, but think you should still have credit, for pointing me in the right direction. – gulbaek Mar 15 '12 at 08:34
1

Try:

;with CTE as 
(select 0 months_ago union all 
 select months_ago - 1 months_ago from CTE where months_ago > -11),
month_list as 
(select dateadd(MONTH, 
                months_ago, 
                dateadd(DAY, 
                        1-datepart(DAY,getdate()),
                        cast(GETDATE() as DATE))) month_start 
 from cte)
SELECT YEAR(ml.start_date) as 'Year', 
       MONTH(ml.start_date) as 'Month', 
       sum(Amount) as 'Units sold',[ProductNo]
FROM month_list ml
left join [Order] o 
       on o.OrderDate >= ml.start_date and 
          o.OrderDate < dateadd(MONTH, 1, ml.start_date)
left join [OrderLine] ol 
       on ol.OrderNo = o.OrderNo and ProductNo = @ProductNo
Group by ProductNo, YEAR(ml.start_date), Month(ml.start_date)
Order by ProductNo, YEAR(ml.start_date), Month(ml.start_date)