SQL帮助计算开立和结束余额列
问题描述:
我正在使用SQL 2008 R2。我有一个小桌子有以下的列:SQL帮助计算开立和结束余额列
日期 LOCATION1 LOCATION2 LOCATION3
,展望计算这些2列
期初余额=从0开始,这等于从分组结束后的余额天 期末余额=期初余额+ LOCATION1 + LOCATION2 + LOCATION3
所以我基本上看吐了这一点到报告:
DATE | OPENING_BALANCE | LOC1 | LOC2 | LOC3 | CLOSING_BALANCE
----------------------------------------------------------------------------
1/1 $0.00 $1,000 $1,000 $1,000 $3,000
1/2 $3,000 $2,000 $2,000 $1,000 $8,000
1/3 $8,000 ($1,000) ($4,000) $500 $3,500
1/4 $3,500 $10,000 ($5,000) $20,000 $28,500
1/5 $28,500
有没有简单的方法来计算这2列?帮助赞赏!在此先感谢
**道歉马虎表例如
答
好了,根据您所提供(只输出和无表模式)的信息,该查询可能会为你工作:
WITH cte AS
(
SELECT
[Date],
Loc1 = Location1,
Loc2 = Location2,
Loc3 = Location3,
DayTotal = Location1 + Location2 + Location3,
ROWNUM = ROW_NUMBER() OVER (ORDER BY [Date])
FROM
tbl
)
SELECT
DATE = c1.[Date],
OPENING_BALANCE = ISNULL(c2.OpeningDayTotals, 0),
LOC1 = c1.Loc1,
Loc2 = c1.Loc2,
Loc3 = c1.Loc3,
CLOSING_BALANCE = ISNULL(c2.OpeningDayTotals, 0) + c1.DayTotal
FROM
cte c1
OUTER APPLY
(
SELECT
OpeningDayTotals = SUM(c2.DayTotal)
FROM
cte c2
WHERE
c1.ROWNUM > c2.ROWNUM
) AS c2
+0
这看起来像它的工作完美!谢谢你 – Jay
什么数据类型是日期,地点1,地点2。 LOCATION3? –
有没有办法升级到sql server 2012+? –
日期只是一个日期时间,例如,它的确如同1/1/2016。这些地点只是漂浮物。不幸的是,我无法升级到2012+ :(谢谢 – Jay