How to get Maximum Difference between Dates in a row using SQL

user_17691316 0
Viewed 44
Date 1 Date 2 Date 3 Date 4 LineCount Month_Gap
2020-01-01 2019-10-01 2019-09-06 1
2020-01-01 2019-10-01 2019-09-13 2019-09-06 2 0
2020-01-01 2019-10-01 2019-08-13 2019-09-06 2 1

If the LineCount is 1, then Month_Gap should be the maximum month difference between (Date1 & Date3) and (Date2 & Date3). Date3 will always be in between Date1 and Date2.

In this Case, the output should be the max month difference between (2020/01/01 - 2019/09/06) and (2019/10/01 - 2019/09/06), which is 3 months:

Date 1 Date 2 Date 3 Date 4 LineCount Month_Gap
2020-01-01 2019-10-01 2019-09-06 1 3
2020-01-01 2019-10-01 2019-09-13 2019-09-06 2 0
2020-01-01 2019-10-01 2019-08-13 2019-09-06 2 1

I was trying something like this but not sure how to go about it - CASE WHEN LineCount = 1 THEN MAX(DATE_DIFF(.....), which won't work I guess.

amazon-athena amazon-web-services date sql
2 Answers

The pattern you should use is

SELECT TIMESTAMPDIFF("MONTH", LEAST(date1,date2,date3,date4), GREATEST(date1,date2,date3,date4)) as `maximum_difference`;

This will simply look through your columns, find the least and greatest, and return the result.

user_64958890 
    SELECT 
    CASE WHEN LineCount = 1 THEN GREATEST(DATE_DIFF('month', Date3, Date1), 
    DATE_DIFF('month', Date3, Date2)) END AS Month_Gap
user_176913160
Related