CODEWITHSUNDEEP
mysqlsqldatedatetime
Ashish
Ashish

Reputation: 207

MySQL select rows where given date lies between the dates stored in table

Suppose I have some data like:

id  status activity_date
--- ------ -------------
101   R     2014-01-12
101   Mt    2014-04-27 
101   R     2014-05-18
102   R     2014-02-19

Note that for rows with id = 101 we have activity between 2014-01-12 to 2014-04-26 and 2014-05-18 to current date.

Now I need to select that data where status = 'R' and the date is the most current date as of a given date, e.g. if I search for 2014-02-02, I would find the status row created on 2014-01-12, because that was the status that was still valid at the time for entity ID 101.

Upvotes: 3

Views: 1074

Answers (7)

mykel
mykel

Reputation: 426

You can make a query to effectively give you the most status as of a date, e.g.

SELECT
    id, 
    substr(max(concat(activity_date, status)),11) as status,
    max(activity_date) as activity_date
FROM table
WHERE activity_date <= '2014-02-02'
GROUP by id;

Then, similar to Salman's answer, you can use this result inside another query and look for all those results with a status of 'R'

SELECT * from (
    SELECT
        id,
        substr(max(concat(activity_date, status)),11) as status,
        max(activity_date) as activity_date
    FROM table
    WHERE activity_date <= '2014-02-02'
    GROUP by id
) AS temp WHERE temp.status = 'R';

Edit: Rather than use the questionable method of sorting the statuses, you could identify the relevant maximum record with a sub-query, so the original query would become

SELECT join1.* FROM table AS join1
INNER JOIN (
    SELECT id, max(activity_date) as max_activity_date
    FROM table
    WHERE activity_date < '2014-02-02'
    GROUP BY id
) AS join2
ON join1.id = join2.id AND join1.activity_date = join2.max_activity_date;

and the full query

SELECT * from (
    SELECT join1.* FROM table AS join1
    INNER JOIN (
        SELECT id, max(activity_date) as max_activity_date
        FROM table
        WHERE activity_date < '2014-02-02'
        GROUP BY id
    ) AS join2
    ON join1.id = join2.id AND join1.activity_date = join2.max_activity_date
) AS temp WHERE temp.status = 'R';

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272386

If I understand correctly:

Step 1: Convert the start and end date rows into columns. For this, you must join the table with itself based on this criteria:

SELECT
    dates_fr.id,
    dates_fr.activity_date AS date_fr,
    MIN(dates_to.activity_date) AS date_to
FROM test AS dates_fr
LEFT JOIN test AS dates_to ON 
    dates_to.id = dates_fr.id AND
    dates_to.status = 'Mt' AND
    dates_to.activity_date > dates_fr.activity_date
WHERE dates_fr.status = 'R'
GROUP BY dates_fr.id, dates_fr.activity_date

+------+------------+------------+
| id   | date_fr    | date_to    |
+------+------------+------------+
|  101 | 2014-01-12 | 2014-04-27 |
|  101 | 2014-05-18 | NULL       |
|  102 | 2014-02-19 | NULL       |
+------+------------+------------+

Step 2: The rest is simple. Wrap the query inside another query and use appropriate where clause:

SELECT * FROM (
    SELECT
        dates_fr.id,
        dates_fr.activity_date AS date_fr,
        MIN(dates_to.activity_date) AS date_to
    FROM test AS dates_fr
    LEFT JOIN test AS dates_to ON 
        dates_to.id = dates_fr.id AND
        dates_to.status = 'Mt' AND
        dates_to.activity_date > dates_fr.activity_date
    WHERE dates_fr.status = 'R'
    GROUP BY dates_fr.id, dates_fr.activity_date
) AS temp WHERE '2014-02-02' >= temp.date_fr and ('2014-02-02' < temp.date_to OR temp.date_to IS NULL)

+------+------------+------------+
| id   | date_fr    | date_to    |
+------+------------+------------+
|  101 | 2014-01-12 | 2014-04-27 |
+------+------------+------------+

SQL Fiddle

Upvotes: 1

williamscodes
williamscodes

Reputation: 104

I completely agree with Salman's response, the table could be designed in a fashion that allows for greater query accuracy and extensibility. However, the question asked, with regards to a query selecting information based on status and date range can be expressed as.

SELECT * FROM Table_1
WHERE ((status = 'R')
AND ((activity_date BETWEEN '2014-01-12' AND '2014-04-26')
OR activity_date  > CONVERT(DATETIME, '2014-05-17')))

This will select all data with a status of 'R' and will use the BETWEEN operator for the range desired; moreover, the conversion of the final operator is because the expression is evaluated as a mathematical expression and requires explicit conversion.

Upvotes: -1

zaxliu
zaxliu

Reputation: 2806

try the following

SELECT *
FROM your_relation
WHERE status='R'
AND activity_data="2014-02-02"

Upvotes: -1

Raj Kamuni
Raj Kamuni

Reputation: 388

I think you need following ans

SELECT id,MAX(CAST(ACTIVITY_DATE AS date),MIN(CAST (ACTIVITY_DATE AS date)  
FROM Table_Name WHERE CAST('2014-02-02' AS date)
BETWEEN  MIN(CAST (ACTIVITY_DATE AS date) AND MAX(CAST(ACTIVITY_DATE AS date) 
 AND Status='R'
GROUP BY id

Upvotes: 1

Patryk Imosa
Patryk Imosa

Reputation: 824

You can try 

select id, status, activity_date 
from TABLE 
where status = "R" and activity_date = "2014-02-02"

where TABLE is name of your table

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172618

Try this:

select * from yourtable 
where status='R' and activity_date= '2014-02-02'

Upvotes: 0

Related Questions