CODEWITHSUNDEEP
mysql
M. Ahmad Zafar
M. Ahmad Zafar

Reputation: 4939

MySQL Query to get the record for a specific date

I have the following table named staff_status with structure and records:

----------------------------------------------------
| id (INT) | status (VARCHAR) | status_date (DATE) |
----------------------------------------------------
|   1      |     Working      |    2009-05-03      |
|   2      |     Working      |    2009-07-21      |
|   1      |     Leave        |    2010-02-01      |
|   1      |     Working      |    2010-02-15      |
----------------------------------------------------

Now I want to query this to get the status of the staff on a specific date. Example: status of id = 1 on 2010-02-10 should return Leave while on 2010-03-01 should return Working

What I have tried without success:

SELECT t1.status FROM staff_status t1 INNER JOIN (SELECT * FROM staff_status WHERE id = 1 AND status_date < '2010-02-10') t2 ON (t1.id = t2.id AND t1.status_date < t2.status_date);

Upvotes: 2

Views: 604

Answers (5)

Omesh
Omesh

Reputation: 29121

try this:

SELECT IFNULL((SELECT status 
               FROM staff_status 
               WHERE id = 1 AND
                     status_date = '2010-02-10'), 
               "Leave") AS status;

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24116

try this:

select status 
from staff_status 
where status_date<='2010-03-01'
and id=1
order by status_date desc 
limit 1

Upvotes: 0

Andreas Christodoulou
Andreas Christodoulou

Reputation: 650

Surely simply:

SELECT status FROM staff_status WHERE status_date = '2010-02-10'

Would return you "leave"?

Upvotes: 0

Thomas Kelley
Thomas Kelley

Reputation: 10302

First, you'll need the MAX() of the dates per id:

SELECT id, MAX(status_date)
FROM staff_status
WHERE status_date < "2010-02-10" GROUP BY id

...but MySQL doesn't guarantee that the status will be from the row of the MAX(status_date) (in fact, this is almost never the case). So you'll have to take the information you found above, and pull out those records from the original table, matching on id and status_date:

SELECT id, status
FROM staff_status
WHERE
    (id, status_date)
    IN
    (
        SELECT id, MAX(status_date)
        FROM staff_status
        WHERE status_date < "2010-02-10" GROUP BY id
    );

This generates a list of ids and statuses for the most recent date found before 2010-02-10:

+------+---------+
| id   | status  |
+------+---------+
|    2 | Working |
|    1 | Leave   |
+------+---------+
2 rows in set (0.01 sec)

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166606

You could try something like

SELECT  s.*
FROM    staff_status s INNER JOIN
        (
            SELECT  id,
                    MAX(status_date) status_date
            FROM    staff_status
            WHERE   status_date < '2010-02-10'
            AND     id = 1
        ) m ON  s.id = m.id
            AND s.status_date = m.status_date

Additionaly you could try an ORDER BY status_date DESC LIMIT 1

from 13.2.8. SELECT Syntax

Something like

SELECT  *
FROM    staff_status
WHERE   id = 1
AND     status_date < '2010-02-10'
ORDER BY    status_date DESC
LIMIT 1

Upvotes: 2

Related Questions