Wednesday, November 30, 2011

Running value in T-sql

--// Table Variable Declaration
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