Geek asks and answers

Summing date range excluding matches in mysql

I have a simple table with columns start_dateand end_date. These date values ​​may overlap.

id    start_date    end_date
1     2011-01-01    2012-04-01
2     2012-05-01    2013-10-01
3     2013-09-01    2014-09-01
4     2013-10-01    2014-08-01
5     2013-12-01    2014-11-01
6     2013-09-01    2014-09-01
7     2015-01-01    2015-11-01

The problem is to find the amount in months. Example: id: 2,3,4,5,6overlaps, so the idea is to take MAX end_dateand MIN start_dateof 2,3,4,5,6and add a date difference of 1 and 7.

At this time: I found how to estimate the date difference in months:

PERIOD_DIFF( DATE_FORMAT(end_date, '%Y%m') , DATE_FORMAT(start_date, '%Y%m') )

I know the idea here is this:

  • Understand whether two dates coincide or not. And if so, then change the dates accordingly (adjust the end dates and start dates).
  • Scrolling through all dates, estimating the difference in dates in months, the amount and the final result of the return.

, , . , - , MySQL-.

0
3

, , , :

SELECT SUM(PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM a.end_date), EXTRACT(YEAR_MONTH FROM a.start_date))) months
  FROM (
    SELECT MIN(g.start_date) start_date, MAX(g.end_date) end_date 
      FROM (
        SELECT @group_id := @group_id + (@end_date IS NULL OR o.start_date > @end_date) group_id,
               start_date,
               @end_date := DATE(CASE 
                 WHEN (@end_date IS NULL OR o.start_date > @end_date) THEN o.end_date
                 ELSE GREATEST(o.end_date, @end_date)
               END) end_date  
          FROM overlap o
          JOIN (SELECT @group_id := 0, @end_date := NULL) init
      ORDER BY o.start_date ASC  
            ) g
  GROUP BY  g.group_id  
        ) a

, end_date, . End_date , , , .

.

. PERIOD_DIFF.

, , SQLFiddle .

+1

, , :

SELECT SUM(PERIOD_DIFF( DATE_FORMAT(end_date, '%Y%m') , DATE_FORMAT(start_date, '%Y%m') )) AS total_periods
FROM table WHERE ...
0

I did it my own way, checking out the other answers here on Stackoverflow, it should work:

select sum(months)
from (select t.*, 
         @time := if(@sum = 0, 0, period_diff(date_format(start_date, '%Y%m'), date_format(@prevtime, '%Y%m'))) as months,
         @prevtime := start_date,
         @sum := @sum + isstart
  from ((select start_date, 1 as isstart
         from position t
        ) union all
        (select end_date, -1
         from position t
        )
       ) t cross join
       (select @sum := 0, @time := 0, @prevtime := 0) vars 
  order by 1, 2
 ) t
0
source

More articles:


All Articles