Re: Help with missing data in query



"Hugo Kornelis" wrote:

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.
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

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
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

.

Relevant Pages

  • Re: Establishing Precedence In ORDERBY Condition Causing Problems.
    ... inner joins - so you should either modify the query to use inner join ... Hugo Kornelis, SQL Server MVP ... WHEN 1 THEN registerDate ... from PersonalPhotos withunion select distinct email_address ...
    (comp.databases.ms-sqlserver)
  • Re: Return Part of a text field
    ... "Hugo Kornelis" wrote: ... >>field can contain a variety of data. ... > create table test (swNotes ntext) ... > union all ...
    (microsoft.public.sqlserver.mseq)