That's a great idea! There is no need for a CASE because he's interested in a count in the end, so
declare global temporary table SESSION.all_weeknos(wkno int) ON COMMIT PRESERVE ROWS not logged; declare global temporary table SESSION.temp_week_counts (wkno int, asset int, priority int) ON COMMIT PRESERVE ROWS not logged; insert into SESSION.all_weeknos values (1), (2), .. (53); insert into SESSION.temp_week_counts values (2, 1, 1), (2, 2, 2), (3, 1, 1); SELECT aw.wkno, COUNT(wc.asset) from SESSION.all_weeknos aw LEFT JOIN SESSION.temp_week_counts wc ON aw.wkno=wc.wkno GROUP BY aw.WKNO; does the trick. Could you elaborate on your initial suggestion about using Calendar.WEEK_OF_YEAR instead of the ugly case-thing to make up for my temp_week_counts? (Sorry for hijacking the thread, but I am intrigued to see how this should be done) Thanks, On Sat, Aug 20, 2011 at 1:06 PM, John English <[email protected]>wrote: > On 18/08/2011 13:59, Morten Mikkelsen wrote: > >> SELECT wkno, COUNT(wkno) - 1 AS WeekCount from ( >> SELECT R as wkno, 0 as asset, 0 as priority FROM ( >> SELECT ROW_NUMBER() OVER () AS R, RULE.* >> FROM <insert name of table with at least 53 rows in it> >> ) AS TR >> WHERE R <= 53 /* some years have 53 */ >> UNION ALL >> SELECT wkno, asset, priority from TEMP_WEEK_COUNTS >> ) week_counts GROUP BY wkno >> > > How about something like (off the top of my head) > > INSERT INTO somewhere(somecolumn) VALUES(1),(2),(3),...,(53); > SELECT * FROM somewhere LEFT JOIN temp_week_counts ON wkno=somecolumn; > > ...and then as necessary replace "*" with a CASE to turn the right-hand > nulls into zeroes? > > > ------------------------------**------------------------------** > ------------ > John English | My old University of Brighton home page is still here: > | > http://www.it.brighton.ac.uk/**staff/je<http://www.it.brighton.ac.uk/staff/je> > ------------------------------**------------------------------** > ------------ > -- /Morten
