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