Marking a record as day shift or night shift

[andrewmorrison]

Hi,

I have a data series which shows journeys completed by drivers. Some drivers work a day shift, and others work a night shift. In the data, an individual journey record can be as short as 5 minutes. Night shift drivers generally finish before 5am, and day shift drivers start after 5am, but not always. I would like a way to be able to categorise each record as belonging to either a day shift or night shift driver. 

A day shift driver can be identified as someone who will have no journeys that cross the midnight barrier in a shift, and night shift as someone who will always have journeys either side of midnight in a shift.

So, let's say I have a journey that starts at say 4.55am and finishes at 5.30am. I want to be able to determine whether it was done by a nightshift driver or dayshift. If it's nightshift, the same driver will have a record let's say somewhere between 9pm and midnight the previous day. If not, it's dayshift.

Any help would be much appreciated.

[andrewmorrison]

An extract of the data is above. You can see here a nightshift driver taking over at 18:19, and driving until 04:16. What I want to achieve is be able to identify all the trips by this driver in this shift as night-shift.

[Vijay_A_Verma]

The sample file is uploaded here

https://1drv.ms/x/s!Akd5y6ruJhvhuRyxFfpQ-uFrOVpA?e=kONDxu 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Objectno", "Driveruid"}, {{"MinStartTime", each List.Min([StartTime]), type datetime}, {"MaxEndTime", each List.Max([EndTime]), type datetime}, {"Temp", each _, type table [TripID=number, Objectno=text, StartTime=datetime, EndTime=datetime, Distance=number, Driveruid=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if Duration.Days(DateTime.Date([MaxEndTime])-DateTime.Date([MinStartTime]))>0 then "Night Shift" else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Objectno", "Driveruid", "MinStartTime", "MaxEndTime"}),
    #"Expanded Temp" = Table.ExpandTableColumn(#"Removed Columns", "Temp", {"TripID", "Objectno", "StartTime", "EndTime", "Distance", "Driveruid"}, {"TripID", "Objectno", "StartTime", "EndTime", "Distance", "Driveruid"})
in
    #"Expanded Temp"

[andrewmorrison]

Hi. Thank you for this. If I've understood it correctly it doesn't fully do what I am trying to achieve. The dataset I have could cover several weeks, so each driver will have multiple shifts within the dataset. In this, the grouping just pulls the first and last entry for each driver, so it works for a single shift, but not where there is multiple. Do you have any advice on how to proceed with this?

[Vijay_A_Verma]

Thanks for the clarity. I have a follow up question before I can come with the intended solution.

How do I determine the start and end of a shift? Is there any identifier or timing or something..

Hence if I have a record for say all 28 days of Feb-22 month, then how will I segregate the shifts?

[andrewmorrison]

This is the difficult bit. There should be an 8 hour gap between the end of one shift and the start of the next. There will be expections to this, but this will cover >99% of scenarios. Unfortunately there is no way of determining this with 100% certainty from the data available.

[Vijay_A_Verma]

You should post a screenshot of your data (without confidential information) to understand the problem. Then volunteers here can provide a good solution.