If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Overlapping Times

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Mar 2005
Posts: 15
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,497
post CREATE TABLE statements for the table used by post SQL
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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")
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,497
1) create table ssrmeet is not valid SQL
2) Oracle does not use double quote marks to delimit data

post COMPLETE results from following SQL

SELECT * FROM V$VERSION;
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Mar 2005
Posts: 15
Hi Anacedent,


The things is I don't have the necessary permissions to create tables, functions or procedure. So I just did it off the top of my head. Below are the results

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
"CORE 10.2.0.4.0 Production"
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,497
Code:
  1* select dbms_metadata.get_ddl('TABLE','EMPLOYEES','HR') FROM DUAL
SQL> /

DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','HR')
--------------------------------------------------------------------------------

  CREATE TABLE "HR"."EMPLOYEES"
   (    "EMPLOYEE_ID" NUMBER(6,0),
        "FIRST_NAME" VARCHAR2(20),
        "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
        "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
        "PHONE_NUMBER" VARCHAR2(20),
        "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
        "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
        "SALARY" NUMBER(8,2),
        "COMMISSION_PCT" NUMBER(2,2),

DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','HR')
--------------------------------------------------------------------------------
        "MANAGER_ID" NUMBER(6,0),
        "DEPARTMENT_ID" NUMBER(4,0),
         CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,
         CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','HR')
--------------------------------------------------------------------------------
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
          REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE,
         CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")
          REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE,
         CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
          REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE
   ) SEGMENT CREATION IMMEDIATE

DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','HR')
--------------------------------------------------------------------------------
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Mar 2005
Posts: 15
Hi Anacedent,

I am at a total lost... did you post that code in the wrong thread?
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On