OraFAQ Forum: SQL & PL/SQL » Query for shift time

Home » SQL & PL/SQL » SQL & PL/SQL » Query for shift time

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query for shift time [message #581949]

Fri, 12 April 2013 02:25

urbanmojo
Messages: 9
Registered: March 2013

Junior Member

Hello:

I'm trying to figure out an elegant way to do this, in one query. I need to figure out, based on total scheduled shift time and scheduled breaks what the effective schedule time is by hour for a particular employee.

So I have a shift query that gives me this (using a cross-day shift here because they do happen-not sure if that will impact things):

Shift ID    EmployeeID      ShiftStart               ShiftEnd
123           99            04/10/2013 21:00:00      04/11/2013 06:00:00

And I have a activity query that gives me

Shift ID    EmployeeID      ActivityStart            ActivityEnd              Type
123           99            04/10/2013 23:00:00      04/10/2013 23:15:00      Break
123           99            04/11/2013 02:00:00      04/11/2013 03:00:00      Lunch
123           99            04/11/2013 04:00:00      04/11/2013 04:15:00      Break

And I need a query that give me this:

Shift ID    EmployeeID       ShiftStart                 Hour                    Net_Scheduled_Time(min)
123           99            04/10/2013 21:00:00     04/10/2013 21:00:00           60
123           99            04/10/2013 21:00:00     04/10/2013 22:00:00           60
123           99            04/10/2013 21:00:00     04/10/2013 23:00:00           45
123           99            04/10/2013 21:00:00     04/11/2013 00:00:00           60
123           99            04/10/2013 21:00:00     04/11/2013 01:00:00           60
123           99            04/10/2013 21:00:00     04/11/2013 02:00:00            0
123           99            04/10/2013 21:00:00     04/11/2013 03:00:00           60
123           99            04/10/2013 21:00:00     04/11/2013 04:00:00           45
123           99            04/10/2013 21:00:00     04/11/2013 05:00:00           60

Is there a relatively easy way to get there in one query, no temp tables?

Report message to a moderator

Re: Query for shift time [message #581953 is a reply to message #581949]

Fri, 12 April 2013 02:41

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

Almost certainly, but since we don't know what your tables look like, or what your queries look like and we're not psychic, we have no way of telling you how to do it.

If you post a Test case - create table statements and insert statements for a small, repsentative sample of data, then we'll be able to work with your tables and data.

Report message to a moderator

Re: Query for shift time [message #582079 is a reply to message #581953]

Sat, 13 April 2013 00:42

urbanmojo
Messages: 9
Registered: March 2013

Junior Member

I am posting the create table scripts and inserts. A summary of the tables:
Z_D_ACTIVITY - a dimension with activity names. For test purposes on Lunch and Break are here.
Z_D_EMPLOYEE - a dimension to get employee names.
Z_SCHED_ACTIVITY - start and end times of an activity, and a shift_id to tie it to the shift
Z_SHIFT - the start and end time of a shift

Actually now that I think of it you can probably leave hooking up to the dimensions out of it to keep it simple, don't think they are needed for the test case. I'll leave them here anyway.

Please let me know if you need more information.

Cheers

CREATE TABLE Z_D_ACTIVITY
(
   ACTIVITY_ID decimal(22),
   NAME varchar2(50)
);

CREATE TABLE Z_D_EMPLOYEE
(
   EMPLOYEE_ID decimal(22),
   NAME varchar2(50)
);

CREATE TABLE Z_SCHED_ACTIVITY
(
   SCHED_ACTIVITY_ID decimal(38),
   STARTTIME timestamp,
   ENDTIME timestamp,
   ACTIVITY_ID decimal(38),
   EMPLOYEE_ID decimal(38),
   SHIFT_ID decimal(38)
);

CREATE TABLE Z_SHIFT
(
   SHIFT_ID decimal(22),
   EMP_ID decimal(22),
   STARTTIME timestamp,
   ENDTIME timestamp
);


INSERT INTO Z_D_ACTIVITY VALUES (-4107,'Lunch');
INSERT INTO Z_D_ACTIVITY VALUES (-4104,'Break');

INSERT INTO Z_D_EMPLOYEE  VALUES (1878,'Carol');
INSERT INTO Z_D_EMPLOYEE  VALUES (5217,'Bob');

INSERT INTO Z_SCHED_ACTIVITY  VALUES (16452556,{ts '2013-04-11 01:00:00.0'},{ts '2013-04-11 02:00:00.0'},-4107,5217,252849);
INSERT INTO Z_SCHED_ACTIVITY  VALUES (16456677,{ts '2013-04-11 05:30:00.0'},{ts '2013-04-11 05:45:00.0'},-4104,1878,249440);
INSERT INTO Z_SCHED_ACTIVITY  VALUES (16457993,{ts '2013-04-11 03:52:00.0'},{ts '2013-04-11 04:07:00.0'},-4104,5217,252849);
INSERT INTO Z_SCHED_ACTIVITY  VALUES (16613742,{ts '2013-04-11 08:00:00.0'},{ts '2013-04-11 09:00:00.0'},-4107,1878,249440);
INSERT INTO Z_SCHED_ACTIVITY  VALUES (16613744,{ts '2013-04-11 10:30:00.0'},{ts '2013-04-11 10:45:00.0'},-4104,1878,249440);

INSERT INTO Z_SHIFT VALUES (249440,1878,{ts '2013-04-11 03:00:00.0'},{ts '2013-04-11 12:00:00.0'});
INSERT INTO Z_SHIFT VALUES (252849,5217,{ts '2013-04-10 21:00:00.0'},{ts '2013-04-11 06:00:00.0'});

Report message to a moderator

Re: Query for shift time [message #582084 is a reply to message #582079]

Sat, 13 April 2013 01:10

urbanmojo
Messages: 9
Registered: March 2013

Junior Member

Since my original post's sample desired output doesn't match the test case data, here is the expected output:

Shift ID      EmployeeID             ShiftStart                 Hour               Net_Scheduled_Time(min)
249440           1878            04/11/2013 03:00:00     04/11/2013 03:00:00           60
249440           1878            04/11/2013 03:00:00     04/11/2013 04:00:00           60
249440           1878            04/11/2013 03:00:00     04/11/2013 05:00:00           45
249440           1878            04/11/2013 03:00:00     04/11/2013 06:00:00           60
249440           1878            04/11/2013 03:00:00     04/11/2013 07:00:00           60
249440           1878            04/11/2013 03:00:00     04/11/2013 08:00:00            0
249440           1878            04/11/2013 03:00:00     04/11/2013 09:00:00           60
249440           1878            04/11/2013 03:00:00     04/11/2013 10:00:00           45
249440           1878            04/11/2013 03:00:00     04/11/2013 11:00:00           60
252849           5217            04/10/2013 21:00:00     04/10/2013 21:00:00           60
252849           5217            04/10/2013 21:00:00     04/10/2013 22:00:00           60
252849           5217            04/10/2013 21:00:00     04/10/2013 23:00:00           45
252849           5217            04/10/2013 21:00:00     04/11/2013 00:00:00           60
252849           5217            04/10/2013 21:00:00     04/11/2013 01:00:00            0
252849           5217            04/10/2013 21:00:00     04/11/2013 02:00:00           60
252849           5217            04/10/2013 21:00:00     04/11/2013 03:00:00           52
252849           5217            04/10/2013 21:00:00     04/11/2013 04:00:00           53
252849           5217            04/10/2013 21:00:00     04/11/2013 05:00:00           60

Report message to a moderator

Re: Query for shift time [message #582090 is a reply to message #582084]

Sat, 13 April 2013 03:49

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Could you post valid INSERT statements:

SQL> INSERT INTO Z_SHIFT VALUES (252849,5217,{ts '2013-04-10 21:00:00.0'},{ts '2013-04-11 06:00:00.0'});
INSERT INTO Z_SHIFT VALUES (252849,5217,{ts '2013-04-10 21:00:00.0'},{ts '2013-04-11 06:00:00.0'})
                                        *
ERROR at line 1:
ORA-00911: invalid character

Regards
Michel

Report message to a moderator

Re: Query for shift time [message #582091 is a reply to message #582084]

Sat, 13 April 2013 04:50

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> with 
  2    timeslice as (
  3      select shift_id, emp_id employee_id, starttime+0 shift_start, column_value hour_start
  4      from z_shift, 
  5           table(cast(multiset(select starttime+(level-1)/24 
  6                               from dual
  7                               connect by level <= ceil(((endtime+0)-(starttime+0))*24))
  8                      as sys.odciDateList))
  9    )
 10  select s.shift_id, s.employee_id, s.shift_start, s.hour_start,
 11         60 - 
 12         nvl(round(sum(( least(a.endtime+0,s.hour_start+1/24) 
 13                       - greatest(a.starttime+0,s.hour_start))*1440)),
 14             0) "Net_Scheduled_Time(min)"
 15  from timeslice s left outer join z_sched_activity a
 16       on     a.shift_id = s.shift_id and a.employee_id = s.employee_id
 17          and ( ( a.starttime+0 <= s.hour_start and a.endtime+0 >= s.hour_start )
 18                or ( a.starttime+0 >= s.hour_start and a.starttime+0 <= s.hour_start+1/24 ) )
 19  group by s.shift_id, s.employee_id, s.shift_start, s.hour_start
 20  order by s.employee_id, s.hour_start
 21  /
  SHIFT_ID EMPLOYEE_ID SHIFT_START         HOUR_START          Net_Scheduled_Time(min)
---------- ----------- ------------------- ------------------- -----------------------
    249440        1878 11/04/2013 03:00:00 11/04/2013 03:00:00                      60
    249440        1878 11/04/2013 03:00:00 11/04/2013 04:00:00                      60
    249440        1878 11/04/2013 03:00:00 11/04/2013 05:00:00                      45
    249440        1878 11/04/2013 03:00:00 11/04/2013 06:00:00                      60
    249440        1878 11/04/2013 03:00:00 11/04/2013 07:00:00                      60
    249440        1878 11/04/2013 03:00:00 11/04/2013 08:00:00                       0
    249440        1878 11/04/2013 03:00:00 11/04/2013 09:00:00                      60
    249440        1878 11/04/2013 03:00:00 11/04/2013 10:00:00                      45
    249440        1878 11/04/2013 03:00:00 11/04/2013 11:00:00                      60
    252849        5217 10/04/2013 21:00:00 10/04/2013 21:00:00                      60
    252849        5217 10/04/2013 21:00:00 10/04/2013 22:00:00                      60
    252849        5217 10/04/2013 21:00:00 10/04/2013 23:00:00                      60
    252849        5217 10/04/2013 21:00:00 11/04/2013 00:00:00                      60
    252849        5217 10/04/2013 21:00:00 11/04/2013 01:00:00                       0
    252849        5217 10/04/2013 21:00:00 11/04/2013 02:00:00                      60
    252849        5217 10/04/2013 21:00:00 11/04/2013 03:00:00                      52
    252849        5217 10/04/2013 21:00:00 11/04/2013 04:00:00                      53
    252849        5217 10/04/2013 21:00:00 11/04/2013 05:00:00                      60

Regards
Michel

Report message to a moderator

Re: Query for shift time [message #582164 is a reply to message #582091]

Mon, 15 April 2013 01:52

urbanmojo
Messages: 9
Registered: March 2013

Junior Member

Michel:

Thanks for your help on this.

When I run this against my data (Oracle 11.2.0.3.0) the first employee is o.k. but the second one 5217 is different from your results. Basically all the values that are not "60" are different:

SHIFT_ID	EMPLOYEE_ID	SHIFT_START	HOUR_START	Net_Scheduled_Time(min)
249,440	1,878	2013-04-11 03:00:00.0	2013-04-11 03:00:00.0	60
249,440	1,878	2013-04-11 03:00:00.0	2013-04-11 04:00:00.0	60
249,440	1,878	2013-04-11 03:00:00.0	2013-04-11 05:00:00.0	45
249,440	1,878	2013-04-11 03:00:00.0	2013-04-11 06:00:00.0	60
249,440	1,878	2013-04-11 03:00:00.0	2013-04-11 07:00:00.0	60
249,440	1,878	2013-04-11 03:00:00.0	2013-04-11 08:00:00.0	0
249,440	1,878	2013-04-11 03:00:00.0	2013-04-11 09:00:00.0	60
249,440	1,878	2013-04-11 03:00:00.0	2013-04-11 10:00:00.0	45
249,440	1,878	2013-04-11 03:00:00.0	2013-04-11 11:00:00.0	60
252,849	5,217	2013-04-10 21:00:00.0	2013-04-10 21:00:00.0	60
252,849	5,217	2013-04-10 21:00:00.0	2013-04-10 22:00:00.0	60
252,849	5,217	2013-04-10 21:00:00.0	2013-04-10 23:00:00.0	60
252,849	5,217	2013-04-10 21:00:00.0	2013-04-11 00:00:00.0	60
252,849	5,217	2013-04-10 21:00:00.0	2013-04-11 01:00:00.0	-60
252,849	5,217	2013-04-10 21:00:00.0	2013-04-11 02:00:00.0	60
252,849	5,217	2013-04-10 21:00:00.0	2013-04-11 03:00:00.0	44
252,849	5,217	2013-04-10 21:00:00.0	2013-04-11 04:00:00.0	46
252,849	5,217	2013-04-10 21:00:00.0	2013-04-11 05:00:00.0	60

Any idea why that might be?

Thanks,,

Report message to a moderator

Re: Query for shift time [message #582165 is a reply to message #582164]

Mon, 15 April 2013 02:00

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No, I have no idea as I even don't know if you have the same data and if you executed the same query.
All I can tell you for the moment is that I executed it in 10.2.0.4.

Regards
Michel

Report message to a moderator

Re: Query for shift time [message #582168 is a reply to message #582165]

Mon, 15 April 2013 02:36

urbanmojo
Messages: 9
Registered: March 2013

Junior Member

My apologies--I had inserted an extra row into z_shift as a test when you said the inserts weren't working and forgot to restore it to the original state. Query works fine--thanks for your help.

Report message to a moderator

Previous Topic:	duplicates issue - UNIQUE constraint
Next Topic:	How make the complete schema public synonym.....

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 20 01:37:36 CDT 2026