CODEWITHSUNDEEP
phpmysql
vpbot
vpbot

Reputation: 2487

Get records within range and overlapping records falling within range

I have the following data:

===============================
PK  | StartDate  |  EndDate
===============================
1   | 2016-05-01 |  2016-05-01
2   | 2016-05-02 |  2016-05-25
3   | 2016-04-01 |  2016-06-01
4   | 2016-02-21 |  2016-06-01
5   | 2016-05-01 |  2016-06-01
===============================

My objective is to get all dates between 2016-05-01 and 2016-05-31 and any records that falls within May 2016

Thus the final result:

===============================
PK  | StartDate  |  EndDate
===============================
1   | 2016-05-01 |  2016-05-01
2   | 2016-05-02 |  2016-05-25
3   | 2016-04-01 |  2016-06-01
5   | 2016-05-01 |  2016-06-01
===============================

I was thinking of the following,

SELECT events_sysid, title, startdate, enddate, location, longlat, categoryid 
FROM events
WHERE 1=1 AND isactive = 1 AND userid = 2
AND (DATE(startdate) >= DATE('2017-05-01') AND DATE(enddate) <= DATE('2017-05-31'))

But this only yield;

===============================
PK  | StartDate  |  EndDate
===============================
1   | 2016-05-01 |  2016-05-01
2   | 2016-05-02 |  2016-05-25    
===============================

I've got lost on how to extract the "Any records that falls within May 2016"

Missing:

===============================
PK  | StartDate  |  EndDate
===============================
3   | 2016-04-01 |  2016-06-01
5   | 2016-05-01 |  2016-06-01
===============================

Upvotes: 0

Views: 47

Answers (3)

Arun V K
Arun V K

Reputation: 94

I understand from

My objective is to get all dates between 2016-05-01 and 2016-05-31 and any records that falls within May 2016

that, in short you need to get the dates in May irrespective of they start or end in May.

So try this : 

SELECT *
FROM `dateTable` 
WHERE `StartDate` >= '2016-05-01' OR `EndDate` <= '2016-05-31'

Upvotes: 0

Kamlesh Uikey
Kamlesh Uikey

Reputation: 101

Answer is simple you need to yield any record which fall in may, so you need to check whether it was created before month end and it was ended any after start of may month

Date(startdate) < "2016-06-01 00:00:00" AND Date(enddate) > "2016-04-30 23:59:59"

Upvotes: 0

Amir Rahimi Farahani
Amir Rahimi Farahani

Reputation: 1590

To find overlapping records with May, we are interested in those records that start before end of May and end after the start of May:

StartDate <= '2017-05-31' AND EndDate >= '2017-05-01'

In other words, we are not interested in records that start after end of May and the records that end before the start of May:

NOT (StartDate > '2017-05-31' OR EndDate < '2017-05-01’)

Upvotes: 2

Related Questions