Forum Questions: Numerical statistics issues (non-cursors)

question:

Link: http://bbs.csdn.net/topics/390985021

Solve:

CREATE Table Rental Table (
 Room code	NVARCHAR,
 date		,
 total		decimal (18,2),
 refund amount	Decimal ( 18,2),
 balance		DECIMAL (18,2)
)

INSERT into rental table
SELECT ' xsdgc-01-01-0003 ', ' 2015-02-05 ', 32780, Null,null
UNION ALL
Select ' xsdgc-01-01-0003 ', ' 2015-02-06 ', 0,100,-100
UNION ALL
Select ' Xsdgc-01-01-0003 ', ' 2015-02-06 ', 0,200,-200


SELECT * from lease form


; With TAB as (
select Row_number () (PARTITION by Room Code order by (select 0)) as ID
, Room code, date, total, refund amount, balance from lease form 
 )
SELECT DISTINCT a.id,a. Room code, a. Date
, MAX (A. total) over ()-SUM (ISNULL (B. Refund amount, 0)) over (PARTITION by a.id) + ISNULL (A. Refund amount, 0) as Total
, ISNULL (A. Refund amount, 0) as refund amount
, MAX (A. Total) over ()-SUM (ISNULL (B. Refund amount, 0)) over (PARTITION by A . ID) as balance from
tab A left JOIN tab B on a.ID > B.id-1