db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Conor66 <conor21e...@yahoo.ie>
Subject Week number of the year CASE statement query
Date Tue, 09 Aug 2011 23:24:22 GMT

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.


Mime
View raw message