Scotty G Scotty G - 16 days ago 10
SQL Question

Counting consecutive shift and days in SQL

I am trying to display the number of consecutive Shifts and Days a team has worked. I have tried a number of solutions but just cannot get it quite right.

The data is Date, Shift, Crew. There is a Day and Night Shift and 3 crews.
ConsecutiveShift and ConsecutiveDay are the fields I am trying to produce. The output will be used to look at productivity based on the number of consecutive shifts/days worked.

Any help is very much appreciated

Dateop Shift Crew
2015-12-23 00:00:00 D B Crew
2015-12-23 00:00:00 N A Crew
2015-12-24 00:00:00 D B Crew
2015-12-24 00:00:00 N A Crew
2015-12-25 00:00:00 D C Crew
2015-12-25 00:00:00 N B Crew
2015-12-26 00:00:00 D C Crew
2015-12-26 00:00:00 N B Crew
2015-12-27 00:00:00 D C Crew
2015-12-27 00:00:00 N B Crew
2015-12-28 00:00:00 D C Crew
2015-12-28 00:00:00 N B Crew
2015-12-29 00:00:00 D C Crew
2015-12-29 00:00:00 N B Crew
2015-12-30 00:00:00 D A Crew
2015-12-30 00:00:00 N C Crew
2015-12-31 00:00:00 D A Crew
2015-12-31 00:00:00 N C Crew
2016-01-01 00:00:00 D A Crew
2016-01-01 00:00:00 N C Crew
2016-01-02 00:00:00 D A Crew
2016-01-02 00:00:00 N C Crew


Desired Output - First 3 columns are the original data, column 4 and 5 are the counts I am trying to produce


Answer

The following query should work, using window functions:

With cte As (
    Select DateOp, Shift, Crew,
        DateDiff(Day, DateOp, '2016-01-01') + Row_Number() Over (Partition By Crew, Shift Order By DateOp) As ShiftIsland,
        DateDiff(Day, DateOp, '2016-01-01') + Row_Number() Over (Partition By Crew Order By DateOp) As DayIsland
      From [YourTable])      
Select DateOp, Shift, Crew,
    Row_Number() Over (Partition By Crew, Shift, ShiftIsland Order By DateOp) As ConsecutiveShifts,
    Row_Number() Over (Partition By Crew, DayIsland Order By DateOp) As ConsecutiveDays
  From cte
  Order By Crew, DateOp, Shift;