Thanks for your reply. Yes I looked into Calendar.WEEK_OF_YEAR, I came across it after I wrote that CASE statement and it would definitely be a better way to do this. But Im wondering how to get the weeks where nothing happens, so that I can have zero values also, not just the weeks where something happens. Thanks again John English-2 wrote: > > 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. > > Regards, > > ------------------------------------------------------------------------ > John English | My old University of Brighton home page is still here: > | http://www.it.brighton.ac.uk/staff/je > ------------------------------------------------------------------------ > >
-- View this message in context: http://old.nabble.com/Week-number-of-the-year-CASE-statement-query-tp32230409p32234338.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
