2

I have below table structure and data:

-----------------------------
|  Date      | Accu. Output |
-----------------------------
| 2017-01-01 |          150 |
| 2017-01-02 |          165 |
| 2017-01-03 |          182 |
| 2017-01-04 |          190 |
| 2017-01-05 |          210 |
-----------------------------

I need to generate below output from above dataset. I can do a client side processing. But I am trying to write a query to get this.

----------------------------------------------
|  Date      |   Accu. Output | Daily Output |
----------------------------------------------
| 2017-01-05 |            210 |           20 |
| 2017-01-04 |            190 |            8 |
| 2017-01-03 |            182 |           17 |
| 2017-01-02 |            165 |           15 |
| 2017-01-01 |            150 |            0 |
----------------------------------------------

Daily output is the difference between current Accu. Output and previous day's Accu. Output.

Thanks

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Lakmal Premaratne
  • 1,159
  • 7
  • 18
  • 34

1 Answers1

4

Here is a method uses a left join:

select t.*,
       coalesce(t.accu_output - tprev.accu_output, 0) as diff
from t left join
     t tprev
     on tprev.date = t.date - interval 1 day;

This assumes -- as in your sample data -- that the days are increasing with no gaps and exactly one value per day. These conditions are implied by your question (previous day's output).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Having read your answer, I thought that there can be instances where there can be gaps in dates (but it is very rare). Since there is a auto incrementing column in the table, I could use that column for comparison. .i.e `select t.id, t.logged_at, t.reading as accu_reading, (t.reading-t1.reading) as diff from readings_tab t left join readings_tab t1 on t.id = t1.id+1 order by t.id desc;` [SQL Fiddle](http://sqlfiddle.com/#!9/5c9058/15) – Lakmal Premaratne Aug 21 '17 at 08:26
  • @LakmalPremaratne . . . I would suggest that you ask a new question, with appropriate sample data and a description of what to do. – Gordon Linoff Aug 21 '17 at 12:08
  • Yes I will ask a new question. They have added another key column. – Lakmal Premaratne Aug 21 '17 at 14:42