On Tue, Aug 16, 2011 at 9:01 PM, Conor66 <conor21eire@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 (
FROM <insert name of table with at least 53 rows in it>
) AS TR 
  WHERE R <= 53   /* some years have 53 */
SELECT wkno, asset, priority from TEMP_WEEK_COUNTS
) week_counts GROUP BY wkno