--清除某个日期后所有重复的出入库流水
if not object_id('tempdb..#min') is null
  drop table #min
select voucher_no, item_no, branch_no, db_no, real_qty, sheet_amt, oper_date = convert(varchar(16), oper_date, 120), min_no = min(sheet_no), max_no=max(sheet_no)
  into #min
  from wh_inout_flow a
 where trans_no <> 'S'
   and oper_date > '2023-08-01'
 group by voucher_no, item_no, branch_no, db_no, real_qty, sheet_amt, convert(varchar(16), oper_date, 120)
having count(1) > 1
/*
select f.* from wh_inout_flow f
 inner join #min b on b.voucher_no=f.voucher_no and b.item_no=f.item_no and b.branch_no=f.branch_no and b.db_no=f.db_no and b.real_qty=f.real_qty and b.sheet_amt=f.sheet_amt and b.oper_date = convert(varchar(16), f.oper_date, 120)
 where f.sheet_no > b.min_no + 500
*/
update x set stock_qty = x.stock_qty - b.qty
  from wh_stock x
 inner join (
 select f.item_no, f.branch_no, qty = sum(case when f.db_no = '+' then f.real_qty else -f.real_qty end)
  from wh_inout_flow f
 inner join #min b on b.voucher_no=f.voucher_no and b.item_no=f.item_no and b.branch_no=f.branch_no and b.db_no=f.db_no and b.real_qty=f.real_qty and b.sheet_amt=f.sheet_amt and b.oper_date = convert(varchar(16), f.oper_date, 120)
 where f.sheet_no > b.min_no + 500
 group by f.item_no, f.branch_no) b on b.item_no = x.item_no and b.branch_no = x.branch_no
delete x 
  from wh_inout_flow x
 inner join #min b on b.voucher_no=x.voucher_no and b.item_no=x.item_no and b.branch_no=x.branch_no and b.db_no=x.db_no and b.real_qty=x.real_qty and b.sheet_amt=x.sheet_amt and b.oper_date = convert(varchar(16), x.oper_date, 120)
 where x.sheet_no > b.min_no + 500
drop table #min