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.
On Tue, 14 Aug 2007 20:08:01 -0700, SLIMSHIM wrote:
>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:
(snip)
>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
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
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||"Hugo Kornelis" wrote:
[vbcol=seagreen]
> On Tue, 14 Aug 2007 20:08:01 -0700, SLIMSHIM wrote:
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
> (snip)
> 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
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
> You did a great job . thank you.
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
|||On Wed, 15 Aug 2007 17:02:04 -0700, SLIMSHIM wrote:
>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
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
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||"Hugo Kornelis" wrote:
> On Wed, 15 Aug 2007 17:02:04 -0700, SLIMSHIM wrote:
>
> 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
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>
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
Jan000
Total12012
Feb000
Mar000
Apr000
May000
Jun000
Jul12012
Aug000
Sep000
Oct000
Nov000
Dec000
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
SLIMSHIM
|||On Thu, 16 Aug 2007 19:38:04 -0700, SLIMSHIM wrote:
>THanx for the help
>I did go to that site But I couldn't figure out how to upload anything.
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.
>after i tried your suggested changed this is what my result looks like
>Jan000
>Total12012
>Feb000
>Mar000
>Apr000
>May000
>Jun000
>Jul12012
>Aug000
>Sep000
>Oct000
>Nov000
>Dec000
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.
>I was wondering if I could use COMPUTE intead of coalese to get the total
>heading to the bottom ?
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
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
