批次累加到指定数量sqlserver

 CREATE TABLE [dbo].[TestTable](
    [batch] [nchar](10) NULL,
    [num] [decimal](18, 0) NULL,
    [catalogNo] [nchar](10) NULL
) ON [PRIMARY]
GO

  --insert into TestTable ([batch],[num],[catalogNo]) values ('A1',10,'A')
  --insert into TestTable ([batch],[num],[catalogNo]) values ('A2',10,'A')
  --insert into TestTable ([batch],[num],[catalogNo]) values ('A3',10,'A')
  --insert into TestTable ([batch],[num],[catalogNo]) values ('A4',10,'A')
  --insert into TestTable ([batch],[num],[catalogNo]) values ('A5',10,'A')
  --insert into TestTable ([batch],[num],[catalogNo]) values ('A6',10,'A')
  --insert into TestTable ([batch],[num],[catalogNo]) values ('A7',10,'A')
  --insert into TestTable ([batch],[num],[catalogNo]) values ('A8',10,'A')
  --insert into TestTable ([batch],[num],[catalogNo]) values ('B1',10,'B')
  --insert into TestTable ([batch],[num],[catalogNo]) values ('B2',10,'B')
  --insert into TestTable ([batch],[num],[catalogNo]) values ('B3',10,'B')
  --insert into TestTable ([batch],[num],[catalogNo]) values ('B4',10,'B')
  --insert into TestTable ([batch],[num],[catalogNo]) values ('B5',10,'B')
  --insert into TestTable ([batch],[num],[catalogNo]) values ('B6',10,'B')
  --insert into TestTable ([batch],[num],[catalogNo]) values ('B7',10,'B')
  --insert into TestTable ([batch],[num],[catalogNo]) values ('B8',10,'B')

select 产品号=catalogNo,批次号=batch,批次库存=total_amount,每个批次的用量=iif(xx<0,total_amount,total_amount-xx) from 
  (
  select catalogNo,batch,total_amount,
    sum(total_amount) over(partition by catalogNo order by batch rows between unbounded preceding and current row) as amount,
    xx=sum(total_amount) over(partition by catalogNo order by batch rows between unbounded preceding and current row)-35
    from 
    (select catalogNo,batch,sum(num) as total_amount
    from TestTable 
    group by catalogNo,batch) as a 
    )x where catalogNo='A' and xx<=amount and xx<=total_amount

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值