calculating a column´s value from two registries

[keyser6]

Hi, I have a table that looks like this:


Year|Month|Concept|amount
2003| 1 | 3 | 1596
2003| 2 | 4 | 2258
2004| 1 | 3 | 4563

This means that someone made a payment (different types of payment defined by the "concept" columns) in x month on y year. This table is the result of a query joining two tables (I joined them to print others columns that are not relevant to this question). Anyway, I now need a new column that shows the difference between a payment for each month and the same month in the previous year to be shown in this same table. Could anyone here help?

Thanks

 

[PHV]

Something like this ?
SELECT A.Year, A.Month, A.Concept, A.amount, (A.amount-B.amount) AS Difference
FROM yourTableOrView AS A LEFT JOIN yourTableOrView AS B
ON (A.Year=B.Year+1) AND (A.Month=B.Month)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244