CODEWITHSUNDEEP
mysql
Anji007
Anji007

Reputation: 133

MySQL query to get sum of difference between Start date and End date

I have a table which has the following data:

+-----------+-----------+
| Date_from | Date_to   |
+-----------+-----------+
| 20-NOV-19 | 22-NOV-19 |
+-----------+-----------+
| 10-NOV-19 | 21-NOV-19 |
+-----------+-----------+
| 14-NOV-19 | 26-NOV-19 |
+-----------+-----------+

I need a query to find the sum of the difference between date_from and date_to.

Example:

The query result should be

15 days (2+9+4)

Any help would be much appreciated.

Upvotes: 0

Views: 1102

Answers (3)

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

You can use CTE to generate all the dates between the range and get the distinct count().It will give you the total number of days as 17. Since you want difference you have to subtract by 2(start date and end date)

WITH recursive Date_Ranges AS (
    select datefrom as Dt,dateto from mydates
   union all
   select dt + interval 1 day , dateto
   from Date_Ranges
   where dt < dateto)
select count(distinct(dt))-2 from Date_Ranges

DEMO HERE

Upvotes: 0

Kishan
Kishan

Reputation: 793

You can use below query to get total. Please change Table_name with your actual table name

SELECT SUM(TIMESTAMPDIFF(DAY,Date_from,Date_to)) as total FROM Table_name

Upvotes: 1

Joakim Danielson
Joakim Danielson

Reputation: 51892

I used window functions in a sub-query to calculate the difference between date_from and date_to and then subtracting any overlapping days

SELECT SUM(days) FROM
    (SELECT CASE WHEN LEAD(date_from) OVER w  < date_to THEN DATEDIFF(date_to, date_from) + DATEDIFF(LEAD(date_from) OVER w, date_to)
          ELSE DATEDIFF(date_to, date_from)
          END AS days
          FROM test
          WINDOW w AS (ORDER BY date_to)) as d

Note though that this produces the result 16 days, not 15 as in the question, but then again so does 

SELECT DATEDIFF('2019-11-26', '2019-11-10')

Upvotes: 0

Related Questions