DECLARE @Table AS TABLE
(
Account VARCHAR(25),
Mnth INT,
Salary MONEY
)
--// Data Insertion
INSERT INTO @Table(Account,Mnth,Salary)
VALUES ('ABC123',201101,100),
('ABC123',201102,200),
('ABC123',201103,150),
('ABC123',201104,300),
('ABC123',201105,50),
('ABC123',201106,40),
('ABC123',201107,500),
('ABC123',201108,600),
('ZZZ555',201105,150),
('ZZZ555',201106,450),
('ZZZ555',201107,200),
('ZZZ555',201108,50),
('ZZZ555',201109,80),
('ZZZ555',201110,20)
--// Result with Running value of each account
SELECT * FROM @Table d1
OUTER APPLY
(
SELECT SUM(d2.Salary) RunningValue
FROM @Table d2
WHERE d1.account = d2.Account
AND d2.mnth <= d1.mnth
) t
Result:
Account | Mnth | Salary | RunningValue |
ABC123 | 201101 | 100 | 100 |
ABC123 | 201102 | 200 | 300 |
ABC123 | 201103 | 150 | 450 |
ABC123 | 201104 | 300 | 750 |
ABC123 | 201105 | 50 | 800 |
ABC123 | 201106 | 40 | 840 |
ABC123 | 201107 | 500 | 1340 |
ABC123 | 201108 | 600 | 1940 |
ZZZ555 | 201105 | 150 | 150 |
ZZZ555 | 201106 | 450 | 600 |
ZZZ555 | 201107 | 200 | 800 |
ZZZ555 | 201108 | 50 | 850 |
ZZZ555 | 201109 | 80 | 930 |
ZZZ555 | 201110 | 20 | 950 |
No comments:
Post a Comment