Solved

# Really stuck on shift pattern SQL query

Posted on 2006-05-04
432 Views
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
Question by:davoman

LVL 17

Expert Comment

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

LVL 1

Author Comment

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

LVL 11

Expert Comment

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

LVL 1

Author Comment

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

LVL 11

Expert Comment

-- 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

LVL 13

Accepted Solution

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

LVL 1

Author Comment

thanks a lot
0

## Featured Post

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.