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
