db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Morten Mikkelsen <mortenbr...@gmail.com>
Subject Re: Week number of the year CASE statement query
Date Sat, 20 Aug 2011 17:44:58 GMT
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 <john.foreign@gmail.com>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

Mime
View raw message