select drive_date,a.site_code,site_name,
projection,actual_mobile_hrs
from blood_drives a,sites b
where a.site_code = b.site_code
and drive_date = sysdate-1
and drive_cancelled is null
DRIVE_DATE|SITE_CODE|SITE_NAME|PROJECTION|ACTUAL_MOBILE_HRS
12/16/2013|CL04|Hanover Central High School|79|6
12/16/2013|CW02|Franciscan St. Anthony Health|16|4
12/16/2013|GE38|Healthtrack Sports Wellness|17|3
12/16/2013|LI10|AT&T - Lisle|10|4
12/16/2013|NL08|Trinity Lutheran Church|18|4
12/16/2013|OP02|Rich Central High School|68|5.5
12/16/2013|PL31|C.W. Avery Family Y.M.C.A.|17|3
12/16/2013|PL48|River View Elementary School|21|4
12/16/2013|SI02|Franciscan Omni Health and Fitness|17|3.5
If actual_mobile_hrs <= 4.5 then
CASE
WHEN projection between 10 and 30
THEN 3
WHEN projection between 31 and 60
THEN 6
WHEN projection between 61 and 90
THEN 9
WHEN projection between 91 and 120
THEN 12
WHEN projection between 121 and 150
THEN 15
End Case no_of_beds
End if;
If actual_mobile_hrs > 5 then
CASE
WHEN projection between 10 and 40
THEN 3
WHEN projection between 11 and 75
THEN 6
WHEN projection between 76 and 115
THEN 9
WHEN projection between 116 and 150
THEN 12
WHEN projection between 151 and 190
THEN 15
End Case no_of_beds
End if;
I want to add bed_no as a column to the select statement with the case statement. There is a if and elsif condition. How to add that to make it a case statement?
SELECT drive_date , a.site_code , site_name , projection , actual_mobile_hrs , CASE WHEN actual_mobile_hrs <= 4.5 THEN CASE WHEN projection BETWEEN 10 AND 30 THEN 3 WHEN projection BETWEEN 31 AND 60 THEN 6 WHEN projection BETWEEN 61 AND 90 THEN 9 WHEN projection BETWEEN 91 AND 120 THEN 12 WHEN projection BETWEEN 121 AND 150 THEN 15 END WHEN actual_mobile_hrs > 5 THEN CASE WHEN projection BETWEEN 10 AND 40 THEN 3 WHEN projection BETWEEN 11 AND 75 THEN 6 WHEN projection BETWEEN 76 AND 115 THEN 9 WHEN projection BETWEEN 116 AND 150 THEN 12 WHEN projection BETWEEN 151 AND 190 THEN 15 END END AS no_of_beds FROM blood_drives a, sites b WHERE a.site_code = b.site_code AND drive_date = SYSDATE - 1 AND drive_cancelled IS NULL;
I assume between 11 and 75 is a typo and should be between 41 and 75
CASE WHEN actual_mobile_hrs <= 4.5 and projection between 10 and 30 then 3
WHEN actual_mobile_hrs <= 4.5 and projection between 31 and 60 then 6
WHEN actual_mobile_hrs <= 4.5 and projection between 61 and 90 then 9
WHEN actual_mobile_hrs <= 4.5 and projection between 91 and 120 then 12
WHEN actual_mobile_hrs <= 4.5 and projection between 121 and 150 then 15
WHEN actual_mobile_hrs > 5 and projection between 10 and 40 then 3
WHEN actual_mobile_hrs > 5 and projection between 41 and 75 then 6
WHEN actual_mobile_hrs > 5 and projection between 76 and 115 then 9
WHEN actual_mobile_hrs > 5 and projection between 116 and 150 then 12
WHEN actual_mobile_hrs > 5 and projection between 151 and 190 then 15
END no_of_beds
With monday.comâ€™s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.
Open in new window