CODEWITHSUNDEEP
mysqlsql
Serafeim
Serafeim

Reputation: 15104

SQL query to get historic data from table

I have a table that stores the history of status changes to a person like this:

id | username | date | status

The date field is when the status was updated and the status field contains the new status that the person has since that date.

So the date in my table could be something like this:

1 | serafeim | 2012-03-03 | "NEW"
2 | john | 2012-03-05 | "NEW"
3 | serafeim | 2012-03-13 | "PENDING"
4 | serafeim | 2012-03-15 | "OLD"
5 | john | 2012-03-05 | "PENDING"

etc etc.

Now, I'd like to have a query that for a specific date in the past will retrieve the status that each user had then. For instance, for 2012-04-14 I'd like to get the following results

serafeim | "PENDING"
john | "NEW"

for 2012-03-04 I should get

serafeim | "NEW"

Can anybody think of an SQL query that will do that ? I don't want to do this programatically ! I'm using mysql but I don't think that that's relative to my problem...

Thanks in advance

Upvotes: 0

Views: 4033

Answers (3)

il_guru
il_guru

Reputation: 8508

I cannot test right now on a MySql database, but this query should do the job.

The table2 query retrieve the max date in which you registered an event for every user name before the desired date: this should be the last status event for that person.

The join get the status.

select username, status from table
join
(
select username, max(date) as maxdate from table
where date <= '2012-04-14'
group by username ) table2
on table.username = table2.username and table.date = table.2maxdate

Another way could be without join 

select username, status from table
where date = (select max(date) as maxdate from table
where date <= '2012-04-14'
group by username )

Upvotes: 1

Alberto De Caro
Alberto De Caro

Reputation: 5213

Get the first record of the user having the date less or equal to the input date:

declare @userid nvarchar(128)
declare @date datetime

SELECT userid, status
FROM
(
  SELECT limit 1 * FROM mytable
  WHERE date <= @date
    AND userid = @userid
  ORDER by date desc
)

Untested! And sorry if any syntax error.

Upvotes: 1

Vikdor
Vikdor

Reputation: 24134

The following query identifies the latest record for a given username, before the date specified, and joins the history table with that latest record ID to fetch the rest of the details.

SELECT a.* 
FROM 
   history a
   JOIN (SELECT username, MAX(id) 'id' FROM history 
         WHERE date < @inputDate
         GROUP BY username
        ) as b
   ON a.id = b.id

Upvotes: 3

Related Questions