sql server mseq: Hello and thanks for your efforts,
I have a table with Part, MonthSold, ItemsSold
i need to generate a view comparing this years sales to lastyears sales and
their differences by month.

this was my first shot at it:

SELECT DATENAME(month, inv_Monthly_Sales.MonthSold) AS Month,
SUM(inv_Monthly_Sales.ItemsSold) AS ThisYear, SUM(yr2.ItemsSold) AS LastYear,
SUM(inv_Monthly_Sales.ItemsSold - yr2.ItemsSold) AS
Comparison
FROM inv_Monthly_Sales INNER JOIN
inv_Monthly_Sales AS yr2 ON inv_Monthly_Sales.Part
= yr2.Part AND MONTH(inv_Monthly_Sales.MonthSold) = MONTH(yr2.MonthSold)
WHERE (YEAR(inv_Monthly_Sales.MonthSold) = @Yr) AND
(inv_Monthly_Sales.Part = @Part) AND (YEAR(yr2.MonthSold) = @Yr - 1) AND
(yr2.Part = @Part)
GROUP BY DATENAME(month, inv_Monthly_Sales.MonthSold),
MONTH(inv_Monthly_Sales.MonthSold), MONTH(yr2.MonthSold)
ORDER BY MONTH(inv_Monthly_Sales.MonthSold)

this works great if there exists data for all 12 months of both years.
if any month is missing on any year i get back nothing.
how can i make it generate the missing columns if there is no data for that
month
i.e. during march and april no gizmos were sold so there won't be a record
any sale for that month. i need a 0 placed in that column if it didn't exist.
i tried using isnull on the sum but it didn't help

please enlighten me if you can.

[quoted text, click to view]
SELECT COALESCE (m.MonthName, 'Total') AS MONTH,
ISNULL(SUM(yr.ItemsSold), 0) AS ThisYear, ISNULL(SUM(yr2.ItemsSold), 0) AS
LastYear,
SUM(ISNULL(yr.ItemsSold, 0) - ISNULL(yr2.ItemsSold,
0)) AS Comparison
FROM (SELECT 1 AS monthId, 'Jan' AS MonthName
UNION ALL
SELECT 2 AS Expr1, 'Feb' AS Expr2
UNION ALL
SELECT 3 AS Expr1, 'Mar' AS Expr2
UNION ALL
SELECT 4 AS Expr1, 'Apr' AS Expr2
UNION ALL
SELECT 5 AS Expr1, 'May' AS Expr2
UNION ALL
SELECT 6 AS Expr1, 'Jun' AS Expr2
UNION ALL
SELECT 7 AS Expr1, 'Jul' AS Expr2
UNION ALL
SELECT 8 AS Expr1, 'Aug' AS Expr2
UNION ALL
SELECT 9 AS Expr1, 'Sep' AS Expr2
UNION ALL
SELECT 10 AS Expr1, 'Oct' AS Expr2
UNION ALL
SELECT 11 AS Expr1, 'Nov' AS Expr2
UNION ALL
SELECT 12 AS Expr1, 'Dec' AS Expr2) AS m
LEFT OUTER JOIN
inv_Monthly_Sales AS yr ON yr.Part = @Part AND
YEAR(yr.MonthSold) = @Yr AND MONTH(yr.MonthSold) = m.monthId LEFT OUTER JOIN
inv_Monthly_Sales AS yr2 ON yr2.Part = @Part AND
YEAR(yr2.MonthSold) = @Yr - 1 AND MONTH(yr2.MonthSold) = m.monthId
GROUP BY m.MonthName WITH ROLLUP
ORDER BY Month

thanx in advance
[quoted text, click to view]
i"m including my final code it has one bug i can't get the sort order
straight
i.e. jan feb mar .......Total
it comes in alphabeticaly

[quoted text, click to view]

Hi slimshim,

You'll have to use a seperate table with all 12 months in it. You can
either create it on the fly as a derived table, or create a permanent
table in your DB as a one-time operation. In the query below, I presume
the latter; the query expects a table dbo.Months, with at least the two
columns MonthNo and MonthName.

SELECT m.MonthName AS MONTH,
SUM(yr.ItemsSold) AS ThisYear,
SUM(yr2.ItemsSold) AS LastYear,
SUM(yr.ItemsSold - yr2.ItemsSold) AS Comparison
FROM dbo.Months AS m
LEFT JOIN inv_Monthly_Sales AS yr
ON yr.Part = @Part
AND YEAR(yr.MonthSold) = @Yr
AND MONTH(yr.MonthSold) = m.MonthNo
LEFT JOIN inv_Monthly_Sales AS yr2
ON yr2.Part = @Part
AND YEAR(yr2.MonthSold) = @Yr - 1
AND MONTH(yr2.MonthSold) = m.MonthNo
GROUP BY m.MonthNo, m.MonthName
ORDER BY m.MonthNo;

Note: If your inv_Month_Sales table is indexed on the MonthSold column,
you should rewrite the date selection to the form MonthSold >= (first
day of month) AND MonthSold < (first day of next month). Let me know if
you need help with that.

Yet another note - the query is untested. Please see www.aspfaq.com/5006
if you prefer a tested reply, or if you want to post followup questions.

--
Hugo Kornelis, SQL Server MVP

[quoted text, click to view]

THanx for the help
I did go to that site But I couldn't figure out how to upload anything.


after i tried your suggested changed this is what my result looks like
Jan 0 0 0
Total 12 0 12
Feb 0 0 0
Mar 0 0 0
Apr 0 0 0
May 0 0 0
Jun 0 0 0
Jul 12 0 12
Aug 0 0 0
Sep 0 0 0
Oct 0 0 0
Nov 0 0 0
Dec 0 0 0

Total falls under jan not dec as it should.
I appreciate all your help.

I was wondering if I could use COMPUTE intead of coalese to get the total
heading to the bottom ?

THanx again

[quoted text, click to view]

Hi slimshim,

That's because you asked it to order on the month name column :-)

Change the last par tof the query to

GROUP BY m.MonthName WITH ROLLUP
ORDER BY MIN(m.MonthId)

I was first about to suggest to include MonthId in the GROUP BY, but I'm
not sure if the WITH ROLLUP option likes that. The workaround I chose is
to use an aggregate function for the ORDER BY.


If that doesn't work (you didn't follow the instructions I linked to
that would have enabled me to test before posting), then change the end
to

GROUP BY m.MonthID WITH ROLLUP
ORDER BY m.MonthID

and change the first line to read

SELECT MAX(COALESCE(m.MonthName, 'Total')) AS MONTH,

--
Hugo Kornelis, SQL Server MVP

[quoted text, click to view]

Hi slimshim,

You don't need to upload anything. The site describes the information
you need to supply to give people the best chance to help you. You just
read that site, assemble the information, then post that information in
your next question.

[quoted text, click to view]

I'm surpried - for a quick test on some scratch data, I got the total as
the very first line.

Try changing the ORDER BY clause to read either

GROUP BY m.MonthName WITH ROLLUP
ORDER BY GROUPING(m.MonthName), MIN(m.MonthId)

or

GROUP BY m.MonthID WITH ROLLUP
ORDER BY GROUPING(m.MonthID), m.MonthID

depending on which version of the query you are now using.

[quoted text, click to view]

COMPUTE is a deprecated feature and will be removed in a future version
of SQL Server. Don't use it for new work, and replace it if you have it
in existing code.

--
Hugo Kornelis, SQL Server MVP

I just wanted to thank you for your help.

[quoted text, click to view]