dear all;
i have a problem .
I have table of work orders having start date and end date for-each row and minutes to finish the stage of work order
I have maintenance start date , maintenance end date , maintenance minutes , factory shift start time and factory shift end time
factory having weekly break is Friday and Saturday
how to expand dates in table by maintenance minutes ?
note :
WantedMinutsToAdd = 495 not 600 because start maintenance is '2015-12-06 11:45:00' and shift end time is 03:30 PM that mean the effected minutes = 11:45:00 AM - 03:30 PM = 225
and end maintenance is '2015-12-07 13:00:00' and shift start time is 08:30 AM that mean the effected minutes = 08:30:00 AM - 01:00 PM = 270
and else outside the daily working hours
600 - 495 = 105 is not effected because it is outside the daily working hours
Please take into consideration the official holidays
`DECLARE @StartMaintenance DATETIME = '2015-12-06 11:45:00'
, @EndMaintenance DATETIME = '2015-12-07 13:00:00'
, @MaintenanceMinuts INT = 10*60
, @ShiftStartTime TIME = '08:30 AM'
, @ShiftEndTime TIME = '03:30 PM'
SELECT @StartMaintenance AS StartMaintenance , @EndMaintenance AS EndMaintenance , @MaintenanceMinuts AS MaintenanceMinuts,@ShiftStartTime AS ShiftStartTime , @ShiftEndTime AS ShiftEndTime
SELECT *
INTO #tbl
FROM (
SELECT '2015-12-06 11:29AM' StartDate ,40 StageWantedMinutes ,'2015-12-06 12:08PM' EndDate ,1 rn ,495 WantedMinutsToAdd ,202 MinutsOfEndDateToFinshDay ,241 MinutsOfStartDateToFinshDay UNION ALL
SELECT '2015-12-06 11:29AM',30,'2015-12-06 11:58AM',2,495,212,241 UNION ALL
SELECT '2015-12-06 11:29AM',20,'2015-12-06 11:48AM',3,495,222,241 UNION ALL
SELECT '2015-12-06 11:29AM',24,'2015-12-06 11:52AM',4,495,218,241 UNION ALL
SELECT '2015-12-06 11:48AM',40,'2015-12-06 12:27PM',5,495,183,222 UNION ALL
SELECT '2015-12-06 11:52AM',48,'2015-12-06 12:39PM',6,495,171,218 UNION ALL
SELECT '2015-12-06 11:58AM',60,'2015-12-06 12:57PM',7,495,153,212 UNION ALL
SELECT '2015-12-06 12:08PM',80,'2015-12-06 1:27PM',8,495,123,202 UNION ALL
SELECT '2015-12-06 12:27PM',60,'2015-12-06 1:26PM',9,495,124,183 UNION ALL
SELECT '2015-12-06 12:39PM',72,'2015-12-06 1:50PM',10,495,100,171 UNION ALL
SELECT '2015-12-06 12:57PM',90,'2015-12-06 2:26PM',11,495,64,153 UNION ALL
SELECT '2015-12-06 1:27PM',120,'2015-12-06 3:26PM',12,495,4,123
) t
SELECT * FROM #tbl
DROP TABLE #tbl`