Sample Header Ad - 728x90

Count Split across Shifts

0 votes
0 answers
33 views
I have a table which registers orders with a PlanStartTime and PlanEndTime. The customer has three shifts in a day starting from 7 AM - 3 PM, 3 PM - 11 PM, 11 PM - 7 AM (next day) Say an order is planned from 17-Mar-20 8 AM to 17-Mar-20 9 AM and counts of items to be produced is 10, then my first shift count for the work order is 10
E.g

PO#		StartDate			EndDate				Count
--------------------------------------------------------
A1		17-Mar-20 8:00		17-Mar-20 9:00		10
--------------------------------------------------------

Expected Results
--------------------------------------------------
Date			Shift		Items to be produced
--------------------------------------------------
17-Mar-20		1			10

My Current Problem Statement is in case the work order is to execute across days how can i have the counts distributed across days per shift
Problem Statement

PO#		StartDate			EndDate			Count
--------------------------------------------------------
A1		17-Mar-20 8:00		19-Mar-20 9:00		490
--------------------------------------------------------

Expected Results
--------------------------------------------------
Date			Shift		Items to be produced
--------------------------------------------------
17-Mar-20		1			70
17-Mar-20		2			80
17-Mar-20		3			80
18-Mar-20		1			80
18-Mar-20		2			80
18-Mar-20		3			80
19-Mar-20 		1			20

Note Shift 1 stands for 7 AM to 3 PM Shift 2 stands for 3 PM to 11 PM Shift 3 stands for 11PM to 7 AM (next day) The count for the work order needs to be equally distributed across different shifts. The start date to end date duration may vary from 1 hr to max 7 days.
Asked by Kaushik (11 rep)
Mar 17, 2020, 06:20 PM