db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Re: Week number of the year CASE statement query
Date Sat, 20 Aug 2011 11:06:48 GMT
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
------------------------------------------------------------------------

Mime
View raw message