Counting consecutive shift and days in SQL

1
votes

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

2
sqlsql-serversql-server-2012recursive-querygaps-and-islands
What's the DBMS (Sql Server, Oracle, MySQL, etc)? - Wagner DosAnjos
I don't understand your required output. Can you explain further? Why does consecutive reset at 5? Is that a hard limit or based on some kind of weekday window? Why is 30th = 1,6 instead of 1,1? - Nick.McDermaid
Could you paste the input data as text rather than a picture. Makes it easier for others to help you. - mendosi
Database is SQL Server 2012. Nick, Sorry for the poor explanation (I know what I want, lol). There are 3 Crews - A, B and C Crew. Operations run 24/7, each crew work 12 hour shifts, 5 Days, 5 Nights, 5 Days off. The shift counter will reset after each crew works their 5 days and then their 5 nights but the days will continue to 10 days and then reset when they have their days off. The analysis I want to conduct is based on the number of days they have worked, maybe on their 3rd and 4th Night shift the crew is less productive etc. - Scotty G

2 Answers

0
votes

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;
-1
votes

I think this is a basic row_number(). For the data you have provided:

select t.*,
       row_number() over (partition by shift, crew order by date) as consecutiveshift,
       row_number() over (partition by crew order by date) as consecutiveday
from t;

This at least returns the data that you have in your question.