CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Steve
Steve

Reputation: 355

SQL Server Tracking Scheduled Shifts when the day varies

I'm not even sure where to start in solving this one, I need to query production data from our MS SQL 2012 Db that has a datetime stamp based on the shift it was recorded in, the tricky part is that we run 4 12hr shifts in a pattern of 2 on, 2 off, 3 on, 2 off i.e. here's the first 3wks of Jan 2013:

S   M   T   W   T   F   S
        1   2   3   4   5
        C/D A/B A/B C/D C/D

6   7   8   9   10  11  12
C/D A/B A/B C/D C/D A/B A/B

13  14  15  16  17  18  19
A/B C/D C/D A/B A/B C/D C/D

A&C are morning shifts running 7:00-19:00 and B&D are night running 19:00-7:00 I'm fairly new to SQL and haven't had to do anything like this yet, I know I can easily determine the data for the 1/1/2013 AM shift with:

select *
from Line05
where L05Time BETWEEN '01/01/2013 07:00' AND '01/01/2013 19:00'

for example but I'm not sure how I can tie that to C Shift.

Upvotes: 1

Views: 2549

Answers (2)

HABO
HABO

Reputation: 15841

The following cranks out a table of shifts. It isn't exactly clear what you want to do, but you should be able to reverse engineer determining the shift from the date/time of an event using some of the calculations shown here.

EDIT: Corrected case to handle to 2/2/3/2 pattern.

; with Samples as (
  -- Start at the beginning of 2013.
  select Cast( '01-01-2013 00:00' as DateTime ) as Sample
  union all
  -- Add hours up to the desired end date.
  select DateAdd( hour, 1, Sample )
    from Samples
    where Sample <= '2013-01-30'
  ),
  ExtendedSamples as (
  -- Calculate the number of days since the beginning of the first shift on 1/1/2013.
  select Sample, DateDiff( hour, '01-01-2013 07:00', Sample ) / 24 as Days
    from Samples ),
  Shifts as (
  -- Calculate the shifts for each day.
  select *,
    case when ( Days + 1 ) % 9 in ( 0, 1, 4, 5 ) then 'C/D' else 'A/B' end as Shifts
    from ExtendedSamples )
  select *,
    case when DatePart( hour, Sample ) between 7 and 18 then Substring( Shifts, 1, 1 ) else Substring( Shifts, 3, 1 ) end as Shift
    from Shifts
    option ( maxrecursion 0 )

Upvotes: 2

jerrylagrou
jerrylagrou

Reputation: 511

I'm sure it will get even tricker when there is a holiday or a shutdown occurs. You are thinking like a programmer, you are thinking there is an algorithm that can determine your answer. Instead, if would advise that you think like a data-guy. There should be a source of information somewhere that has the answer you seek. Ask the person who sets up the schedule if he knows. There should be a table somewere that tells you what shifts are assigned to what time slots. Use the data it contains to get your answer.

Upvotes: 1

Related Questions