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 <[email protected]> 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.
