MySQL creating date ranges based on another column

Question

I have a calendar table that looks like this:

    date   |  qty
-------------------
1998-11-18 |  2
1998-11-19 |  0
1998-11-20 |  0
1998-11-21 |  0
1998-11-22 |  3
1998-11-23 |  0
1998-11-24 |  0
1998-11-25 |  3

I am trying to generate a report which would give the ranges of the entries with the 0 values for quantity (qty). I can't hard code the dates, they need to be determined by the value of the second column. From the above, the report should look like this:

1998-11-19 - 1998-11-21  
1998-11-23 - 1998-11-24

All the GROUP BY and UNION queries give me the individual entries but I can't figure out how to create the date ranges.

alexkorep · Accepted Answer · 2012-09-23 07:28:22Z

1

Assuming the table name is calendar, the following query does the job:

SELECT 
   MIN(t.date),
   MAX(t.date)
from
    (SELECT
        c.date,
        MAX(prev.date) mindat,
        MIN(next.date) maxdat
    FROM 
        calendar c,
        calendar prev,
        calendar next
    WHERE 
        c.qty = 0 AND
        c.date > prev.date AND prev.qty != 0 AND
        c.date < next.date AND next.qty != 0
    GROUP BY
        c.date) t
GROUP BY
    mindat, maxdat

In internal query we basically join calendar table to itself twice: first join (c table to prev table) selects maximum date which is smaller than date in for each c table row. That gives us the beginning of interval the row belongs to. Second join selects interval ending the same way.

External query extracts minimum and maximum dates from the intervals.

I tested query with your data and here is the output:

min(t.date) max(t.date)
1998-11-19  1998-11-21
1998-11-23  1998-11-24

answered Sep 23, 2012 at 7:28

alexkorep

5312 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ethan Finni Over a year ago

I just tried it but only works for the case there is only two ranges in the data with qty zero. The table has multiple instances where date ranges have zero quantities. Is there a way to search through the entire table?

alexkorep Over a year ago

It should look through entire table, I'm not sure what could be wrong here. It might incorrectly process the open intervals (intervals with zero quantities at the beginning and the end of the table). Could you please give me an example of data which is not processed correctly?

Ethan Finni · Accepted Answer · 2012-09-23 18:32:41Z

0

After trying a number of things, the query that solves the problem is:

SELECT start_date, MAX(dates) AS End
FROM (
  SELECT calendar.*,
         @f:=CONVERT(
             IF(@r<=>gty AND DATEDIFF(dates, @d)=1, @f, dates), DATE
           ) AS start_date,
           @d:=dates, @r:=gty
  FROM     calendar JOIN (SELECT @d:=NULL) AS init
  where gty="0"
  ORDER BY gty, dates
)  AS t
GROUP BY start_date, gty;

Thank you all for your help.

answered Sep 23, 2012 at 18:32

Ethan Finni

211 bronze badge

Collectives™ on Stack Overflow

MySQL creating date ranges based on another column

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related