Fill up missing months.

Hi,
I've got this resultset:
year month group value
2005 5 a 10
2005 6 a 20
2005 1 b 15
2005 3 b 16
2005 9 c 15
and now I need to fill it up with "missing" months -- like this:
year month group value
2005 1 a 0
2005 2 a 0
2005 3 a 0
2005 4 a 0
2005 5 a 10
2005 6 a 20
2005 7 a 0
2005 8 a 0
2005 9 a 0
2005 10 a 0
2005 11 a 0
2005 12 a 0
2005 1 b 15
2005 2 b 0
2005 3 b 16
2005 4 b 0
2005 5 b 0
2005 6 b 0
2005 7 b 0
2005 8 b 0
2005 9 b 0
2005 10 b 0
2005 11 b 0
2005 12 b 0
2005 1 c 0
2005 2 c 0
2005 3 c 0
2005 4 c 0
2005 5 c 0
2005 6 c 0
2005 7 c 0
2005 8 c 0
2005 9 c 15
2005 10 c 0
2005 11 c 0
2005 12 c 0
Anything I can think of is some cursor or loop-oriented approach with a
little help of "Dates" table. Maybe someone can come up with more
ellegant, set oriented solution.
Thanks in advance
MaciekHi Maciek !
Use a calendar table:
http://www.aspfaq.com/show.asp?id=2519
HTH, Jens Suessmeyer.|||Create a Calendar table and populate it with all the dates you will
ever need (a calendar I'm using right now has 100 years worth). Once
you've done that you can just outer join it in your query.
You'll may still use a loop to populate the calendar but you only need
to do that once and do it at install time rather than runtime.
David Portas
SQL Server MVP
--|||DECLARE @.StartYear SMALLINT , @.EndYear SMALLINT
SELECT @.StartYear = 2004 , @.EndYear = 2005
SELECT
Years.N AS [Year]
, Months.N AS [Month]
FROM
( SELECT * FROM tblNumbers WHERE N BETWEEN @.StartYear AND @.EndYear ) Years
CROSS JOIN
( SELECT * FROM tblNumbers WHERE N BETWEEN 1 AND 12 ) Months
ORDER BY
[Year]
, [Month]
Then right outer join the table to your resultset.
HTH
"maciek" <maciek@.kolobrzeg.com.pl> wrote in message
news:1129805888.703877.78890@.g47g2000cwa.googlegroups.com...
> Hi,
> I've got this resultset:
> year month group value
> 2005 5 a 10
> 2005 6 a 20
> 2005 1 b 15
> 2005 3 b 16
> 2005 9 c 15
> and now I need to fill it up with "missing" months -- like this:
> year month group value
> 2005 1 a 0
> 2005 2 a 0
> 2005 3 a 0
> 2005 4 a 0
> 2005 5 a 10
> 2005 6 a 20
> 2005 7 a 0
> 2005 8 a 0
> 2005 9 a 0
> 2005 10 a 0
> 2005 11 a 0
> 2005 12 a 0
> 2005 1 b 15
> 2005 2 b 0
> 2005 3 b 16
> 2005 4 b 0
> 2005 5 b 0
> 2005 6 b 0
> 2005 7 b 0
> 2005 8 b 0
> 2005 9 b 0
> 2005 10 b 0
> 2005 11 b 0
> 2005 12 b 0
> 2005 1 c 0
> 2005 2 c 0
> 2005 3 c 0
> 2005 4 c 0
> 2005 5 c 0
> 2005 6 c 0
> 2005 7 c 0
> 2005 8 c 0
> 2005 9 c 15
> 2005 10 c 0
> 2005 11 c 0
> 2005 12 c 0
> Anything I can think of is some cursor or loop-oriented approach with a
> little help of "Dates" table. Maybe someone can come up with more
> ellegant, set oriented solution.
> Thanks in advance
> Maciek
>|||Additionally,
You'd want to cross join the Year/Month results with a distinct list of the
(a,b,c) column - so you'd get a full list of Years/Months/ColumnX :)
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:435774d3$0$140$7b0f0fd3@.mistral.news.newnet.co.uk...
> DECLARE @.StartYear SMALLINT , @.EndYear SMALLINT
> SELECT @.StartYear = 2004 , @.EndYear = 2005
> SELECT
> Years.N AS [Year]
> , Months.N AS [Month]
> FROM
> ( SELECT * FROM tblNumbers WHERE N BETWEEN @.StartYear AND @.EndYear )
Years
> CROSS JOIN
> ( SELECT * FROM tblNumbers WHERE N BETWEEN 1 AND 12 ) Months
> ORDER BY
> [Year]
> , [Month]
> Then right outer join the table to your resultset.
> HTH
> "maciek" <maciek@.kolobrzeg.com.pl> wrote in message
> news:1129805888.703877.78890@.g47g2000cwa.googlegroups.com...
>|||Thanks Jens and David,
Sorry, I put it into wrong words when I wrote "(...) with a little help
of 'Dates' table." I thougt of the Calendar table you mentioned. I have
one in my db but the problem is I do not know how to write this outer
join :-( I mean if it was just one group ("a" for example) i would just
do a simple outer join on 'year' and 'month' columns, but since there
are a few groups I do not know how to make it work. How to repeat this
'twelve months' for each group. I think I'm not set oriented kind of
mind...|||Now I've got the clue!
Thanks!