declare @tbl as table
(
    ItemId int,
    SOQty int,
    DIQty int ,
    IssueQty int,
    BalanceQty int,
    CreateDate datetime,
    StockQty int,
    WIPQty int
)

insert into @tbl values 
(1,10,10,0,10,'2021-12-16 19:28:32.200',10,0), 
--(2,5,5,1,4,'2021-12-17 19:28:05.200',80),
(1,15,10,10,5,'2021-12-18 19:28:34.200',30, 0),
(1,8,5,2,2,'2021-12-19 19:28:35.200',30,0)
--(2,15,15,0,15,'2021-12-20 19:28:05.200',80),
--(2,12,10,5,5,'2021-12-22 19:28:05.200',80)
--(1,15,10,10,5,'2021-12-18 19:28:34.200',30,0)
 
  
update x 
set x.StockQty = tx.StockQty  
from @tbl x
join 
    (select * 
     from 
         (select 
              *,
              row_number() over (partition by itemid order by CreateDate) as RowNo 
          from @tbl) as t 
     where t.RowNo = 1) as tx on tx.CreateDate = x.CreateDate
 
update x 
set x.StockQty = 0 
from @tbl x
join 
    (select * 
     from 
         (select 
              *,
              row_number() over (partition by itemid order by CreateDate) as RowNo 
          from @tbl) as t 
     where t.RowNo != 1) as tx on tx.CreateDate = x.CreateDate
 

declare @tbl1 as table
(
    ItemId int,
    SOQty int,
    DIQty int ,
    IssueQty int,
    BalanceQty int,
    CreateDate datetime,
    StockQty int,
    WIPQty int,
    StockAllocateQty int,
    UpdatedStockQty int
)
 
insert into @tbl1
    select 
        *, 
        BalanceQty as StockAllocateQty,
        sum(StockQty - BalanceQty) over (partition by ItemId 
                                         order by CreateDate   
                                         rows between unbounded preceding and current row) as UpdatedStockQty  
    from @tbl 
    -- order by CreateDate
 
 declare @tblItemWIPQty table
 (
 ItemId int,
 WIPQty  int
 )

 insert into @tblItemWIPQty values(1,40)
 
 
 
update x set x.WIPQty =  tt.WIPQty from @tbl1 x
join 
(select * from  
(
select top 1 * from @tbl1 where UpdatedStockQty < 0
) as t) as t on t.CreateDate = x.CreateDate 
join @tblItemWIPQty tt on tt.ItemId = x.ItemId
 

 
select *,BalanceQty as AllocateQtyWIP ,SUM(case when StockQty - BalanceQty >= 0 then StockQty -BalanceQty else WIPQty - BalanceQty end) 
over(partition by ItemId order by CreateDate   Rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as UpdatedStockQtyWIP  from @tbl1 
--ORDER BY CreateDate

 

I want to allocate BalanceQty first from StockQty and when StockQty is finished then from WIPQty

This is working fine (below is just sake of understanding).

For 1st row BalanceQty = 10 ,StockQty=10 so if we allocate all 10 Qty from Balance, that is StockAllocateQty = 10 and UpdatedStockQty = 0. (Balance - StockQty) = UpdatedStockQty

For 2nd row BalanceQty = 5, StockQty = 0 (all stock Qty is utilized on 1st Row) so if we allocate 5 Qty from balance then we will get StockAllocateQty = 5 and UpdatedStockQty = -5. (0 - 5) = -5

For 3rd row BalanceQty = 2, StockQty = 0 (all stock Qty is utilized on 1st Row) so if we allocate 2 Qty from balance then we will get StockAllocateQty = -7 and UpdatedStockQty = -5. (-5 - -2) = -7

Problem is in these 2 column AllocateQtyWIP UpdatedStockQtyWIP

Now I have extra work in progress Qty to allocate if all stock Qty is utilized on 1st row all Stock Qty is utilized so I assigned to 2nd row

For 1st row we are utilizing from stock Qty and we did not even use WIP Qty than it should be AllocateQtyWIP = 0 UpdatedStockQtyWIP = 40 but I am getting AllocateQtyWIP = 10, UpdatedStockQtyWIP = 0

Expected output for these 2 columns:

but instead I'm getting this:

expected this

CodePudding user response：

 declare @tbl as table
(
 ItemId int,  
 BalanceQty int,
 CreateDate datetime,
 StockQty int,
 WIPQty int
)


insert into @tbl values 
(1,10,'2021-12-16 19:28:32.200',30,0), 
(1,5,'2021-12-18 19:28:34.200',30,0),
(1,2,'2021-12-19 19:28:35.200',30,0)
  
update x set x.StockQty = tx.StockQty  from @tbl x
join 
(select * from 
(
select *,ROW_NUMBER()over(partition by itemid order by CreateDate) as RowNo from @tbl  
)as t where t.RowNo = 1) as tx on tx.CreateDate = x.CreateDate
 

 update x set x.StockQty = 0 from @tbl x
join 
(select * from 
(
select *,ROW_NUMBER()over(partition by itemid order by CreateDate) as RowNo from @tbl  
)as t where t.RowNo != 1) as tx on tx.CreateDate = x.CreateDate
 

 declare @tbl1 as table
(
 ItemId int, 
 BalanceQty int,
 CreateDate datetime,
 StockQty int,
 WIPQty int,
 allocateQTy int,
 UpdatedStockQty int,
 WIPQty1 int
)
 
 insert into @tbl1
select *,BalanceQty as allocateQTy ,SUM(StockQty - BalanceQty) 
over(partition by ItemId order by CreateDate   Rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as UpdatedStockQty,40  from @tbl 
 
 
 declare @tblWIPQty table
 (
 ItemId int,
 StockQty int
 )

insert into @tblWIPQty values(1,40)
 
declare @tbl2 as table
(
ItemId int, 
BalanceQty int,
CreateDate datetime,
StockQty int,
WIPQty int,
allocateQTy int,
UpdatedStockQty int,
WIPQty1 int,
allocateQTyWIP int,
UpdatedStockQtyWIP int
 
)
  

 update x set x.WIPQty =  tt.StockQty  from @tbl1 x
join 
(select * from  
(
select top 1 * from @tbl1 where UpdatedStockQty < 0
) as t) as t on t.CreateDate = x.CreateDate 
join @tblWIPQty tt on tt.ItemId = x.ItemId

 
 
insert into @tbl2 
select *,
case when SUM(StockQty - BalanceQty) over(partition by ItemId order by CreateDate   Rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) >= 0 then 0 else BalanceQty end  as AllocateQtyWIP ,
case when SUM(StockQty - BalanceQty) over(partition by ItemId order by CreateDate   Rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) < 0 then SUM(case when StockQty - BalanceQty >= 0 then StockQty - BalanceQty else WIPQty - BalanceQty end) over(partition by ItemId order by CreateDate   Rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) else WIPQty1 end as UpdatedStockQtyWIP   from @tbl1 
 
 
select * from @tbl2

I figure it out by my self thank you guys for your support