CODEWITHSUNDEEP
mysql
Fabian Ortner
Fabian Ortner

Reputation: 27

Get difference between two records

I got a User-Information table where every 24 hours a new record is added for each user. This record contains a user_id, a value (a counter) and the creation date.

TBL_EXAMPLE
ID | user_id | cnt_val | record_date
--------------------------------------------
1  | 10      | 46      | 2019-02-05 12:14:35
2  | 21      | 12      | 2019-02-05 12:14:35
3  | 32      | 453     | 2019-02-05 12:14:35
4  | 10      | 23      | 2019-02-06 16:11:21
5  | 21      | 34      | 2019-02-06 16:11:21
6  | 32      | 480     | 2019-02-06 16:11:21
7  | 10      | 31      | 2019-02-07 11:34:25
8  | 21      | 44      | 2019-02-07 11:34:25
9  | 32      | 489     | 2019-02-07 11:34:25
...

Expected Result:

User 10 Counter: 46 .. 31 --> Difference: 15
User 21 Counter: 12 .. 44 --> Difference: 32
User 32 Counter: 453.. 489 --> Difference: 36

I want to make a list of each difference for each specific user from the oldest to the newest data record in the table dynamically.

Upvotes: 1

Views: 61

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133400

you could use inner join twice on table_exeple and a subquery for min and max date 

select distinct t1.user_id,  t1.cnt_va - t2.cnt_val 
from  (
  select user_id , min(date) min_date,  max(date) max_date
  from TTBL_EXAMPLE 
  group by user_id  
) tmm 
inner join TTBL_EXAMPLE t2  ON  t2.date = tmm.max_date
    and t2.user_id = tmm.user_id
inner join  TBL_EXAMPLE t1  ON t1.date = tmm.min_date
    and t1.user_id = tmm.user_id

Upvotes: 2

Related Questions