CODEWITHSUNDEEP
sql-server-2008
Allen_Delon
Allen_Delon

Reputation: 37

How to Subtract two columns in one table

I am trying to subtract the values in C and D columns and place the results in 'Diff' column.

Create table Test_Table   
(Item INT,                  
[A] INT,                    
[B] INT,                    
[C] INT,                    
[D] INT,                    
[Date] Date
)
GO
INSERT INTO Test_Table

Values ('107', '136', '1', '3',  '3', '03/21/16'),('109', '136', '1', '2',  '2', '03/21/16'),
       ('110', '136', '1', '1',  '1', '03/21/16'),('108', '136', '2', '10', '4', '03/21/16'),
       ('108', '136', '3', '10', '3', '03/25/16'),('108', '136', '4', '10', '3', '03/26/16'),
       ('115', '138', '5', '5',  '3', '04/01/16'),('115', '138', '6', '5',  '2', '04/04/16')
GO

select * from Test_Table

enter image description here

For Item 108:
In row 4: Subtratc C4 and D4 (10-4=6).
In row 5: Replace C5=10 with 6 and subtract two columns (6-3=3). 
In row 6: Replace C6=10 with 3 and subtract two columns (3-3=0).

For Item 115:
In row 7: Subtratc C7 and D7 (5-3=2).
In row 8: Replace C8=5 with 2 and subtract two columns (2-2=0).

The result should be look like this...

enter image description here

Any help would be appreciated.

Upvotes: 1

Views: 541

Answers (1)

Quassnoi
Quassnoi

Reputation: 425371

WITH    i AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY item ORDER BY date) rn
        FROM    test_table
        )
SELECT  i.item, i.a, i.b,
        io.c + COALESCE(dd, 0) c,
        io.c + COALESCE(dd, 0) - i.d diff,
        i.d, i.date
FROM    i
CROSS APPLY
        (
        SELECT  *
        FROM    i io
        WHERE   item = i.item
                AND rn = 1
        ) io
CROSS APPLY
        (
        SELECT  SUM(-d) dd
        FROM    i ii
        WHERE   item = io.item
                AND rn < i.rn
        ) ii

In SQL Server 2014 it's much more easy and efficient:

SELECT  item, a, b,
        FIRST_VALUE(c) OVER (PARTITION BY item ORDER BY date) +
        SUM(-d) OVER (PARTITION BY item ORDER BY date),
        d, date
FROM    test_table

Upvotes: 1

Related Questions