Finding which shift shipped an order?

[Dan5]

1. I have a start and end time for each shift (some go overnight spanning two dates)
2. I have a ship date for each order

How can I determine which orders were shipped by which shifts?

Thanks, 

[PhilipMannering]

I would use the Generate Rows tool to give you all the dates for a persons shift (one row per day) > then join that generated field to ship date. You can then summarize how you wish. If you provide some dummy data with expected output I can mock something up.

[Dan5]

hmm, not sure it would work; here are some sample shifts and ship dates:

Shift 1 - 10 PM 10/10/2021 - 2 AM 10/11/2021    -> Would i then be generating a date row for both 10/10 and 10/11? If so, how would i determine that the shipment occurred within this 4 hour block, and not another shift that occurred on either of those days?

Sample shipments: 9 PM 10/10/2021   , 11 PM 10/10/2021,  1 AM 10/11/2021 , 3 AM 10/11/2021

[mceleavey]

@Dan5 ,

can you provide some representative data so we can help?

M

[PhilipMannering]

I think in this case you would generate for each hour. This works providing the level of details is hourly (otherwise you would have to generate rows by the minute!) 

See attached.

[DawnDuong]

hi @Dan5 

I suppose that you have 2 separate datatables, one that contains all the shift info (says M records) and another that contains all the shipping info (says N record)

One way to solve your use case can be as follow:

1) For every shift, make sure that you have one column for "Start datetime" and one column for "End datetime". Both set to "Datetime" format. 

2) Append the Shift datatable with the Shipment table. This way, you will have MxN records in the output. 

3) Use Filter Tool to filter out records where [shipping time > End datetime] or [shipping time < Start datetime].

Then you will have your required output.

If the shipping datatable is too large, thus making Append to "expensive" to run, you can use iterative macro to run though one SHIFT at a time, line by line. This case, you do not need to use Append, just Filter Out will do.

Dawn.

[Dan5]

Thanks, this is awesome, and clear! 

I will try to implement this, but i am pretty certain it will work. 

Thanks!