Home » RDBMS Server » Server Administration » Finding the correct work shift
| Finding the correct work shift [message #374723] |
Tue, 26 June 2001 13:54  |
Mark Kane
Messages: 21
Registered: January 2000
|
Junior Member |
|
|
I am absolutely stumped on this one, and any help would be appreciated.
I have a table with the following data:
Shift Start_time End_time
1 01-JUN-01 07:00:00 01-JUN-01 15:00:00
2 01-JUN-01 15:00:00 01-JUN-01 23:00:00
3 01-JUN-01 23:00:00 01-JUN-01 07:00:00
I need to be able to find the shift number, start time and end time at any given time. For example, if it is 26-JUN-01 23:30:00, the output should be
3 26-JUN-01 23:00:00 27-JUN-01 07:00:00
The trick I need to solve is to do this in one SQL statement. I am unable to resolve the 3rd shift. In any given day, there are two 3rd shifts because it spans midnite. Here is how I can find the correct shift times for shifts 1 and 2.
select shift_no,
to_char(trunc(sysdate) + (start_time - trunc(start_time)),'DD-MON-YY HH24:MI') shift_start_time,
to_char(trunc(sysdate) + (end_time - trunc(end_time)),'DD-MON-YY HH24:MI') shift_end_time,
to_char(sysdate,'DD-MON-YY HH24:MI') datetime
from work_shift_lu
where (sysdate > trunc(sysdate) + (start_time - trunc(start_time))
and sysdate <= trunc(sysdate) + (end_time - trunc(end_time)))
Again, any help is greatly appreciated.
Mark Kane
|
|
|
|
| Re: Finding the correct work shift [message #374730 is a reply to message #374723] |
Wed, 27 June 2001 06:15  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
Right, here's a solution.
It looks messy as anything, and after the patented 'OraFAQ Format Destroyer' has finished with it it will look worse.
select shift
,trunc(l_date)+ (start_time - trunc(start_time)) start_time
,trunc(l_date)+ (end_time - trunc(end_time)) end_time
from work_shift
where ( (start_time - trunc(start_time)) < (l_date - trunc(l_date))
AND (end_time - trunc(end_time)) > (l_date - trunc(l_date))
AND (start_time - trunc(start_time)) < (end_time - trunc(end_time))) -- between start and end dates;
OR (( (start_time - trunc(start_time)) < (l_date - trunc(l_date))
OR (end_time - trunc(end_time)) > (l_date - trunc(l_date)))
AND (start_time - trunc(start_time)) > (end_time - trunc(end_time)));
It is doing two checks.
One is to see if the start date is earlier than Now, AND the end date is later, AND the start_date is earlier than the end_date (this is for shifts 1 and 2).
The other check is to see if EITHER (the start date is earlier than now OR the end date is later than now) AND the start_date is later than the end date (for shift 3)
Hope this helps.
|
|
|
|
| Re: Finding the correct work shift [message #374732 is a reply to message #374723] |
Wed, 27 June 2001 06:29 |
Hans
Messages: 42
Registered: September 2000
|
Member |
|
|
Hi,
i'm not sure if i understood your problem correct but nevertheless here is my suggestion.
May be there exists an easier solution. Please look at the script below.
connect test/test
drop table work_shift_lu;
create table work_shift_lu (
shift_no number(3),
start_time date,
end_time date
);
insert into work_shift_lu (shift_no,start_time,end_time)
values ( 1, to_date('01-JUN-2001 07:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('01-JUN-2001 15:00:00','DD-MON-YYYY HH24:MI:SS'));
insert into work_shift_lu (shift_no,start_time,end_time)
values ( 2, to_date('01-JUN-2001 15:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('01-JUN-2001 23:00:00','DD-MON-YYYY HH24:MI:SS'));
insert into work_shift_lu (shift_no,start_time,end_time)
values ( 3, to_date('01-JUN-2001 23:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('01-JUN-2001 07:00:00','DD-MON-YYYY HH24:MI:SS'));
commit;
-----------------
-- <b>Statement</b>
-----------------
select shift_no,
to_char(trunc(sysdate) + (start_time - trunc(start_time)),'DD-MON-YYYY HH24:MI') shift_start_time,
to_char(trunc(sysdate) + (end_time - trunc(end_time)),'DD-MON-YYYY HH24:MI') shift_end_time,
to_char(sysdate,'DD-MON-YYYY HH24:MI') datetime
from (
select shift_no, start_time, end_time, sign(end_time-start_time) signum from work_shift_lu
)
where ( sysdate > trunc(sysdate) + (start_time - trunc(start_time))
and sysdate <= trunc(sysdate) + (end_time - trunc(end_time))
and signum = 1 )
union all
select shift_no,
to_char(trunc(sysdate) + (start_time - trunc(start_time)),'DD-MON-YYYY HH24:MI') shift_start_time,
to_char(trunc(sysdate+1) + (end_time - trunc(end_time)),'DD-MON-YYYY HH24:MI') shift_end_time,
to_char(sysdate,'DD-MON-YYYY HH24:MI') datetime
from (
select shift_no, start_time, end_time, sign(end_time-start_time) signum from work_shift_lu
)
where (sysdate > trunc(sysdate) + (start_time - trunc(start_time))
and sysdate <= trunc(sysdate+1) + (end_time - trunc(end_time))
and signum = -1 )
union all
select shift_no,
to_char(trunc(sysdate-1) + (start_time - trunc(start_time)),'DD-MON-YYYY HH24:MI') shift_start_time,
to_char(trunc(sysdate) + (end_time - trunc(end_time)),'DD-MON-YYYY HH24:MI') shift_end_time,
to_char(sysdate,'DD-MON-YYYY HH24:MI') datetime
from (
select shift_no, start_time, end_time, sign(end_time-start_time) signum from work_shift_lu
)
where (sysdate > trunc(sysdate-1) + (start_time - trunc(start_time))
and sysdate <= trunc(sysdate) + (end_time - trunc(end_time))
and signum = -1 )
union all
select shift_no,
to_char(trunc(sysdate) + (start_time - trunc(start_time)),'DD-MON-YYYY HH24:MI') shift_start_time,
to_char(trunc(sysdate) + (end_time - trunc(end_time)),'DD-MON-YYYY HH24:MI') shift_end_time,
to_char(sysdate,'DD-MON-YYYY HH24:MI') datetime
from (
select shift_no, start_time, end_time, sign(end_time-start_time) signum from work_shift_lu
)
where (sysdate >= trunc(sysdate) + (start_time - trunc(start_time))
and sysdate <= trunc(sysdate) + (end_time - trunc(end_time))
and signum = 0 );
---------------------------------------------------
-- create function <b>foo</b> for testing above statement
---------------------------------------------------
create or replace function foo( date_in date )
return varchar2
is
cursor cur_1(v_date date) is
select shift_no,
to_char(trunc(v_date) + (start_time - trunc(start_time)),'DD-MON-YYYY HH24:MI') shift_start_time,
to_char(trunc(v_date) + (end_time - trunc(end_time)),'DD-MON-YYYY HH24:MI') shift_end_time,
to_char(v_date,'DD-MON-YYYY HH24:MI') datetime
from (
select shift_no, start_time, end_time, sign(end_time-start_time) signum from work_shift_lu
)
where ( v_date > trunc(v_date) + (start_time - trunc(start_time))
and v_date <= trunc(v_date) + (end_time - trunc(end_time))
and signum = 1 )
union all
select shift_no,
to_char(trunc(v_date) + (start_time - trunc(start_time)),'DD-MON-YYYY HH24:MI') shift_start_time,
to_char(trunc(v_date+1) + (end_time - trunc(end_time)),'DD-MON-YYYY HH24:MI') shift_end_time,
to_char(v_date,'DD-MON-YYYY HH24:MI') datetime
from (
select shift_no, start_time, end_time, sign(end_time-start_time) signum from work_shift_lu
)
where (v_date > trunc(v_date) + (start_time - trunc(start_time))
and v_date <= trunc(v_date+1) + (end_time - trunc(end_time))
and signum = -1 )
union all
select shift_no,
to_char(trunc(v_date-1) + (start_time - trunc(start_time)),'DD-MON-YYYY HH24:MI') shift_start_time,
to_char(trunc(v_date) + (end_time - trunc(end_time)),'DD-MON-YYYY HH24:MI') shift_end_time,
to_char(v_date,'DD-MON-YYYY HH24:MI') datetime
from (
select shift_no, start_time, end_time, sign(end_time-start_time) signum from work_shift_lu
)
where (v_date > trunc(v_date-1) + (start_time - trunc(start_time))
and v_date <= trunc(v_date) + (end_time - trunc(end_time))
and signum = -1 )
union all
select shift_no,
to_char(trunc(v_date) + (start_time - trunc(start_time)),'DD-MON-YYYY HH24:MI') shift_start_time,
to_char(trunc(v_date) + (end_time - trunc(end_time)),'DD-MON-YYYY HH24:MI') shift_end_time,
to_char(v_date,'DD-MON-YYYY HH24:MI') datetime
from (
select shift_no, start_time, end_time, sign(end_time-start_time) signum from work_shift_lu
)
where (v_date >= trunc(v_date) + (start_time - trunc(start_time))
and v_date <= trunc(v_date) + (end_time - trunc(end_time))
and signum = 0 );
r1 cur_1%rowtype;
begin
open cur_1( date_in );
fetch cur_1 into r1;
if cur_1%notfound then
close cur_1;
return (null);
end if;
close cur_1;
return ( r1.shift_no || ' ' || r1.shift_start_time || ' ' || r1.shift_end_time || ' ' || r1.datetime );
end;
/
show errors
SQL> select foo( to_date('20.12.2001 23:20:00','DD.MM.YYYY HH24:MI:SS' ) ) result from dual;
RESULT
--------------------------------------------------------------------------------------------------------
3 20-DEC-2001 23:00 21-DEC-2001 07:00 20-DEC-2001 23:20
SQL> select foo( to_date('20.12.2001 05:30:00','DD.MM.YYYY HH24:MI:SS' ) ) result from dual;
RESULT
--------------------------------------------------------------------------------------------------------
3 19-DEC-2001 23:00 20-DEC-2001 07:00 20-DEC-2001 05:30
SQL> select foo( to_date('20.12.2001 13:30:00','DD.MM.YYYY HH24:MI:SS' ) ) result from dual;
RESULT
--------------------------------------------------------------------------------------------------------
1 20-DEC-2001 07:00 20-DEC-2001 15:00 20-DEC-2001 13:30
SQL> select foo( to_date('20.12.2001 19:30:00','DD.MM.YYYY HH24:MI:SS' ) ) result from dual;
RESULT
--------------------------------------------------------------------------------------------------------
2 20-DEC-2001 15:00 20-DEC-2001 23:00 20-DEC-2001 19:30
|
|
|
|
| Re: Finding the correct work shift [message #374735 is a reply to message #374723] |
Wed, 27 June 2001 07:09 |
Mark Kane
Messages: 21
Registered: January 2000
|
Junior Member |
|
|
John and Hans,
I really appreciate your time on this. I did not expect such thorough answers. It will take me a little time to absorb all of this, but both methods look like they will work for me. I might blend both approaches to come away with a stored procedure that can be reused.
Just so you know, I am working on a electronic marquee sign that will report production test yields on a factory floor. If yields drop below a certain percent, managers stop the line to determine the cause.
Thanks again, Mark
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 19:07:01 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
