db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Conor66 <conor21e...@yahoo.ie>
Subject Re: Week number of the year CASE statement query
Date Tue, 16 Aug 2011 19:01:46 GMT

Hi Morten thanks for your reply. Sorry for my late response in getting back
to you, I have been sick for the last few weeks. 
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 

Morten Mikkelsen wrote:
> 
> On Wed, Aug 10, 2011 at 1:24 AM, Conor66 <conor21eire@yahoo.ie> wrote:
> 
>>
>> The problem lies in... I want to also include in my output the weeks
>> where
>> nothing happens, so in my case the output should read like
>> strName                 intWeekNum     Count
>> Closed, Completed         1                 0
>> Closed, Completed         2                 8
>> Closed, Completed         3                 0
>> Closed, Completed         4                 4
>>
> 
> You can add the missing weeks with a UNION with a resultset of (weekno,
> 0).
> See http://db.apache.org/derby/docs/dev/ref/rreffuncrownumber.html for an
> idea on how to generate numbers to use for week numbers.
> Add all weeks in a year, and remember to subtract one from your 'count' to
> get the right results.
> 
> Needless to say, you should switch to the built-in function to calculate
> week numbers (so they continue to be correct in the future years, also)
> -- 
> /Morten
> 
> 

-- 
View this message in context: http://old.nabble.com/Week-number-of-the-year-CASE-statement-query-tp32230409p32274318.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message