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:
AllocateQtyWIP | UpdatedStockQtyWIP |
---|---|
0 | 40 |
5 | 35 |
2 | 33 |
but instead I'm getting this:
ItemId | SOQty | DIQty | IssueQty | BalanceQty | CreateDate | StockQty | WIPQty | StockAllocateQty | UpdatedStockQty | AllocateQtyWIP | UpdatedStockQtyWIP |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | 10 | 10 | 0 | 10 | 2021-12-16 19:28:32.200 | 10 | 0 | 10 | 0 | 10 | 0 |
1 | 15 | 10 | 10 | 5 | 2021-12-18 19:28:34.200 | 0 | 40 | 5 | -5 | 5 | 35 |
1 | 8 | 5 | 2 | 2 | 2021-12-19 19:28:35.200 | 0 | 0 | 2 | -7 | 2 | 33 |
expected this
ItemId | SOQty | DIQty | IssueQty | BalanceQty | CreateDate | StockQty | WIPQty | StockAllocateQty | UpdatedStockQty | AllocateQtyWIP | UpdatedStockQtyWIP |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | 10 | 10 | 0 | 10 | 2021-12-16 19:28:32.200 | 10 | 0 | 10 | 0 | 0 | 40 |
1 | 15 | 10 | 10 | 5 | 2021-12-18 19:28:34.200 | 0 | 40 | 5 | -5 | 5 | 35 |
1 | 8 | 5 | 2 | 2 | 2021-12-19 19:28:35.200 | 0 | 0 | 2 | -7 | 2 | 33 |
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