113.库存明细账案例（包含结存数）

川长思鸟来 2024-02-19 19:02 48阅读 0赞

--结存表
    CREATE TABLE Stocks(Item varchar(10),Period int,Balance int)
    INSERT Stocks SELECT 'aa',200501,100
    UNION  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-1'
    UNION ALL SELECT 'aa',90 ,1,'2005-2-1'
    UNION ALL SELECT 'aa',55 ,0,'2005-2-1'
    UNION ALL SELECT 'aa',-10,1,'2005-2-2'
    UNION ALL SELECT 'aa',-5 ,0,'2005-2-3'
    UNION ALL SELECT 'aa',200,1,'2005-2-2'
    UNION ALL SELECT 'aa',90 ,1,'2005-2-1'
    UNION ALL SELECT 'bb',95 ,1,'2005-2-2'
    UNION ALL SELECT 'bb',65 ,0,'2005-2-3'
    UNION ALL SELECT 'bb',-15,1,'2005-2-5'
    UNION ALL SELECT 'bb',-20,0,'2005-2-5'
    UNION ALL SELECT 'bb',100,1,'2005-2-7'
    UNION ALL SELECT 'cc',100,1,'2005-1-7'
    GO
    
    --查询时间段定义
    DECLARE @dt1 datetime,@dt2 datetime
    SELECT @dt1='2005-2-2',@dt2='2005-2-10'
    
    --查询
    --期初库存年月及计算期初数的开始时间)
    DECLARE @Period int,@dt datetime
    SELECT @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=0
    	FROM tb a
    	WHERE Date>=@dt AND Date<@dt1 
    		AND NOT EXISTS(
    			SELECT * FROM tb WHERE Item=a.Item AND Date>@dt1 AND Date=@dt AND Date0 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 a
    	WHERE Date>=@dt1 AND Date