SQL存储过程 一个简单进销存系统按日的库存

一个简单进销存系统按日的库存

一下是数据库表结构

以下是结果

ALTER PROC dbo.proc_StokcWithDate 
@StartDate DATE,
@EndDate DATE,
@PartNumQuery NVARCHAR(200)
AS
BEGINSELECTtt.Date,CAST(0 AS DECIMAL(18, 5)) AccInQty,CAST(0 AS DECIMAL(18, 5)) AccOutQty,CAST(0 AS DECIMAL(18, 5)) Balance INTO #StockWithDateFROM dbo.TQPartTran ttWHERE 1 = 2;DECLARE @EveryDate DATE = @StartDate;DECLARE @i2 INT = 0;WHILE @EveryDate < @EndDateBEGINSELECT@EveryDate = DATEADD(DAY, @i2, @StartDate)INSERT INTO #StockWithDate ([Date])VALUES (@EveryDate);SET @i2 = @i2 + 1;ENDSELECT DISTINCTtt.PartNum INTO #PartNumFROM dbo.TQPartTran tt;IF LEN(@PartNumQuery) > 0BEGINTRUNCATE TABLE #PartNum;INSERT INTO #PartNum (PartNum)VALUES (@PartNumQuery);ENDSELECTROW_NUMBER() OVER (ORDER BY swd.Date, pn.PartNum) ID,pn.PartNum,swd.* INTO #BaseInfoFROM #StockWithDate swd,#PartNum pn;CREATE INDEX BaseInfoIndex ON #BaseInfo (ID);DECLARE @PartNum NVARCHAR(200),@Date DATE;DECLARE @i INT = 1,@n INT = 0;SELECT@n = COUNT(ID)FROM #BaseInfo bi;WHILE @i <= @nBEGINSELECT@PartNum = bi.PartNum,@Date = bi.DateFROM #BaseInfo biWHERE bi.ID = @i;UPDATE #BaseInfoSET AccInQty = (SELECTSUM(tt.Qty)FROM TQPartTran ttWHERE tt.PartNum = @PartNumAND tt.Date <= @DateAND tt.TranType = 'PUR-STK'),AccOutQty = (SELECTSUM(tt.Qty)FROM TQPartTran ttWHERE tt.PartNum = @PartNumAND tt.Date <= @DateAND tt.TranType = 'STK-CUS')WHERE ID = @i;SET @i = @i + 1;ENDUPDATE #BaseInfoSET AccInQty = 0WHERE AccInQty IS NULL;UPDATE #BaseInfoSET AccOutQty = 0WHERE AccOutQty IS NULL;UPDATE #BaseInfoSET Balance = AccInQty - AccOutQty;IF LEN(@PartNumQuery) > 0BEGINSELECTbi.ID 序号,bi.PartNum 料号,bi.Date 日期,CAST(bi.AccInQty AS DECIMAL(18, 2)) 累计入库,CAST(bi.AccOutQty AS DEC(18, 2)) 累计出库,CAST(bi.Balance AS DEC(18, 2)) 结余FROM #BaseInfo biWHERE bi.PartNum = @PartNumQueryORDER BY bi.Date, bi.PartNum;ENDELSEBEGINSELECTbi.ID 序号,bi.PartNum 料号,bi.Date 日期,CAST(bi.AccInQty AS DECIMAL(18, 2)) 累计入库,CAST(bi.AccOutQty AS DEC(18, 2)) 累计出库,CAST(bi.Balance AS DEC(18, 2)) 结余FROM #BaseInfo biENDDROP TABLE #PartNum;DROP TABLE #StockWithDate;DROP TABLE #BaseInfo;
END--exec proc_StokcWithDate '2020-05-8','2020-05-13',N''  
--exec proc_StokcWithDate '2020-05-8','2020-05-13',''
--exec proc_StokcWithDate '2020-05-8','2020-05-13',N''  
--exec proc_StokcWithDate '2020-05-8','2020-05-13',''
GO