[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 474

# Really stuck on shift pattern SQL query

Hi All

If a have a table

Start      End      Rate
07:00      19:59      WeekDay day
20:00      06:59      Weekday evening
07:00      19:59      Weekend day
20:00      06:59      Weekend evening
07:00      19:59      Weekend day
20:00      06:59      Weekend evening
07:00      19:59      Bank Holiday day
20:00      06:59      Bank Holiday evening

I will feed this with a startdate and and end date and wish to have an output thus

Shift Start: Saturday 29th April 18:00
Shift End: Sunday 30th April 01:00

Start      End      Rate
Sat 29/04/06 18:00      Sat 29/04/06 20:00      Weekend Day
Sat 29/04/06 20:00      Sun 30/04/06 01:00      Weekend Evening

Overlap
The default time bands defined for a client will not necessarily fall neatly into one day.  For instance, a Saturday evening rate falls into Sunday morning, and a Sunday evening rate falls into the Monday, and so on.  The system should calculate overlaps based on the following example:
Shift Time:       20:00 Sat – 09:00 Sun
Default Bands for Client:       19:00 – 07:00 Sat Evening Rate, 07:00 – 19:00 Sun Day Rate
Time Bands Generated for Shift:
20:00 (Sat) – 07:00 (Sun) … Sat Evening Rate
07:00 (Sun) – 09:00 (Sun) … Sun Day Rate

I am really stuck on this one people any help will be appreciated

regards

steve

0
davoman
1 Solution

Commented:
Try this!

SELECT dateiff(hour,Start,End),
CASE
WHEN datepart(weekday,Start)=6 AND datepart(weekday,Start)=6 THEN 'Weekend Day'
WHEN datepart(weekday,Start)=6 AND datepart(weekday,Start)=7 THEN 'Weekend Evening'
WHEN datepart(weekday,Start)=7 AND datepart(weekday,Start)=7 THEN 'Sun Day'
--add more condition if you want
END as Rate
FROM yourtable
0

Author Commented:
hi there

But if the dates were longer ie

start 10/04/06 13:00
and end 21/04/06 20:00 then

we would need all the shifts in between in the output table
0

Commented:
try to match that with one of the bands, thats your startdate for the generated shift

then check if the enddate is within the band,
if so use enddate for generated shift.

if not, use the enddate of the band
and start the proces over,
this time using the enddate of the band as the startdate (first line)

assuming your shifts cross a finite number of bands,
you can use a case - when solution for this.

0

Author Commented:
argh i can't get my head round it, could someone post any code or psudo code to get me going , i'm losing my hair
0

Commented:
-- kinda lame, ignoring days for now and only for one overlap:

select a.startdate,
case when a.enddate between b.startdate and b.enddate then a.enddate
else b.enddate end as enddate,
from shift a
left join shift b on a.startdate between b.startdate and b.enddate
union
select b.enddate
case when a.enddate between c.startdate and c.enddate then a.enddate
else c.enddate end as enddate -- this means overlapping more than one shift --> repeat this pattern
from shift a
left join shift b on a.startdate between b.startdate and b.enddate
left join shift c on b.enddate between c.startdate and c.enddate
where a.enddate not between b.startdate and b.enddate

--it's far from elegant, but it might give you a start
0

Commented:
This is close, but it might give you something to work with:

DECLARE @StartTime datetime, @EndTime datetime, @StartWeekend bit, @EndWeekend bit
SET @StartTime = '4/30/2006 18:00'
SET @EndTime = '5/1/2006 01:00'
SELECT @StartWeekend = CASE DATEPART(dw,@StartTime) WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE 0 END
SELECT @EndWeekend = CASE DATEPART(dw,@EndTime) WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE 0 END

SELECT *,
CASE WHEN StartTime > EndTime THEN 24 ELSE 0 END,
DATEADD(hh,(CASE WHEN StartTime > EndTime THEN 24 ELSE 0 END),CONVERT(datetime,convert(varchar(10),@EndTime,1) + ' ' + EndTime)) EndTime24,
CONVERT(datetime,convert(varchar(10),@StartTime,1) + ' ' + StartTime) StartTime,
CONVERT(datetime,convert(varchar(10),@EndTime,1) + ' ' + EndTime) EndTime,
@StartTime as ActStart,
@EndTime as ActEnd
FROM Shifts
WHERE ((@StartTime
BETWEEN (CONVERT(datetime,convert(varchar(10),@StartTime,1) + ' ' + StartTime))
AND (CONVERT(datetime,convert(varchar(10),@EndTime,1) + ' ' + EndTime)) AND Weekend = @StartWeekend)
OR (@EndTime
BETWEEN (CONVERT(datetime,convert(varchar(10),@StartTime,1) + ' ' + StartTime))
AND (DATEADD(hh,(CASE WHEN StartTime > EndTime THEN 24 ELSE 0 END),CONVERT(datetime,convert(varchar(10),@EndTime,1) + ' ' + EndTime))) AND Weekend = @EndWeekend))
AND id NOT IN (7,8) -- need bank holiday table to join on.
0

Author Commented:
thanks a lot
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.