Hi. On Tue, Aug 16, 2011 at 9:01 PM, Conor66 <conor21e...@yahoo.ie> wrote:
> > I understand the concept of the union and the row_number but how do i union > a temp table? Attached is my SQL file. Thanks for any help you can provide > me with. > http://old.nabble.com/file/p32274318/Main%2Bcode.sql Main+code.sql > > You really should consider the WEEK_OF_YEAR approach for two reasons: 1) I would like to see how it's done and 2) That case-statement is ugly. Can anyone share an example on how to do this, please? You will have to do the UNION on your base data - inside the count-aggregation - like this: create table temp_week_counts ( wkno int, asset int, priority int ); insert into temp_week_counts values (2, 1, 1); insert into temp_week_counts values (2, 2, 2); insert into temp_week_counts values (3, 1, 1); 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 -- /Morten
