create table tb(Machine varchar(5),PNO varchar(10),PQty decimal(8,2),DayQty decimal(8,2),OrderQty int)
insert into tb select 'A','001',100,40,10
insert into tb select 'A','002',30,20,25
insert into tb select 'A','003',20,10,36
insert into tb select 'B','001',100,30,1
insert into tb select 'B','002',40,10,4 
go
;with cte as(
--不同机器的第一行
select Machine,[Day]=1,PNO,AQty=convert(decimal(8,2),(case when PQty>dayQty then DayQty else PQty end)),
    PQty=convert(decimal(8,2),PQty-(case when PQty>DayQty then DayQty else PQty end)),OrderQty,
    Rate=convert(decimal(8,6),(case when PQty<DayQty then 1.0-PQty/DayQty else 0.0 end))
from tb a
where OrderQty=(select top 1 OrderQty from tb where machine=a.machine order by OrderQty)
union all
--当某机器某OrderQty上一次序未生产完时
select a.Machine,[Day]=a.[Day]+1,a.PNO,AQty=convert(decimal(8,2),(case when a.PQty>b.DayQty then b.DayQty else a.PQty end)),
    PQty=convert(decimal(8,2),(case when a.PQty>b.DayQty then a.PQty-b.DayQty else 0.0 end)),a.OrderQty,
    Rate=convert(decimal(8,6),(case when a.PQty<b.DayQty then 1.0-a.PQty/b.DayQty else 0.0 end))
from cte a inner join tb b on a.Machine=b.Machine and a.PNO=b.PNO where a.PQty>0
union all
--当某机器某QrderQty在某天生产完但剩余生产力时
select a.Machine,a.[Day],b.PNO,AQty=convert(decimal(8,2),(case when b.PQty>b.DayQty*a.rate then b.DayQty*a.Rate else b.PQty end)),
    PQty=convert(decimal(8,2),b.PQty-(case when b.PQty>b.DayQty*a.Rate then b.DayQty*a.Rate else b.PQty end)),b.OrderQty,
    Rate=convert(decimal(8,6),(case when b.PQty<b.DayQty*a.rate then (b.DayQty*a.Rate-b.PQty)/b.DayQty else 0 end))
from cte a inner join tb b on a.Machine=b.Machine
where a.Rate>0 and a.PQty=0 and a.AQty>0 and a.OrderQty<b.OrderQty 
    and not exists(select 1 from tb where Machine=a.Machine and OrderQty>a.OrderQty and OrderQty<b.OrderQty)
union all
--当某天生产完某项OrderQty且生产力刚好用完时
select a.Machine,[Day]=a.[Day]+1,b.PNO,AQty=convert(decimal(8,2),(case when b.PQty>b.DayQty then b.DayQty else b.PQty end)),
    PQty=convert(decimal(8,2),b.PQty-(case when b.PQty>b.DayQty then b.DayQty else b.PQty end)),b.OrderQty,
    Rate=convert(decimal(8,6),(case when b.PQty<b.DayQty then 1.0-b.PQty*1.0/b.DayQty else 0 end))
from cte a inner join tb b on a.Machine=b.Machine
where a.Rate=0 and a.PQty=0 and a.AQty>0 and a.OrderQty<b.OrderQty
    and not exists(select 1 from tb where Machine=a.Machine and OrderQty>a.OrderQty and OrderQty<b.OrderQty)
union all
--添加各天不生产的部分
select a.Machine,[Day]=a.[day],b.PNO,AQty=0,PQty=0,OrderQty=b.OrderQty,Raty=0.00
from cte a inner join tb b on a.OrderQty<b.OrderQty and a.Machine=b.Machine
where a.PQty>0 
)select Machine,[Day],PNO,AQty,PQty from cte order by Machine,[Day],PNO
go
drop table tb
/*
Machine Day         PNO        AQty                                    PQty
------- ----------- ---------- --------------------------------------- ---------------------------------------
A       1           001        40.00                                   60.00
A       1           002        0.00