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

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.

CodePudding user response：

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.

CodePudding user response：

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