Wednesday, April 1, 2015

Summing by row number


DECLARE @tmpTbl as table
(
ordNum varchar(10),
ordType varchar(4),
dlqAmt numeric(5,2),
pdAmt numeric(5,2),
rcvdDt datetime
)

INSERT @tmpTbl
VALUES
('5','ORG',0.00,101.32,'12/1/2013'),
('8','ADD',0.00,41.12,'12/18/2013'),
('11','ADD',32.17,17.19,'1/13/2014'),
('21','ADD',0.00,31.01,'1/28/2014'),
('92','ADD',0.00,0.92,'2/13/2014'),
('108','ADD',0.19,92.42,'2/21/2014'),
('317','ADD',0.00,13.11,'3/3/2014'),
('1010','ADD',0.00,4.08,'3/17/2014'),
('1713','ADD',0.00,11.06,'4/1/2014'),
('3010','ADD',22.97,2.19,'4/16/2014')

--select * from @tmpTbl

;with mycte as
(
select ordNum,
ordType,
dlqAmt,
pdAmt,
rcvdDt,
ROW_NUMBER() OVER( ORDER BY rcvdDt) rn
from @tmpTbl)

Select m1.ordNum,m1.ordType,m1.rcvdDt,m2.dlqAmt1, m2.pdAmt1 from mycte m1
CROSS APPLY (
SELECT SUM(dlqAmt) dlqAmt1, SUM(pdAmt) pdAmt1
FROM mycte WHERE rn <= m1.rn ) m2
Where m1.dlqAmt<>0


No comments:

Post a Comment