已知数据库中如下数据,月初，或每天数据不一定有，但是月末数据肯定有，
所以就想根据月末来来算出每天剩余， 但是不知道怎么写
求各路大神帮忙
Item Qty TransDate Type
A001 100.00 2014/06/01 月初
A001 60.00 2014/06/01 每天
A001 -20.00 2015/06/20 每天
A001 140.00 2015/06/30 月末
B001 70.00 2014/06/25 每天
B001 70.00 2015/06/30 月末
C001 170.00 2015/06/1 月初
C001 170.00 2015/06/30 月末
想做成每天的剩余量，上面的例子的结果就是
A001 160 2014/06/01 每天剩余
~
A001 160 2014/06/19 每天剩余
A001 140 2014/06/20 每天剩余
~
A001 140 2014/06/30 每天剩余
B001 0 2014/06/01 每天剩余
~
B001 0 2014/06/24 每天剩余
B001 70 2014/06/25 每天剩余
~
B001 70 2014/06/30 每天剩余
C001 170 2014/06/01 每天剩余
~
C001 170 2014/06/30 每天剩余
3 个解决方案
#1
想用存储过程实现
#2
原表中的数据要拆开来用，“月末”用于确定统计哪些月以及生成每一天的条目，“月初”和“每天”用于累加计算每一天的余量。代码供参考：
CREATE TABLE Inventory
(
Item varchar(10)
, Qty int
, TransDate datetime
, Type nvarchar(10)
)
INSERT INTO Inventory VALUES ('A001', 100.00, '2014/06/01', N'月初' )
INSERT INTO Inventory VALUES ('A001', 60.00, '2014/06/01', N'每天' )
INSERT INTO Inventory VALUES ('A001', -20.00, '2015/06/20', N'每天' )
INSERT INTO Inventory VALUES ('A001', 140.00, '2015/06/30', N'月末' )
INSERT INTO Inventory VALUES ('B001', 70.00, '2014/06/25', N'每天' )
INSERT INTO Inventory VALUES ('B001', 70.00, '2015/06/30', N'月末' )
INSERT INTO Inventory VALUES ('C001', 170.00, '2015/06/1', N'月初' )
INSERT INTO Inventory VALUES ('C001', 170.00, '2015/06/30', N'月末' )
SELECT
Item
, (
SELECT
ISNULL(SUM(Qty), 0)
FROM Inventory inv
WHERE inv.Type <> N'月末'
AND inv.Item = ent.Item
AND inv.TransDate <= ent.TransDate
) AS Remain
, TransDate
, N'每天剩余' AS Type
FROM (
SELECT
e.Item
, DATEADD(DD, -1 * s.number, e.TransDate) AS TransDate
FROM (
SELECT Item, Qty, TransDate
FROM Inventory
WHERE Type = N'月末'
) e
JOIN master.dbo.spt_values s ON s.number >= 0 AND s.number <= DATEPART(DD, e.TransDate) - 1
WHERE s.type = 'P'
) ent
ORDER BY ent.Item, ent.TransDate
#3
太感谢了好用！
#1
想用存储过程实现
#2
原表中的数据要拆开来用，“月末”用于确定统计哪些月以及生成每一天的条目，“月初”和“每天”用于累加计算每一天的余量。代码供参考：
CREATE TABLE Inventory
(
Item varchar(10)
, Qty int
, TransDate datetime
, Type nvarchar(10)
)
INSERT INTO Inventory VALUES ('A001', 100.00, '2014/06/01', N'月初' )
INSERT INTO Inventory VALUES ('A001', 60.00, '2014/06/01', N'每天' )
INSERT INTO Inventory VALUES ('A001', -20.00, '2015/06/20', N'每天' )
INSERT INTO Inventory VALUES ('A001', 140.00, '2015/06/30', N'月末' )
INSERT INTO Inventory VALUES ('B001', 70.00, '2014/06/25', N'每天' )
INSERT INTO Inventory VALUES ('B001', 70.00, '2015/06/30', N'月末' )
INSERT INTO Inventory VALUES ('C001', 170.00, '2015/06/1', N'月初' )
INSERT INTO Inventory VALUES ('C001', 170.00, '2015/06/30', N'月末' )
SELECT
Item
, (
SELECT
ISNULL(SUM(Qty), 0)
FROM Inventory inv
WHERE inv.Type <> N'月末'
AND inv.Item = ent.Item
AND inv.TransDate <= ent.TransDate
) AS Remain
, TransDate
, N'每天剩余' AS Type
FROM (
SELECT
e.Item
, DATEADD(DD, -1 * s.number, e.TransDate) AS TransDate
FROM (
SELECT Item, Qty, TransDate
FROM Inventory
WHERE Type = N'月末'
) e
JOIN master.dbo.spt_values s ON s.number >= 0 AND s.number <= DATEPART(DD, e.TransDate) - 1
WHERE s.type = 'P'
) ent
ORDER BY ent.Item, ent.TransDate
#3
太感谢了好用！