SQL - Work center schedule list - calculate lead times [closed]

I need help how to calculate the workcenter schedule list in SQL language, I mean the start and finnish dates of the operation when I know the input parameters as Lead time operations? This is complicated by the fact that some work centers may have 1/2/3 of the work shift per day. The table shows an example of one order with input data from the database and what the output in the form of Start/ Finnish date time should look like (yellow columns). Can anyone help me with this how to write an SQL script with the desired result? I tried in several ways, but it never worked for me in general for various examples. Thanks in advance for the ideas.

table shows an example: [1]: https://i.stack.imgur.com/xFKvC.jpg

The resulting Start / Finnish date time values are listed in the "table example" link above. Here is my one MS SQL query that doesn't work properly in the calculation (calculating only with capacity one śhift per day), but you may find an error in:

CREATE TABLE temp_wcs 
(SEQN INT NOT NULL,
ORDER_NO INT NOT NULL,
PRODUCT_NO INT NOT NULL,
ORDER_QUANTITY INT NOT NULL,
OPERATION_NO INT NOT NULL,
WORK_CENTER INT NOT NULL,
WORKCENTE_SHIFT_CAPACITY INT NOT NULL,
RUN_TIME DECIMAL(10,2) NOT NULL,
SETUP_TIME DECIMAL(10,2) NOT NULL,
LEAD_TIME DECIMAL(10,4),
OPE_START_DATE_TIME DATETIME,
OPE_FINNISH_DATE_TIME DATETIME) 

GO 

INSERT INTO temp_wcs VALUES 
(1, 20210001, 10000001, 100, 5, 101, 1, 6, 60, null, null, null), 
(2, 20210001, 10000001, 100, 10, 102, 1, 4, 20, null, null, null),
(3, 20210001, 10000001, 100, 15, 103, 2, 5, 10, null, null, null),
(4, 20210001, 10000001, 100, 20, 104, 2, 1, 20, null, null, '10.12.2021 22:00:00') 

GO 

with temp as 
(select a.*,
((a.ORDER_QUANTITY * a.RUN_TIME) + SETUP_TIME) / 60 as LT_HR,
cast(((((a.ORDER_QUANTITY * a.RUN_TIME) + SETUP_TIME) / 60) / 8.0 ) as int) + ((((a.ORDER_QUANTITY * a.RUN_TIME) + SETUP_TIME) / 60) / 8.0) % 1.0 * 8 / 24 as LT
from temp_wcs a), 

t_CummLT as 
(select *,
Sum( LT ) Over (partition by ORDER_NO, PRODUCT_NO order by SEQN desc) as CummLT
from temp), 

t_LTshifts as 
(select *,
case when CummLT % 1.0 > (8.0/24.0)
     then (16.0/24.0)
     else 0 end as LTshifts
from t_CummLT), 

t_CummLTshifts as
(select *,
Sum( LTshifts) Over (partition by ORDER_NO, PRODUCT_NO order By SEQN desc) as CummLTshifts 
from t_LTshifts), 

result as 
(select a.SEQN, a.ORDER_NO, a.PRODUCT_NO, a.OPERATION_NO, a.LT_HR,
Cumm = a.CummLT + a.CummLTshifts,
a.OPE_FINNISH_DATE_TIME 
from t_CummLTshifts a 
where a.SEQN = (select max(SEQN) from temp b where b.ORDER_NO = a.ORDER_NO and b.PRODUCT_NO = a.PRODUCT_NO group by ORDER_NO, PRODUCT_NO)

union all

select b.SEQN, b.ORDER_NO, b.PRODUCT_NO, b.OPERATION_NO, b.LT_HR,
Cumm = b.CummLT + b.CummLTshifts,
FINISH = a.OPE_FINNISH_DATE_TIME - a.Cumm 
from result a, t_CummLTshifts b 
where a.ORDER_NO = b.ORDER_NO and a.PRODUCT_NO = b.PRODUCT_NO and b.SEQN = a.SEQN-1)
 
select * from result order by SEQN 

from Recent Questions - Stack Overflow https://ift.tt/3xxY33b
https://ift.tt/eA8V8J