January 8, 2008 at 3:39 pm
I need to create a query that can be used for generating a work shift differential. I have a query that creates the starting and ending datetime field (these are stored in the database separately, not my design but an outside vendor). I need to get the time worked between 3 pm and 11 pm for one differential and 11 pm to 7 am for the other differential. To make things more interesting the employee may work anywhere from 15 minutes up to 24 hours and thus can work in both shifts.
Here is a sample of the data that I'm working with:
Duration ScheduleStartDT ScheduleEndDT
16:4512/2/2007 12:00:00 AM12/2/2007 4:45:00 PM
24:0012/1/2007 12:00:00 AM12/2/2007 12:00:00 AM
17:0012/2/2007 12:00:00 AM12/2/2007 5:00:00 PM
13:0012/1/2007 10:00:00 AM12/1/2007 11:00:00 PM
08:0012/1/2007 6:30:00 AM12/1/2007 2:30:00 PM
05:0012/1/2007 6:30:00 AM12/1/2007 11:30:00 AM
The duration field is calculated for military time.
Any help would be greatly appreciated.
Thanks,
Doug
January 9, 2008 at 3:30 am
Nice Problem!!
To get you started, i've worked out the SQL for the first shift, you can do the rest yourself 😛
It looks like its been butchered, however I can't think of an elegant way of sorting this out. (if i do then i'll post it)
I've added some comments in to the code
One note about the other shift, again use the dateadd using the start date to work out your starting point but use a value for the hours which is greater than 24 to push you into the next day (ie dateadd(hh, 31, ......))
create table shift_diff (Duration varchar(5), ScheduleStartDT datetime, ScheduleEndDT datetime)
insert into shift_diff values ('16:45', '12/2/2007 12:00:00 AM','12/2/2007 4:45:00 PM')
insert into shift_diff values ('24:00', '12/1/2007 12:00:00 AM', '12/2/2007 12:00:00 AM')
insert into shift_diff values ('17:00', '12/2/2007 12:00:00 AM', '12/2/2007 5:00:00 PM')
insert into shift_diff values ('13:00' ,'12/1/2007 10:00:00 AM' ,'12/1/2007 11:00:00 PM')
insert into shift_diff values ('08:00' ,'12/1/2007 6:30:00 AM', '12/1/2007 2:30:00 PM')
insert into shift_diff values ('05:00' ,'12/1/2007 6:30:00 AM', '12/1/2007 11:30:00 AM')
select
-- first column, between 3pm and 11pm
-- first 2 cases, work is out side of shift time
case when ScheduleEndDT <= shift_start
or ScheduleStartDT >= shift_end
then 0
-- third case, start and end times are wholly within the shift
when ScheduleStartDT >= shift_start
and ScheduleEndDT <= shift_end
then datediff(hh, ScheduleStartDT, ScheduleEndDT)
-- fourth case start time is before the shift, end time is in the shift
when ScheduleStartDT <= shift_start
and ScheduleEndDT > shift_start
and ScheduleEndDT < shift_end
then datediff(hh, shift_start, ScheduleEndDT)
-- fifth case start time is in the shift, end time is after
when ScheduleStartDT > shift_start
and ScheduleStartDT < shift_end
and ScheduleEndDT >= shift_end
then datediff(hh, ScheduleStartDT, shift_end)
-- sixth and final, start time is before the shift starts and end time is after the shift starts
when ScheduleStartDT <= shift_start
and ScheduleEndDT >= shift_end
then datediff(hh, shift_start, shift_end)
end as shift1,
duration,
ScheduleStartDT,
ScheduleEndDT,
shift_start,
shift_end,
day_string
from
(select
duration,
ScheduleStartDT,
ScheduleEndDT,
dateadd(hh, 15, cast(year(ScheduleStartDT) as varchar)+'-'+cast(month(ScheduleStartDT) as varchar)+'-'+cast(day(ScheduleStartDT) as varchar)) as shift_start,
dateadd(hh, 23, cast(year(ScheduleStartDT) as varchar)+'-'+cast(month(ScheduleStartDT) as varchar)+'-'+cast(day(ScheduleStartDT) as varchar)) as shift_end,
cast(year(ScheduleStartDT) as varchar)+'-'+cast(month(ScheduleStartDT) as varchar)+'-'+cast(day(ScheduleStartDT) as varchar) as day_string
from Shift_Diff) as ShiftDiff2
EDIT: this is a revised version that tidies up the time calculations a bit
January 9, 2008 at 4:32 am
Here's a more complete solution:
select
-- zero column, between 3pm and 11pm
-- first 2 cases, work is out side of shift time
case when ScheduleEndDT <= shiftP_start
or ScheduleStartDT >= shiftP_end
then 0
-- third case, start and end times are wholly within the shift
when ScheduleStartDT >= shiftP_start
and ScheduleEndDT <= shiftP_end
then datediff(hh, ScheduleStartDT, ScheduleEndDT)
-- fourth case start time is before the shift, end time is in the shift
when ScheduleStartDT <= shiftP_start
and ScheduleEndDT > shiftP_start
and ScheduleEndDT < shiftP_end
then datediff(hh, shiftP_start, ScheduleEndDT)
-- fifth case start time is in the shift, end time is after
when ScheduleStartDT > shiftP_start
and ScheduleStartDT < shiftP_end
and ScheduleEndDT >= shiftP_end
then datediff(hh, ScheduleStartDT, shiftP_end)
-- sixth and final, start time is before the shift starts and end time is after the shift starts
when ScheduleStartDT <= shiftP_start
and ScheduleEndDT >= shiftP_end
then datediff(hh, shiftP_start, shiftP_end)
end as [2300D-1 to 0700],
-- zero column, between 3pm and 11pm
-- first 2 cases, work is out side of shift time
case when ScheduleEndDT <= shift0_start
or ScheduleStartDT >= shift0_end
then 0
-- third case, start and end times are wholly within the shift
when ScheduleStartDT >= shift0_start
and ScheduleEndDT <= shift0_end
then datediff(hh, ScheduleStartDT, ScheduleEndDT)
-- fourth case start time is before the shift, end time is in the shift
when ScheduleStartDT <= shift0_start
and ScheduleEndDT > shift0_start
and ScheduleEndDT < shift0_end
then datediff(hh, shift0_start, ScheduleEndDT)
-- fifth case start time is in the shift, end time is after
when ScheduleStartDT > shift0_start
and ScheduleStartDT < shift0_end
and ScheduleEndDT >= shift0_end
then datediff(hh, ScheduleStartDT, shift0_end)
-- sixth and final, start time is before the shift starts and end time is after the shift starts
when ScheduleStartDT <= shift0_start
and ScheduleEndDT >= shift0_end
then datediff(hh, shift0_start, shift0_end)
end as [0700 to 1500],
-- first column, between 3pm and 11pm
-- first 2 cases, work is out side of shift time
case when ScheduleEndDT <= shift1_start
or ScheduleStartDT >= shift1_end
then 0
-- third case, start and end times are wholly within the shift
when ScheduleStartDT >= shift1_start
and ScheduleEndDT <= shift1_end
then datediff(hh, ScheduleStartDT, ScheduleEndDT)
-- fourth case start time is before the shift, end time is in the shift
when ScheduleStartDT <= shift1_start
and ScheduleEndDT > shift1_start
and ScheduleEndDT < shift1_end
then datediff(hh, shift1_start, ScheduleEndDT)
-- fifth case start time is in the shift, end time is after
when ScheduleStartDT > shift1_start
and ScheduleStartDT < shift1_end
and ScheduleEndDT >= shift1_end
then datediff(hh, ScheduleStartDT, shift1_end)
-- sixth and final, start time is before the shift starts and end time is after the shift starts
when ScheduleStartDT <= shift1_start
and ScheduleEndDT >= shift1_end
then datediff(hh, shift1_start, shift1_end)
end as [1500 to 2300],
-- Second column, between 11pm and 7am the next day
-- first 2 cases, work is out side of shift time
case when ScheduleEndDT <= shift2_start
or ScheduleStartDT >= shift2_end
then 0
-- third case, start and end times are wholly within the shift
when ScheduleStartDT >= shift2_start
and ScheduleEndDT <= shift2_end
then datediff(hh, ScheduleStartDT, ScheduleEndDT)
-- fourth case start time is before the shift, end time is in the shift
when ScheduleStartDT <= shift2_start
and ScheduleEndDT > shift2_start
and ScheduleEndDT < shift2_end
then datediff(hh, shift2_start, ScheduleEndDT)
-- fifth case start time is in the shift, end time is after
when ScheduleStartDT > shift2_start
and ScheduleStartDT < shift2_end
and ScheduleEndDT >= shift2_end
then datediff(hh, ScheduleStartDT, shift2_end)
-- sixth and final, start time is before the shift starts and end time is after the shift starts
when ScheduleStartDT <= shift2_start
and ScheduleEndDT >= shift2_end
then datediff(hh, shift2_start, shift2_end)
end as [2300 to 0700D+1],
duration,
ScheduleStartDT,
ScheduleEndDT,
shift1_start,
shift1_end,
shift2_start,
shift2_end,
day_string
from
(select
duration,
ScheduleStartDT,
ScheduleEndDT,
dateadd(hh, -1, cast(year(ScheduleStartDT) as varchar)+'-'+cast(month(ScheduleStartDT) as varchar)+'-'+cast(day(ScheduleStartDT) as varchar)) as shiftP_start,
dateadd(hh, 7, cast(year(ScheduleStartDT) as varchar)+'-'+cast(month(ScheduleStartDT) as varchar)+'-'+cast(day(ScheduleStartDT) as varchar)) as shiftP_end,
dateadd(hh, 7, cast(year(ScheduleStartDT) as varchar)+'-'+cast(month(ScheduleStartDT) as varchar)+'-'+cast(day(ScheduleStartDT) as varchar)) as shift0_start,
dateadd(hh, 15, cast(year(ScheduleStartDT) as varchar)+'-'+cast(month(ScheduleStartDT) as varchar)+'-'+cast(day(ScheduleStartDT) as varchar)) as shift0_end,
dateadd(hh, 15, cast(year(ScheduleStartDT) as varchar)+'-'+cast(month(ScheduleStartDT) as varchar)+'-'+cast(day(ScheduleStartDT) as varchar)) as shift1_start,
dateadd(hh, 23, cast(year(ScheduleStartDT) as varchar)+'-'+cast(month(ScheduleStartDT) as varchar)+'-'+cast(day(ScheduleStartDT) as varchar)) as shift1_end,
dateadd(hh, 23, cast(year(ScheduleStartDT) as varchar)+'-'+cast(month(ScheduleStartDT) as varchar)+'-'+cast(day(ScheduleStartDT) as varchar)) as shift2_start,
dateadd(hh, 31, cast(year(ScheduleStartDT) as varchar)+'-'+cast(month(ScheduleStartDT) as varchar)+'-'+cast(day(ScheduleStartDT) as varchar)) as shift2_end,
cast(year(ScheduleStartDT) as varchar)+'-'+cast(month(ScheduleStartDT) as varchar)+'-'+cast(day(ScheduleStartDT) as varchar) as day_string
from Shift_Diff) as ShiftDiff2
January 9, 2008 at 6:41 am
Well this has been fun and brightened up a quiet morning 😀
Heres a shift calculating function you can use:
alter function dbo.calc_shift (@Time_start as datetime, @Time_end as datetime, @shift_start as int, @shift_end as int, @day_offset as int) returns int
as
begin
declare @ShiftStartT as datetime
declare @ShiftEndT as datetime
declare @returnval as int
set @ShiftStartT = dateadd(hh, @Shift_Start+(@day_offset*24), cast(year(@Time_Start) as varchar)+'-'+cast(month(@Time_Start) as varchar)+'-'+cast(day(@Time_Start) as varchar))
-- account for shift end being the next day
if @shift_end > @shift_start
set @ShiftEndT = dateadd(hh, @Shift_End+(@day_offset*24), cast(year(@Time_Start) as varchar)+'-'+cast(month(@Time_Start) as varchar)+'-'+cast(day(@Time_Start) as varchar))
else
set @ShiftEndT = dateadd(hh, @Shift_End+24+(@day_offset*24), cast(year(@Time_Start) as varchar)+'-'+cast(month(@Time_Start) as varchar)+'-'+cast(day(@Time_Start) as varchar))
Return (select
case
-- first 2 cases, work is out side of shift time
when @Time_end <= @ShiftStartT
or @Time_start >= @ShiftEndT
then 0
-- third case, start and end times are wholly within the shift
when @Time_start >= @ShiftStartT
and @Time_end <= @ShiftEndT
then datediff(hh, @Time_start, @Time_end)
-- fourth case start time is before the shift, end time is in the shift
when @Time_start <= @ShiftStartT
and @Time_end > @ShiftStartT
and @Time_end < @ShiftEndT
then datediff(hh, @ShiftStartT, @Time_end)
-- fifth case start time is in the shift, end time is after
when @Time_start > @ShiftStartT
and @Time_start < @ShiftEndT
and @Time_end >= @ShiftEndT
then datediff(hh, @Time_start, @ShiftEndT)
-- sixth and final, start time is before the shift starts and end time is after the shift starts
when @Time_start <= @ShiftStartT
and @Time_end >= @ShiftEndT
then datediff(hh, @ShiftStartT, @ShiftEndT)
end )
end
This turns the SQL into:
select
dbo.calc_shift(ScheduleStartDT, ScheduleEndDT, 23, 7, -1) as [2300D-1 to 0700],
dbo.calc_shift(ScheduleStartDT, ScheduleEndDT, 7, 15, 0) as [0700 to 1500],
dbo.calc_shift(ScheduleStartDT, ScheduleEndDT, 15, 23, 0) as [1500 to 2300],
dbo.calc_shift(ScheduleStartDT, ScheduleEndDT, 23, 7, 0) as [2300 to 0700D+1],
duration,
ScheduleStartDT as StartTime,
ScheduleEndDT as EndTime
from Shift_Diff
January 9, 2008 at 10:43 am
You Rock!!
Thank you for your help. I had to tweak it a little in order to get the minutes but just getting the first part was tremendously helpful.
Thanks again,
Doug
January 10, 2008 at 8:41 am
No problem 🙂
remember I said how if i found a more elegant solution I'd post it.
Well with my mathematical head on this morning a slightly neater approach dawned on me:
take the total shift time = time_end - time_start
subtract from that positive difference between when the work started and when the shift started = work_start - time_start
subtract from that the psoitive difference between when the work ends and the shift ends = time_start - time_end
The only downfall in this is that there is no function in SQL that only returns positive numbers eg:
function(5) = 5
function(-5) = 0
here's the final SQL for 2 of the shifts:
with ShiftDiff2(Duration, ScheduleStartDT, ScheduleEndDT, shift1_start, shift1_end, shift2_start, shift2_end, shift3_start, shift3_end, shift4_start, shift4_end)
as
(select
duration,
ScheduleStartDT,
ScheduleEndDT,
dateadd(hh, -1, cast(year(ScheduleStartDT) as varchar)+'-'+cast(month(ScheduleStartDT) as varchar)+'-'+cast(day(ScheduleStartDT) as varchar)) as shift1_start,
dateadd(hh, 7, cast(year(ScheduleStartDT) as varchar)+'-'+cast(month(ScheduleStartDT) as varchar)+'-'+cast(day(ScheduleStartDT) as varchar)) as shift1_end,
dateadd(hh, 7, cast(year(ScheduleStartDT) as varchar)+'-'+cast(month(ScheduleStartDT) as varchar)+'-'+cast(day(ScheduleStartDT) as varchar)) as shift2_start,
dateadd(hh, 15, cast(year(ScheduleStartDT) as varchar)+'-'+cast(month(ScheduleStartDT) as varchar)+'-'+cast(day(ScheduleStartDT) as varchar)) as shift2_end,
dateadd(hh, 15, cast(year(ScheduleStartDT) as varchar)+'-'+cast(month(ScheduleStartDT) as varchar)+'-'+cast(day(ScheduleStartDT) as varchar)) as shift3_start,
dateadd(hh, 23, cast(year(ScheduleStartDT) as varchar)+'-'+cast(month(ScheduleStartDT) as varchar)+'-'+cast(day(ScheduleStartDT) as varchar)) as shift3_end,
dateadd(hh, 23, cast(year(ScheduleStartDT) as varchar)+'-'+cast(month(ScheduleStartDT) as varchar)+'-'+cast(day(ScheduleStartDT) as varchar)) as shift4_start,
dateadd(hh, 31, cast(year(ScheduleStartDT) as varchar)+'-'+cast(month(ScheduleStartDT) as varchar)+'-'+cast(day(ScheduleStartDT) as varchar)) as shift4_end
from Shift_Diff)
select
ScheduleStartDT,
ScheduleEndDT,
Duration,
case
-- ignore anything outside the shift times
when ScheduleEndDT <= shift1_start
or ScheduleStartDT >= shift1_end
then 0
else
datediff(hh, shift1_start, shift1_end)
-- only interested in a positive difference
- case when datediff(hh, shift1_start, ScheduleStartDT) < 0 then 0 else datediff(hh, shift1_start, ScheduleStartDT) end
- case when datediff(hh, ScheduleEndDT, shift1_end) < 0 then 0 else datediff(hh, ScheduleEndDT, shift1_end) end
end as [2300D-1 to 0700],
case
-- ignore anything outside the shift times
when ScheduleEndDT <= shift2_start
or ScheduleStartDT >= shift2_end
then 0
else
datediff(hh, shift2_start, shift2_end)
-- only interested in a positive difference
- case when datediff(hh, shift2_start, ScheduleStartDT) < 0 then 0 else datediff(hh, shift2_start, ScheduleStartDT) end
- case when datediff(hh, ScheduleEndDT, shift2_end) < 0 then 0 else datediff(hh, ScheduleEndDT, shift2_end) end
end as [0700 to 1500]
from ShiftDiff2
January 10, 2008 at 10:47 am
I Found this workaround on this site. With this you can return a postive number of zero from a function.
DECLARE @NBR INT
SET @NBR = -123456789
SELECT round(@NBR,-1-floor(log10(abs(@NBR))))
more info:
http://www.sqlservercentral.com/articles/Advanced+Querying/afixfunctionintsql/2487/
January 15, 2008 at 4:39 am
Hi Adam,
I've seen and made similar work arounds but they look so hacky I can't bring myself to use them in real life applications.
For some complex queries, like this one or others where you may have a subquery in the select then the case..when..else syntax will become hard to read and result in possibly longer execution times.
I built this script, have a look at the execution plans for each of the 3 queries, I'm a big fan of derived tables (and CTE's) as they can really tidy up large queries and speed up some stubbornly slow queries.
create table #test1 (
idcol varchar(10),
numcol int)
insert into #test1 values ('a',1)
insert into #test1 values ('b',0)
insert into #test1 values ('c',-1)
create table #test2 (
idcol varchar(10),
charcol varchar(100))
insert into #test2 values ('a', 'hello')
insert into #test2 values ('b', 'whatever')
insert into #test2 values ('c', 'goodbye')
-- with CTE
;with myCTE (idcol, numcol, charcol) as
(
select idcol, (select numcol from #test1 where idcol = a.idcol), charcol
from #test2 a)
select
charcol,
case
when numcol < 0 then 0
else numcol
end as NewNumCol
from myCTE
-- with derived table
select
charcol,
case
when numcol < 0 then 0
else numcol
end as NewNumCol
from (
select idcol, (select numcol from #test1 where idcol = a.idcol) as numcol, charcol
from #test2 a) as MyDerived
-- with subqueries
select
charcol,
case
when (select numcol from #test1 where idcol = a.idcol) < 0 then 0
else (select numcol from #test1 where idcol = a.idcol)
end as NewNumCol
from #test2 a
/*
drop table #test1
drop table #test2
*/
January 15, 2008 at 5:52 am
Adam Haines (1/10/2008)
I Found this workaround on this site. With this you can return a postive number of zero from a function.DECLARE @NBR INT
SET @NBR = -123456789
SELECT round(@NBR,-1-floor(log10(abs(@NBR))))
more info:
http://www.sqlservercentral.com/articles/Advanced+Querying/afixfunctionintsql/2487/
Adam... just so you know, that function doesn't actually work... it returns a zero for positive numbers AND returns a "domain error" if @NBR happens to be a zero...
DECLARE @NBR INT
SET @NBR = 123456789
SELECT round(@NBR,-1-floor(log10(abs(@NBR))))
GO
DECLARE @NBR INT
SET @NBR = 0
SELECT round(@NBR,-1-floor(log10(abs(@NBR))))
-----------
0
(1 row(s) affected)
A domain error occurred.
-----------
--Jeff Moden
Change is inevitable... Change for the better is not.
January 15, 2008 at 6:12 am
this works for integer values up to ~700
isnull(ceiling(log(nullif(floor(exp( @value )), 0))), 0)
as I said previously though, I would avoid like the plague
January 15, 2008 at 9:15 am
Jeff Moden (1/15/2008)
Adam Haines (1/10/2008)
Adam... just so you know, that function doesn't actually work... it returns a zero for positive numbers AND returns a "domain error" if @NBR happens to be a zero...
I don't know about the rest of the function, but log(0) is going to return an error (a la divide by 0).....It's supposed to return "infinity" if my math chip isn't rusted out.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 15, 2008 at 1:06 pm
Adam... just so you know, that function doesn't actually work... it returns a zero for positive numbers AND returns a "domain error" if @NBR happens to be a zero...
DECLARE @NBR INT
SET @NBR = 123456789
SELECT round(@NBR,-1-floor(log10(abs(@NBR))))
GO
DECLARE @NBR INT
SET @NBR = 0
SELECT round(@NBR,-1-floor(log10(abs(@NBR))))
While I agree this is not the best solution, I just wanted to point out that this is possible. Additionally, any error catching logic could be easily implemented to avoid problems.
DECLARE @NBR INT
SET @NBR = -2147483647 --largest int
SELECT CASE WHEN @NBR > 0 THEN
@NBR
WHEN @NBR = 0 THEN
0
ELSE
round(@NBR,-1-floor(log10(abs(@NBR))))
END
January 15, 2008 at 4:41 pm
Wouldn't this be easier?
SELECT CASE
WHEN @Nbr > 0 THEN @Nbr
ELSE 0
END
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2008 at 3:32 am
Jeff,
(Did you miss my earlier posts?)
Case would be a lot easier for very simple examples but sometimes @nbr is derived and for clarity/optimization it would be better to avoid the repetition.
select
charcol,
case
when (select numcol from #test1 where idcol = a.idcol) < 0 then 0
else (select numcol from #test1 where idcol = a.idcol)
end as NewNumCol
from #test2 a
Obviously subqueries are bad and should be avoided at all costs but as with cursors, sometimes they make a lot more sense than the alternatives.
January 16, 2008 at 6:58 am
Samuel Vella (1/16/2008)
Jeff,(Did you miss my earlier posts?)
Case would be a lot easier for very simple examples but sometimes @nbr is derived and for clarity/optimization it would be better to avoid the repetition.
selectcharcol,
case
when (select numcol from #test1 where idcol = a.idcol) < 0 then 0
else (select numcol from #test1 where idcol = a.idcol)
end as NewNumCol
from #test2 a
Obviously subqueries are bad and should be avoided at all costs but as with cursors, sometimes they make a lot more sense than the alternatives.
Heh... no, I didn't miss your earlier posts and you certainly had the right idea 🙂 of using a more obvious CASE statement instead of a complicated formula that didn't actually work. I wanted to provide the simplest answer I could for the positive number problem so that folks could either use it directly or turn it into a function, whichever whim struck them.
You are correct... not all subqueries are bad. Usually, though, "correlated" subqueries are very bad. They form hidden RBAR that can be just as bad and is frequently worse than a Cursor or While Loop because they do a SELECT for every row in the outer table. If there's a "Triangular Join" in the sub-query, it'll make a Cursor look like a Saint . 😉
In the case of your code, there's simply no need for the correlated subqueries...
SELECT CharCol,
CASE
WHEN NumCol > 0 THEN NumCol
ELSE 0
END AS NewNumCol
FROM #Test2
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply