CODEWITHSUNDEEP
mysqlsql
Vector JX
Vector JX

Reputation: 179

How to compare datetime and get first and last date MySQL

I have below mentioned tables: MySQL Version - 5.7.25

Table1:

ID             ref
T-1            abc-1-x
T-2            abc-2-x
T-3            abc-3-x
T-4            abc-4-x
T-5            abc-5-x

Table2

ref_id        value          date
abc-1-x       250            2019-12-10 14:16:19
abc-2-x       500            2019-12-11 17:21:05
abc-3-x       100            2019-12-12 18:11:01
abc-4-x       150            2019-12-13 05:04:11
abc-5-x       120            2019-12-14 12:10:09

Table3

ID           seq             date2
T-1          1               2019-12-10 14:05:04
T-1          2               2019-12-10 21:17:01
T-1          3               2019-12-11 13:12:12
T-2          1               2019-12-11 14:13:46
T-2          2               2019-12-12 14:05:04
T-3          1               2019-12-12 14:05:04
T-3          2               2019-12-12 17:15:14
T-3          3               2019-12-13 10:09:08
T-3          4               2019-12-14 12:18:14
T-4          1               2019-12-13 01:01:04
T-4          2               2019-12-13 03:03:01
T-5          1               2019-12-15 14:05:04

By utilizing the above mentioned three tables, I want to compare the date of Table2 with Table3 against each ID, and fetch the first date and last date from Table3 against each id.

Scenarios:

Required Output:

ID          Value     date                  date1                 date2                remarks   day_diff
T-1         250       2019-12-10 14:16:19   2019-12-10 14:05:04   2019-12-11 13:12:12  Greater   1
T-2         500       2019-12-11 17:21:05   2019-12-11 14:13:46   2019-12-12 14:05:04  Greater   1
T-3         100       2019-12-12 18:11:01   2019-12-12 14:05:04   2019-12-14 12:18:14  Greater   2
T-4         150       2019-12-13 05:04:11   2019-12-13 01:01:04   2019-12-13 03:03:01  Same      0
T-5         120       2019-12-14 12:10:09   2019-12-15 14:05:04   Null                 Greater   1

Upvotes: 0

Views: 329

Answers (1)

Nick
Nick

Reputation: 147216

You can use a subquery to generate the MAX and MIN dates from Table3 for each entry in Table2 and then generate the Greater/Same/Less values in an outer query:

SELECT *, 
       CASE WHEN DATE(date2) > DATE(date) OR DATE(date1) > DATE(date) THEN 'Greater'
            WHEN DATE(date2) < DATE(date) OR DATE(date1) < DATE(date) THEN 'Less'
            ELSE 'Same'
       END AS remarks,
       DATEDIFF(date2, date) AS day_diff
FROM (
  SELECT t3.ID, t2.Value, t2.date, MIN(t3.date2) AS date1, MAX(t3.date2) AS date2
  FROM Table1 t1
  JOIN Table2 t2 ON t2.ref_id = t1.ref
  JOIN Table3 t3 ON t3.ID = t1.ID
  GROUP BY t3.ID, t2.Value, t2.date
) g

Output:

ID      Value   date                    date1                   date2                   remarks    day_diff
T-1     250     2019-12-10 14:16:19     2019-12-10 14:05:04     2019-12-11 13:12:12     Greater    1
T-2     500     2019-12-11 17:21:05     2019-12-11 14:13:46     2019-12-12 14:05:04     Greater    1
T-3     100     2019-12-12 18:11:01     2019-12-12 14:05:04     2019-12-14 12:18:14     Greater    2
T-4     150     2019-12-13 05:04:11     2019-12-13 01:01:04     2019-12-13 03:03:01     Same       0
T-5     120     2019-12-14 12:10:09     2019-12-15 14:05:04     2019-12-15 14:05:04     Greater    1

Note that if there is only one date for a Table2 value in Table3, this will give the same values for date1 and date2. If you really want NULL for date2 in that case, change the SELECT * in the above query to

SELECT ID, Value, date, date1,
       CASE WHEN date1 = date2 THEN NULL ELSE date2 END AS date2,

Demo on dbfiddle

Upvotes: 1

Related Questions