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
SELECT "Name" AS "strName", "Week_Number" AS "intWeekNum" , COUNT(*) AS
"Count" FROM(
SELECT "WOS"."strName" AS "Name", (CASE when
mod(year("dtmDateCompleted"), 4) <> 0 then (CASE
when day("dtmDateCompleted") > 0 AND day("dtmDateCompleted") < 8
AND month("dtmDateCompleted") = 1 then 1
when day("dtmDateCompleted") > 7 AND
day("dtmDateCompleted") < 15 AND month("dtmDateCompleted") = 1 then 2
when day("dtmDateCompleted") > 14 AND
day("dtmDateCompleted") < 22 AND month("dtmDateCompleted") = 1 then 3
when day("dtmDateCompleted") > 21 AND
day("dtmDateCompleted") < 29 AND month("dtmDateCompleted") = 1 then 4
when day("dtmDateCompleted") > 28 AND
day("dtmDateCompleted") <= 31 AND month("dtmDateCompleted") = 1 then 5
when day("dtmDateCompleted") >= 1 AND
day("dtmDateCompleted") < 5 AND month("dtmDateCompleted") = 2 then 5
when day("dtmDateCompleted") > 4 AND
day("dtmDateCompleted") < 12 AND month("dtmDateCompleted") = 2 then 6
when day("dtmDateCompleted") > 11 AND
day("dtmDateCompleted") < 19 AND month("dtmDateCompleted") = 2 then 7
when day("dtmDateCompleted") > 18 AND
day("dtmDateCompleted") < 26 AND month("dtmDateCompleted") = 2 then 8
when day("dtmDateCompleted") > 25 AND
day("dtmDateCompleted") <= 28 AND month("dtmDateCompleted") = 2 then 9
when day("dtmDateCompleted") >= 1 AND
day("dtmDateCompleted") < 5 AND month("dtmDateCompleted") = 3 then 9
when day("dtmDateCompleted") > 4 AND
day("dtmDateCompleted") < 12 AND month("dtmDateCompleted") = 3 then 10
when day("dtmDateCompleted") > 11 AND
day("dtmDateCompleted") < 19 AND month("dtmDateCompleted") = 3 then 11
when day("dtmDateCompleted") > 18 AND
day("dtmDateCompleted") < 26 AND month("dtmDateCompleted") = 3 then 12
end)
end) AS "Week_Number"
FROM "tblWorkOrder" "W"
INNER JOIN "tblWorkOrderStatus" "WOS" ON ("WOS"."id" =
"W"."intWorkOrderStatusID")
WHERE "dtmDateCompleted" IS NOT NULL
AND "W"."intWorkOrderStatusID" = 7
) as "Table"
GROUP BY "Week_Number", "Name"
--
View this message in context:
http://old.nabble.com/Week-number-of-the-year-CASE-statement-query-tp32230409p32230409.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
