存货收发存应用实例

 

-->测试数据：

IF OBJECT_ID('[dbo].[products]') IS NOT NULL

    DROP TABLE PRODUCTS

GO

CREATE TABLE [dbo].[products](

[id] INT IDENTITY(1,1) NOT NULL,

[p_id] INT NOT NULL, --产品编号

[p_tm] DATETIME NULL, --时间

[p_flag] INT NULL, --1表示期初值，表示增加，表示减少

[p_num] INT NULL, --数目

[p_price] MONEY NULL, --单价

[p_money] MONEY NULL, --金额

)

 

GO

 

INSERT [products]

SELECT 1,'2010-04-01',1,50,12.3,615 UNION ALL

SELECT 1,'2010-04-02',2,10,12.3,123 UNION ALL

SELECT 1,'2010-04-05',3,10,15.3,153 UNION ALL

SELECT 1,'2010-04-06',2,5,12.3,61.5 UNION ALL

SELECT 1,'2010-04-07',2,20,12.3,246 UNION ALL

SELECT 1,'2010-04-09',2,22,12.3,270.6 UNION ALL

SELECT 1,'2010-04-10',3,7,15.7,109.7 UNION ALL

SELECT 1,'2010-04-11',3,10,15.5,155 

GO

 

-->SQL查询如下：

;WITH T AS

(

    SELECT id, p_id, p_tm

       ,[期初数量] = CASE p_flag WHEN 1 THEN p_num ELSE 0 END

       ,[期初单价] = CASE p_flag WHEN 1 THEN p_price ELSE 0 END

       ,[期初金额] = CASE p_flag WHEN 1 THEN p_money ELSE 0 END

       ,[增加数量] = CASE p_flag WHEN 2 THEN p_num ELSE 0 END

       ,[增加单价] = CASE p_flag WHEN 2 THEN p_price ELSE 0 END

       ,[增加金额] = CASE p_flag WHEN 2 THEN p_money ELSE 0 END

       ,[减少数量] = CASE p_flag WHEN 3 THEN p_num ELSE 0 END

       ,[减少单价] = CASE p_flag WHEN 3 THEN p_price ELSE 0 END

       ,[减少金额] = CASE p_flag WHEN 3 THEN p_money ELSE 0 END

    FROM products

)

SELECT a.id, a.p_id, a.p_tm

    ,[期初数量] = SUM(b.期初数量 + b.增加数量 - b.减少数量) - a.增加数量 + a.减少数量

    ,[期初单价] = (SUM(b.期初金额 + b.增加金额 - b.减少金额) - a.增加金额 + a.减少金额)

                  /(SUM(b.期初数量 + b.增加数量 - b.减少数量) - a.增加数量 + a.减少数量)

    ,[期初金额] = SUM(b.期初金额 + b.增加金额 - b.减少金额) - a.增加金额 + a.减少金额

    ,[增加数量] = a.[增加数量]

    ,[增加单价] = a.[增加单价]

    ,[增加金额] = a.[增加金额]

    ,[减少数量] = a.[减少数量]

    ,[减少单价] = a.[减少单价]

    ,[减少金额] = a.[减少金额]

    ,[期末数量] = SUM(b.期初数量 + b.增加数量 - b.减少数量)

    ,[期末单价] = SUM(b.期初金额 + b.增加金额 - b.减少金额)

                  /SUM(b.期初数量 + b.增加数量 - b.减少数量)

    ,[期末金额] = SUM(b.期初金额 + b.增加金额 - b.减少金额)

FROM T a

    JOIN T b

       ON a.p_id = b.p_id AND a.p_tm >= b.p_tm

GROUP BY a.id, a.p_id, a.p_tm, a.增加数量,a.增加单价,a.增加金额,

       a.减少数量, a.减少单价, a.减少金额

ORDER BY a.p_tm

 

/*
iid p_id p_tm 期初数量 期初单价 期初金额 增加数量 增加单价 增加金额 减少数量 减少单价 减少金额 期末数量 期末单价 期末金额
1 1 2010-04-01 00:00:00.000 50 12.30 615.00 0 0.00 0.00 0 0.00 0.00 50 12.30 615.00
2 1 2010-04-02 00:00:00.000 50 12.30 615.00 10 12.30 123.00 0 0.00 0.00 60 12.30 738.00
3 1 2010-04-05 00:00:00.000 60 12.30 738.00 0 0.00 0.00 10 15.30 153.00 50 11.70 585.00
4 1 2010-04-06 00:00:00.000 50 11.70 585.00 5 12.30 61.50 0 0.00 0.00 55 11.7545 646.50
5 1 2010-04-07 00:00:00.000 55 11.7545 646.50 20 12.30 246.00 0 0.00 0.00 75 11.90 892.50
6 1 2010-04-09 00:00:00.000 75 11.90 892.50 22 12.30 270.60 0 0.00 0.00 97 11.9907 1163.10
7 1 2010-04-10 00:00:00.000 97 11.9907 1163.10 0 0.00 0.00 7 15.70 109.70 90 11.7044 1053.40
8 1 2010-04-11 00:00:00.000 90 11.7044 1053.40 0 0.00 0.00 10 15.50 155.00 80 11.23 898.40(8 行受影响)
*/