我正在使用 Microsoft SQL Server 2012。
我有一个带有项目交易的表：
artcode transdate Qty transactionvalue
------------------------------------------------
M100 2010-11-24 6.00 179.40
M100 2010-11-24 -6.00 -179.4
M100 2010-11-25 100.00 2900.00
M100 2010-11-26 -1.00 -29
M100 2010-11-26 -5.00 -145
M100 2010-11-26 -1.00 -29
M100 2010-11-29 -5.00 -145
M100 2010-11-29 -3.00 -87
M100 2010-11-29 -1.00 -29
通过此查询，我设法按运行顺序获取累积值：
SELECT
TransDate, ArtCode, CumulativeQuantity, CumulativeValue
FROM
(SELECT
ArtCode,
SUM(CAST(REPLACE(REPLACE(NULLIF(Qty, ''), ',', '.'), ' ', '') AS float)) OVER (PARTITION BY artcode ORDER BY transdate) AS CumulativeQuantity,
SUM(CAST(REPLACE(REPLACE(NULLIF(TotCostPrice, ''), ',', '.'), ' ', '') AS FLOAT)) OVER (PARTITION BY artcode ORDER BY transdate) AS CumulativeValue,
TransDate
FROM
stage_itemhistory
WHERE
artcode = 'm100' AND stockaffect = 1) S
GROUP BY
TransDate, ArtCode, CumulativeQuantity, CumulativeValue
这将返回：
TransDate ArtCode CumulativeQuantity CumulativeValue
--------------------------------------------------------
2010-11-24 M100 0 0
2010-11-25 M100 100 2900
2010-11-26 M100 93 2697
2010-11-29 M100 84 2436
这与我所追求的非常接近，唯一缺少的是之间的日期，这将具有以前的日期累积值。所以它看起来像这样：
TransDate ArtCode CumulativeQuantity CumulativeValue
--------------------------------------------------------
2010-11-24 M100 0 0
2010-11-25 M100 100 2900
2010-11-26 M100 93 2697
2010-11-27 M100 93 2697
2010-11-28 M100 93 2697
2010-11-29 M100 84 2436
任何和所有的帮助将不胜感激！先感谢您。
这比我想象的要难。有人可能有更简单的解决方案。需要填写艺术代码，还要考虑不同艺术代码的不同范围。
declare @T table (artcode varchar(10), transdate date, Qty smallmoney, transactionvalue smallmoney);
insert into @T values
('M100', '2010-11-24', 6.00, 179.40)
, ('M100', '2010-11-24', -6.00, -179.4)
, ('M100', '2010-11-25', 100.00, 2900.00)
, ('M100', '2010-11-26', -1.00, -29)
, ('M100', '2010-11-26', -5.00, -145)
, ('M100', '2010-11-26', -1.00, -29)
, ('M100', '2010-11-29', -5.00, -145)
, ('M100', '2010-11-29', -3.00, -87)
, ('M100', '2010-11-29', -1.00, -29)
, ('M101', '2010-11-23', 6.00, 179.40)
, ('M101', '2010-11-25', 100.00, 2900.00)
, ('M101', '2010-11-26', -1.00, -29)
, ('M101', '2010-11-26', -5.00, -145)
, ('M101', '2010-11-26', -1.00, -29)
, ('M101', '2010-11-30', -5.00, -145)
, ('M101', '2010-11-30', -3.00, -87)
, ('M101', '2010-11-30', -1.00, -29);
with limits as
( select t.artcode, min(t.transdate) as startDate, max(t.transdate) as endtDate
from @T t
group by t.artcode
)
, dts as
( select l.artcode, l.startDate as dt, l.startDate, l.endtDate
from limits l
union all
select l.artcode, dateadd(day, 1, l.dt), l.startDate, l.endtDate
from dts l
where dateadd(day, 1, l.dt) <= l.endtDate
)
select distinct dts.artcode, dts.dt
, sum(isnull(t.Qty, 0)) over (partition by dts.artcode order by dts.dt) as Qty
, sum(isnull(t.transactionvalue, 0)) over (partition by dts.artcode order by dts.dt) as transactionvalue
from dts
left join @T t
on t.transdate = dts.dt
and t.artcode = dts.artcode
order by dts.artcode, dts.dt;
artcode dt Qty transactionvalue
---------- ---------- --------------------- ---------------------
M100 2010-11-24 0.00 0.00
M100 2010-11-25 100.00 2900.00
M100 2010-11-26 93.00 2697.00
M100 2010-11-27 93.00 2697.00
M100 2010-11-28 93.00 2697.00
M100 2010-11-29 84.00 2436.00
M101 2010-11-23 6.00 179.40
M101 2010-11-24 6.00 179.40
M101 2010-11-25 106.00 3079.40
M101 2010-11-26 99.00 2876.40
M101 2010-11-27 99.00 2876.40
M101 2010-11-28 99.00 2876.40
M101 2010-11-29 99.00 2876.40
M101 2010-11-30 90.00 2615.40
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句