SQL query to compute the sum of differences between amounts

Question

I have a table that looks like this:

CustID Year Month Amount
------ ---- ----- ------
11     2012 12    310685
11     2013 1     312119
11     2013 2     313929
11     2013 3     315511
19     2012 12    189736
19     2013 1     195161
19     2013 2     199713
10     2013 1     448438
10     2013 2     453850
10     2013 3     460198

I need:

for each CustID, compute the difference of Amount of the current month and Amount of the previous month (if there is no 'previous month', just ignore it - return NULL or 0 or whatever).
sum the differences for each month.

Expected results:

Year Month Total
---- ----- -----
2012 12    NULL
2013 1     6859     // (312119-310685) + (195161-189736) [no diff for CustID = 10 because it has no amount for 2012.12]
2013 2     11774
2013 3     7930

I tried to do it using CTE and ROW_NUMBER() with self-joining etc., but the query became entangled and I lost my way... Any help will be appreciated.

Use self join http://stackoverflow.com/questions/3362038/what-is-self-join-and-when-would-you-use-it — Suganthan Madhavan Pillai, Nov 26 '13 at 08:01

score 0 · Accepted Answer · answered Nov 26 '13 at 08:19

0

I think this does what you are after:

WITH YearMonth
AS
(
    SELECT  CustID, 
            [Year], 
            [Month], 
            ([Year] * 12 + [Month]) AS YearMonth, Amount
    FROM YourTable
)
SELECT  YM.[Year], YM.[Month], (YM.Amount - PREV.Amount) AS Total
FROM    YearMonth YM
LEFT
JOIN    YearMonth PREV
        ON PREV.CustID = YM.CustID
        AND PREV.YearMonth = YM.YearMonth - 1

answered Nov 26 '13 at 08:19

Fergus Bown

1,666
9
16

Yes, it pointed me in the right direction. I only added the `SUM()` function to it, et voila! – kodkod Nov 26 '13 at 10:17

SQL query to compute the sum of differences between amounts

1 Answers1