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 Wed, 10 Aug 2011 10:29:14 GMT
On 10/08/2011 02:24, Conor66 wrote:
> Hi guys, firstly Id just like to say thank to everyone that has already
> responded to my questions, People on here are very helpful.
> What I am trying to do here is get the week number in the year that
> something happens and then count the amount of times this happens in each
> week, from 1 - 52. I have left out the rest of the CASE from weeks 13 to 52
> but that part works fine.
> The COUNT also works fine as can be seen from my attached screenshot.
> 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
> and so on.... Here is my code

Presumably you want to do a left join rather than an inner join. Also,
have you considered creating a function to get the week number from the
date? I would imagine it would be a bit more efficient (you could just
use Calendar.WEEK_OF_YEAR to pull it out), and certainly more readable.


  John English | My old University of Brighton home page is still here:
               | http://www.it.brighton.ac.uk/staff/je

View raw message