How to force 12 rows (1) per month

i have a datatable with Part , DateSold,ItemsSold

i want to show the sales for the last 12 months by month. if there were no sales for a given month i want the table to show 0.

here was my first attemp that only gives me data if all twelve months had sales.

SELECT MONTH(DateSold) AS Month, SUM(ItemsSold) AS ThisYear
FROM inv_Monthly_Sales

WHERE (YEAR(DateSold) = @.Yr - 1) AND (Part = @.Part)
GROUP BY MONTH(DateSold)
ORDER BY Month

i tried using isnull with the sum but that didn't work.

how can i force the table to always generate a dummy table of 12 months and then left join to it and in that way force it to give 12 moths of sales even when it does'n exist.

your help is greatly appreciated,

slimshim

Create a derived table that has 12 entries -- 1 for each month. Then LEFT JOIN your inv_MONTHLY_Sales table to this derived table based on the MONTH(DateSold). This will give you a row for each month.

Also, are you wanting the order that the months display to be dependent on the current month? For instance, the current month is August -- do you want the months to display Aug-Dec and then Jan-Jul? If so, you can use MONTH(getdate()) in combination with the month of the date with a MODULO operator as part of an ORDER BY.

The derived table might look something like this:

Code Snippet

select monthId
from ( select 1 as monthId union all select 2 union all
select 3 union all select 4 union all
select 5 union all select 6 union all
select 7 union all select 8 union all
select 9 union all select 10 union all
select 11 union all select 12
) as monthList
--order by monthId
order by (monthId + 12 - month(getdate())) % 12

/*
monthId
--
8
9
10
11
12
1
2
3
4
5
6
7
*/

If you don't need the top month to be the current month, just order by the "monthId" instead of the combination order that I listed.

Maybe something like:

Code Snippet

SELECT monthId AS Month,
SUM(ItemsSold) AS ThisYear
from ( select 1 as monthId union all select 2 union all
select 3 union all select 4 union all
select 5 union all select 6 union all
select 7 union all select 8 union all
select 9 union all select 10 union all
select 11 union all select 12
) as monthList
join inv_Monthly_Sales
on monthId = month(dateSold)
and (YEAR(DateSold) = @.Yr - 1)
AND (Part = @.Part)
GROUP BY monthId
ORDER BY monthId

|||I would stick to the generic order jan,feb, mar.....|||

Very well; maybe something like:

Code Snippet

SELECT monthId AS Month,
SUM(ItemsSold) AS ThisYear
from ( select 1 as monthId union all select 2 union all
select 3 union all select 4 union all
select 5 union all select 6 union all
select 7 union all select 8 union all
select 9 union all select 10 union all
select 11 union all select 12
) as monthList
join inv_Monthly_Sales
on monthId = month(dateSold)
and (YEAR(DateSold) = @.Yr - 1)
AND (Part = @.Part)
GROUP BY monthId
ORDER BY monthId

|||

THanks , I tried this code. but any year with no sales at all returns nothing. I was trying to get back all zeroes. I'm going to try it with is null on the sum and see if that works i"ll post back soon.

thanks again.

|||

I tried with isnull ... still didn't work here is the code i used. extras genereated by microsoft

SELECT monthList.monthId AS Month, ISNULL(SUM(inv_Monthly_Sales.ItemsSold), 0) AS ThisYear
FROM (SELECT 1 AS monthId
UNION ALL
SELECT 2 AS Expr1
UNION ALL
SELECT 3 AS Expr1
UNION ALL
SELECT 4 AS Expr1
UNION ALL
SELECT 5 AS Expr1
UNION ALL
SELECT 6 AS Expr1
UNION ALL
SELECT 7 AS Expr1
UNION ALL
SELECT 8 AS Expr1
UNION ALL
SELECT 9 AS Expr1
UNION ALL
SELECT 10 AS Expr1
UNION ALL
SELECT 11 AS Expr1
UNION ALL
SELECT 12 AS Expr1) AS monthList INNER JOIN
inv_Monthly_Sales ON monthList.monthId = MONTH(inv_Monthly_Sales.MonthSold) AND YEAR(inv_Monthly_Sales.MonthSold) = @.Yr AND
inv_Monthly_Sales.Part = @.Part
GROUP BY monthList.monthId
ORDER BY Month

thanks once again

|||MY FAULT! I didn't even do what I said! OH BROTHER! Change the INNER JOIN to a LEFT JOIN|||

That worked great. If no one ever told you your a genius consider it done now.Thanks again.

How can I add a Total rollup to the bottom ?

|||

Thank you for your kindness. Try adding a COMPUTE statement (I think) -- maybe add:

COMPUTE SUM(inv_Monthly_Sales.ItemsSold)

After your ORDER BY statement

|||

thnaks again . this is the error i get with the COMPUTE line added

COMPUTE clause#1,aggregare expresion #1,is not in the select list

|||

Oh, sorry; at this point I am not sure and am guessing. Maybe:

COMPUTE sum(ISNULL(SUM(inv_Monthly_Sales.ItemsSold), 0))

Will somebody please check me on this?

|||

I tried

GROUP BY monthList.monthId WITH ROLLUP

this worked except the total ended up as the first row in the result not the last.

|||

Query will return rows in any order depending on the execution plan. You need put an ORDER BY clause to get the desired order. To order results from rollup at the end, you can do below:

ORDER BY GROUPING(monthList.monthId)

|||

Thanks for the response. I tried it with that modification. here is the error message.

A Grouping function can only be specified when either CUBE or ROLLUP is specified in the GROUP BY clause|||

You might do something like this and then join as necessary:

Code Snippet

declare @.Months table(

MonthID tinyint

, MonthName varchar(9)

,MonthAbbreviation char(3)

)

declare @.dtStart datetime, @.i tinyint

select @.i =0 , @.dtStart = '01 Jan 2007'

while @.i < 12

begin

insert into @.Months

select @.i+1

, datename(MOnth,dateadd(m,@.i,@.dtSTart))

,left(datename(MOnth,dateadd(m,@.i,@.dtSTart)),3)

set @.i = @.i + 1

end

select * from @.Months