Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2005
    Posts
    15

    Unanswered: Overlapping Times

    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 courses


    Code:
    Schedule for room called GR4
    Course        StartDate    EndDate      BeginTime EndTime HoursPerClass
    MATH0001  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      4
    MATH0007  20-JAN-13  17-MAY-13 1710         2000      3
    MATH0008  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 between 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.

    Code:
    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_SEMESTER
    
    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 = '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)))WklyHours
    from (
    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
    )w
    where ssrmeet_room_code is not null
    GROUP BY ssrmeet_room_code, ssrmeet_bldg_code

  2. #2
    Join Date
    Mar 2005
    Posts
    15
    create table ssrmeet (SSRMEET_ROOM_CODE char,SSRMEET_START_DATE date,SSRMEET_END_DATE date,SSRMEET_CRN char, SSRMEET_BEGIN_TIME char, SSRMEET_END_TIME char,SSRMEET_TERM_CODE,SSRMEET_TUE_DAY char)

    Insert into ssrmeet values ("LR11",07-JAN-13,17-MAY-13,"20368","1610","1800","201220","T")
    Insert into ssrmeet values ("LR11",07-JAN-13,17-MAY-13,"20662","1310","1600","201220","T")
    Insert into ssrmeet values ("LR11",07-JAN-13,17-MAY-13,"20666","0810","0900","201220","T")
    Insert into ssrmeet values ("LR11",07-JAN-13,17-MAY-13,"20672","0910","1000","201220","T")
    Insert into ssrmeet values ("LR11",07-JAN-13,17-MAY-13,"20928","1210","1300","201220","T")
    Insert into ssrmeet values ("GR4",06-FEB-13,19-FEB-13,"21224","0810","1700","201220","T")
    Insert into ssrmeet values ("GR4",20-FEB-13,04-MAR-13,"21225","0810","1700","201220","T")
    Insert into ssrmeet values ("GR4",10-JAN-13,05-FEB-13,"21230","0810","1700","201220","T")
    Insert into ssrmeet values ("GR4",22-APR-13,17-MAY-13,"21232","0810","1700","201220","T")
    Insert into ssrmeet values ("GR4",08-MAR-13,21-APR-13,"21233","0810","1700","201220","T")
    Insert into ssrmeet values ("GR4",07-JAN-13,09-JAN-13,"21238","0910","1300","201220","T")
    Insert into ssrmeet values ("LR11,"07-JAN-13,17-MAY-13,"21353","1910","2000","201220","T")
    Insert into ssrmeet values ("GR4",20-JAN-13,17-MAY-13,"21470","1710","2000","201220","T")
    Insert into ssrmeet values ("LR11",07-JAN-13,17-MAY-13,"21932","1010","1200","201220","T")
    Insert into ssrmeet values ("GR4",08-JAN-13,18-JAN-13,"22384","1710","2000","201220","T")
    Insert into ssrmeet values ("LR11",12-FEB-13,12-FEB-13,"22540","2010","2300","201220","T")
    Insert into ssrmeet values ("LR11",02-APR-13,02-APR-13,"22625","1810","1900","201220","T")

    create table ssbsect (SSRMEET_CRN char, SSBSECT_TERM_CODE char)

    Insert into ssbsect values ("20368","201220")
    Insert into ssbsect values ("20662","201220")
    Insert into ssbsect values ("20666","201220")
    Insert into ssbsect values ("20672","201220")
    Insert into ssbsect values ("20928","201220")
    Insert into ssbsect values ("21224","201220")
    Insert into ssbsect values ("21225","201220")
    Insert into ssbsect values ("21230","201220")
    Insert into ssbsect values ("21232","201220")
    Insert into ssbsect values ("21233","201220")
    Insert into ssbsect values ("21238","201220")
    Insert into ssbsect values ("21353","201220")
    Insert into ssbsect values ("21470","201220")
    Insert into ssbsect values ("21932","201220")
    Insert into ssbsect values ("22384","201220")
    Insert into ssbsect values ("22540","201220")
    Insert into ssbsect values ("22625","201220")

  3. #3
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    May I be so bold as to offer some hopefully constructive observations.


    Make it real easy for other people to import your DDL, Inserts & Queries

    For example, the data you provide is formatted incorrectly.
    1. All the " need to be replaced with '.
    2. The dates also need to be in '. If I want to help, I first have to spend 15-20 minutes fiddling with the insert statements to make them valid.
    3. Provide enough data! There is no data for the other days of the week. It will be difficult to make sure that all parts of the solution are working!

    Your Create statement is incomplete.
    1. Where are the other days of the week?
    2. The query you say you are using won't run without errors because of the missing variables. Where is the bld_code in all this?
    3. All your column names are defined as char. That only holds 1 character.

    It is also helpful to provide a small grid (table) of your anticipated output. That way whoever helps will know they got it right.

    Do you have control over the table and the column names and types? If so,
    1. Lose the table name that prefixes all your column names. Then, if you don't use aliases, you can code things like ssbsect.crn = ssrmeet.crn
    2. For columns that you do math on, change the type to some numeric.


    I have taken the liberty of making some of these changes to your table and first query:

    Code:
    create table ssrmeet 
    (
    	ROOM_CODE char(4),
    	START_DATE date,
    	END_DATE date,
    	CRN char(5), 
    	BEGIN_TIME int, 
    	END_TIME int,
    	TERM_CODE char(6),
    	MON_DAY char(1),
    	TUE_DAY char(1),
    	WED_DAY char(1),
    	THU_DAY char(1),
    	FRI_DAY char(1),
    	SAT_DAY char(1),
    	SUN_DAY char(1)
    )
    
    create table ssbsect 
    (
        CRN char(5),
        TERM_CODE char(6)
    )
    
    Insert into ssrmeet values ('LR11','07-JAN-13','17-MAY-13','20368',1610,1800,'201220', NULL, 'T', NULL, NULL, NULL, NULL, NULL)
    Insert into ssbsect values ('20368','201220')
    
    select DISTINCT
         M.ROOM_CODE,
         M.start_date,  M.end_date,
         S.CRN,
         M.begin_time,  M.end_time,
         ((M.END_TIME - M.BEGIN_TIME)+10)/100 As HoursPerClass,
         datediff(DD, M.START_DATE, M.END_DATE)+1 as Duration_of_Class_By_Days,
         datediff(WW, M.START_DATE, M.END_DATE)+1 as Duration_of_Class_By_Week,
         (((M.END_TIME - M.BEGIN_TIME)+10)/100) * (datediff(DD, M.START_DATE, M.END_DATE)+1) AS HOURS_DURING_SEMESTER
    from ssbsect s 
    inner join ssrmeet M on S.term_code = M.term_code and S.crn = M.crn 
    where s.TERM_CODE = '201220' AND m.ROOM_CODE = 'GR4' AND m.TUE_DAY = 'T'
    Here is the output of the modified query:

    Code:
    ROOM    start_date      end_date	CRN     begin    end  Hours    Days    Weeks  Semeseter
    GR4 	2013-01-07	2013-01-09	21238	910	1300	4	3	1	12
    GR4 	2013-01-08	2013-01-18	22384	1710	2000	3	11	2	33
    GR4 	2013-01-10	2013-02-05	21230	810	1700	9	27	5	243
    GR4 	2013-01-20	2013-05-17	21470	1710	2000	3	118	17	354
    GR4 	2013-02-06	2013-02-19	21224	810	1700	9	14	3	126
    GR4 	2013-02-20	2013-03-04	21225	810	1700	9	13	3	117
    GR4 	2013-03-08	2013-04-21	21233	810	1700	9	45	8	405
    GR4 	2013-04-22	2013-05-17	21232	810	1700	9	26	4	234
    Last edited by LinksUp; 02-27-13 at 09:09.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •