sql库存明细帐处理示例包含结存数.doc

sql 库存明细帐处理示例(包含结存数) -结存表CREATE TABLE Stocks(Item varchar(10),Period int,Balance int)INSERT Stocks SELECT aa,200501,100UNION ALL SELECT cc,200501,100-明细账数据CREATE TABLE tb(ID int IDENTITY PRIMARY KEY,Item varchar(10), -产品编号Quantity int, -交易数量Flag bit, -交易标志,1代表入库,0代表出库,这样可以有效区分退货(负数)Date datetime) -交易日期INSERT tb SELECT aa,100,1,2005-1-1UNION ALL SELECT aa,90 ,1,2005-2-1UNION ALL SELECT aa,55 ,0,2005-2-1UNION ALL SELECT aa,-10,1,2005-2-2UNION ALL SELECT aa,-5 ,0,2005-2-3UNION ALL SELECT aa,200,1,2005-2-2UNION ALL SELECT aa,90 ,1,2005-2-1UNION ALL SELECT bb,95 ,1,2005-2-2UNION ALL SELECT bb,65 ,0,2005-2-3UNION ALL SELECT bb,-15,1,2005-2-5UNION ALL SELECT bb,-20,0,2005-2-5UNION ALL SELECT bb,100,1,2005-2-7UNION ALL SELECT cc,100,1,2005-1-7GO-查询时间段定义DECLARE dt1 datetime,dt2 datetimeSELECT dt1=2005-2-2,dt2=2005-2-10-查询-期初库存年月及计算期初数的开始时间)DECLARE Period int,dt datetimeSELECT Period=CONVERT(CHAR(6),DATEADD(Month,-1,dt1),112),dt=DATEADD(Day,1-Day(dt1),dt1)-查询期初库存SELECT Item=ISNULL(a.Item,b.Item),Date=ISNULL(b.Date,CONVERT(char(10),dt1,120),Opening=ISNULL(a.Balance,0)+ISNULL(b.Opening,0),IN=ISNULL(b.IN,0),IN_Retrun=ISNULL(b.IN_Retrun,0),OUT=ISNULL(b.OUT,0),OUT_Return=ISNULL(b.OUT_Return,0),Balance=ISNULL(a.Balance,0)+ISNULL(b.Opening,0)+ISNULL(b.Amount,0)FROM(-期初数SELECT Item,Balance FROM Stocks WHERE Period=Period)a FULL JOIN(-统计时间段内无发生额的数据(如果这个不是查询需要的,去掉这段查询)SELECT Item,Date=CONVERT(char(10),dt1,120),Opening=SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END),IN=0,IN_Retrun=0,OUT=0,OUT_Return=0,Amount=0FROM tb aWHERE Date>=dt AND Date<dt1 AND NOT EXISTS(SELECT * FROM tb WHERE Item=a.Item AND Date>dt1 AND Date<DATEADD(Day,1,dt2)GROUP BY ItemUNION ALL-指定时间段内有交易发生的数据SELECT Item,Date=CONVERT(char(10),Date,120),Opening=(SELECT SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END)FROM tb WHERE Item=a.Item AND Date>=dt AND Date<MIN(a.Date),IN=SUM(CASE WHEN Flag=1 AND Quantity>0 THEN Quantity END),IN_Retrun=SUM(CASE WHEN Flag=1 AND Quantity<0 THEN -Quantity END),OUT=SUM(CASE WHEN Flag=0 AND Quantity>0 THEN Quantity END),OUT_Return=SUM(CASE WHEN Flag=0 AND Quantity<0 THEN -Quantity END),Amount=SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END)FROM tb aWHERE Date>=dt1 AND Date<DATEADD(Day,1,dt2)GROUP BY CONVERT(char(10),Date,120),Item)b ON a.Item=b.ItemORDER BY Item,Date/*-结果Item Date Opening IN IN_Retrun OUT OUT_Return Balance - - - - - - - - - aa 2005-02-02 225 200 10 0 0 415aa 2005-02-03 415 0 0 0 5 420bb 2005-02-02 0