Calculate delta between chronological records (LibreOffice Base)

Question

I'm fairly new to SQL and this problem I am facing I cannot solve with my current knowledge(it's pretty impossible to become SQL expert in just few days).

Consider that I have a following simple Table:

ID  Date        Counter
1   03/25/14    291.5
2   03/25/14    310
3   03/25/14    270.9
4   03/25/14    320
1   04/25/14    293
2   04/25/14    311
3   04/25/14    278.3
4   04/25/14    322.1

Basically once a month a reading of some counter is added for each user. I want to be able to create a query that would calculate delta of Counter value compared to previous month value. The result should hopefully look something like this:

ID  Date        Counter  PrevMonthDelta
1   04/25/14    293      1.5
2   04/25/14    311      1
3   04/25/14    278.3    7.4
4   04/25/14    322.1    2.1

I am currently experimenting in LibreOffice Base with default HSQLDB format database, but I assume this should work similar across other SQL DBs.

Thanks in advance!

Answer 1

In general you can join the table to itself in a way that the resulting joined table has the values for two months. You can then perform the calculations on these values. Something like

SELECT ID, T2.DATE, T2.COUNTER - T1.COUNTER FROM T T1, T T2 WHERE T1.ID = T2.ID AND YEAR (T1.DATE) = YEAR(T2.DATE) AND MONTH(T1.DATE) + 1 = MONTH (T1.DATE)

If the condition needs to cover the end of year, use something like the following:

AND YEAR (T1.DATE) + (MONTH(T1.DATE) / 12) = YEAR(T2.DATE) AND (MONTH(T1.DATE) MOD 12) + 1 = MONTH (T1.DATE)

If your actual column names are case-sensitive, use double quoted names such as "Counter".