在sql中查找運行總計-如果第一列沒有值，則從第二列計算

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

 

我想先從 StockQty 分配 BalanceQty，然后在 StockQty 完成后從 WIPQty 分配

這作業正常（以下只是為了理解）。

對于第一行 BalanceQty = 10 ,StockQty=10 所以如果我們從 Balance 中分配所有 10 個數量，即 StockAllocateQty = 10 和 UpdatedStockQty = 0。 (Balance - StockQty) = UpdatedStockQty

對于第二行 BalanceQty = 5，StockQty = 0（所有庫存數量都在第一行使用）所以如果我們從余額中分配 5 個數量，那么我們將得到 StockAllocateQty = 5 和 UpdatedStockQty = -5。(0 - 5) = -5

對于第三行 BalanceQty = 2，StockQty = 0（所有庫存數量都在第一行使用）所以如果我們從余額中分配 2 個數量，那么我們將得到 StockAllocateQty = -7 和 UpdatedStockQty = -5。(-5 - -2) = -7

問題出在這 2 列 AllocateQtyWIP UpdatedStockQtyWIP

現在我有額外的作業正在進行數量分配，如果第一行使用了所有庫存數量，所有庫存數量都被使用，所以我分配到第二行

對于第一行，我們使用的是庫存數量，我們甚至沒有使用 WIP 數量比它應該是 AllocateQtyWIP = 0 UpdatedStockQtyWIP = 40 但我得到 AllocateQtyWIP = 10，UpdatedStockQtyWIP = 0

這兩列的預期輸出：

但相反，我得到了這個：

預計這

uj5u.com熱心網友回復：

 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

我自己想出來的 謝謝大家的支持

標籤：sql sql-server-2008 sql-server-2005 sql-server-2012

上一篇：單元格值之間的SQL差異（LAG函式不起作用）

下一篇：找不到模塊：無法決議“@mui/icons-material/Polyline”