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 Fri, 12 Aug 2011 09:45:38 GMT
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)

View raw message