CODEWITHSUNDEEP
sqldatesqlite
something_new
something_new

Reputation: 91

Usage compared to last year

I have a table that contains two columns - date an amount (usage). I want to compare my usage with last year's

Data

Date        Amount
01-01-2015  23
02-01-2015  24
03-01-2015  19
04-01-2015  11
05-01-2015  5
06-01-2015  23
07-01-2015  21
08-01-2015  6
09-01-2015  26
10-01-2015  9
[...]
01-01-2016  30
02-01-2016  19
03-01-2016  5
04-01-2016  8
05-01-2016  15
06-01-2016  27
07-01-2016  19
08-01-2016  21
09-01-2016  24
10-01-2016  27
[until today's date]

The sql statement should return results up to today's date and the diff should be a running total - the amount on a day in 2016 minus the amount on the same day in 2015, plus the previous difference. Assuming today is Jan 10, the result would be:

Day         Diff
01-01-2016  7   (30-23)
02-01-2016  2   (19-24+7)
03-01-2016  -12 (5-19+2)
04-01-2016  -15 (8-11-12)
05-01-2016  -5  (and so on...)
06-01-2016  -1
07-01-2016  -3
08-01-2016  12
09-01-2016  10
10-01-2016  28

I can't figure out to do this when the data is all in one table...

Thanks

Upvotes: 0

Views: 57

Answers (1)

Atheer Mostafa
Atheer Mostafa

Reputation: 735

Try this

SELECT t1.date Day,SUM(t2.Diff) FROM
amounts t1
INNER JOIN
(SELECT amounts.date Day, amounts.amount-prevamounts.amount Diff
FROM amounts inner join amounts prevamounts 
ON amounts.date=date(prevamounts.date,'+1 years')) t2
ON t2.Day<=t1.date
group by t1.date;

demo here : http://sqlfiddle.com/#!7/9bd4c/41

Upvotes: 2

Related Questions