Hi Guys,I am trying to add the hours between each time block stored in a database. In this database a user enters the begin time and the end time. For example the course MATH0001 would start at 8am and end at 10am. Therefore the user would enter 0810 in the start field and 1000 in the end field. The course MATH0001 doesn't run the entire semester it may only run from 8th Jan - 15th March and the course is scheduled in a room called GR4. Now because a course can be scheduled modularly, one room could have several courses scheduled in this manner. The problem: I need to find out how many hours GR4 is used but it contains the following coursesSchedule for room called GR4Course StartDate EndDate BeginTime EndTime HoursPerClassMATH0001 06-FEB-13 19-FEB-13 0810 1700 9 MATH0002 20-FEB-13 04-Mar-13 0810 1700 9 MATH0003 10-JAN-13 05-Feb-13 0810 1700 9 MATH0004 22-APR-13 17-May-13 0810 1700 9 MATH0005 08-MAR-13 21-APR-13 0810 1700 9 MATH0006 07-JAN-13 09-JAN-13 0910 1300 4MATH0007 20-JAN-13 17-MAY-13 1710 2000 3MATH0008 08-JAN-13 18-JAN-13 1710 2000 3
A day only has 13 hours. Therefore the total hours spent in GR4 should be 12 hours. This is calculated by adding the hours betweeen 8am and 5pm = 9 hours and 5pm an 8pm = 3 hours. I would not include 9am to 1pm because it is a subset of the 8am to 5pm slot.Now I have no idea how to accomplish this but below is the code that i have thus far:I forgot to mention that this code was to just test my 'final code' results and it outputs the table shown above. Anyway for testing purposes I have limited the search to the room GR4 and the day Tuesdays.select DISTINCT ssrmeet_room_code, ssrmeet_start_date, ssrmeet_end_date, ssrmeet_crn, ssrmeet_begin_time, ssrmeet_end_time, (((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 As HoursPerClass, ((SSRMEET_END_DATE - SSRMEET_START_DATE)+1) Duration_Of_Class_By_Days, ROUND((((SSRMEET_END_DATE - SSRMEET_START_DATE)+1)/7),0) Duration_Of_Class_By_Week , ((((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 ) * (ROUND((((SSRMEET_END_DATE - SSRMEET_START_DATE)+1)/7),0)) AS HOURS_DURING_SEMESTERfrom ssbsect s inner join ssrmeet M on s.ssbsect_term_code = M.ssrmeet_term_code and s.ssbsect_crn=M.ssrmeet_crn where ssbsect_term_code = '201220' ANDssrmeet_room_code = 'GR4' AND ssrmeet_TUE_DAY = 'T' group by ssrmeet_room_code, ssrmeet_begin_time, ssrmeet_end_time, ssrmeet_crn,(( (CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100, ssrmeet_start_date, ssrmeet_end_date
So the code below is my final report. As I mentioned earlier the code above this text is just for testing purposes and the code below is my final code.[code]SELECT ssrmeet_room_code, ssrmeet_bldg_code, SUM(MONDAY)Monday, SUM(TUESDAY)Tuesday, SUM(WEDNESDAY)Wednesday, SUM(THURSDAY)Thursday, SUM(FRIDAY)Friday, SUM(SATURDAY)Saturday, SUM(SUNDAY)Sunday, ((SUM(MONDAY))+(SUM(TUESDAY))+(SUM(WEDNESDAY))+(SUM(THURSDAY))+(SUM(FRIDAY))+(SUM(SATURDAY))+(SUM(SUNDAY)))WklyHoursfrom (SELECT DISTINCT ssrmeet_room_code, ssrmeet_bldg_code,ssbsect_crn, SUM(CASE WHEN ssrmeet_MON_DAY = 'M'AND ssrmeet_room_code is not null THEN (( (CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 ELSE 0 END) MONDAY, SUM(CASE WHEN ssrmeet_TUE_DAY = 'T'AND ssrmeet_room_code is not null THEN (( (CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 ELSE 0 END) TUESDAY, SUM(CASE WHEN ssrmeet_WED_DAY = 'W'AND ssrmeet_room_code is not null THEN (( (CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 ELSE 0 END) WEDNESDAY, SUM(CASE WHEN ssrmeet_THU_DAY = 'R'AND ssrmeet_room_code is not null THEN (( (CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 ELSE 0 END) THURSDAY, SUM(CASE WHEN ssrmeet_FRI_DAY = 'F'AND ssrmeet_room_code is not null THEN (( (CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 ELSE 0 END) FRIDAY, SUM(CASE WHEN ssrmeet_SAT_DAY = 'S'AND ssrmeet_room_code is not null THEN (( (CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 ELSE 0 END) SATURDAY, SUM(CASE WHEN ssrmeet_SUN_DAY = 'U'AND ssrmeet_room_code is not null THEN (( (CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 ELSE 0 END) SUNDAY FROM ssbsect s inner join ssrmeet M on s.ssbsect_term_code=M.ssrmeet_term_code and s.ssbsect_crn=M.ssrmeet_crn WHERE ssbsect_term_code = '201220' AND ssrmeet_room_code is not null GROUP BY ssrmeet_room_code, ssrmeet_bldg_code, ssbsect_crn, ssrmeet_start_date)wwhere ssrmeet_room_code is not nullGROUP BY ssrmeet_room_code, ssrmeet_bldg_code[code]