I have a "user profile" database that maintains all changes to a user's profile. The current information is viewed through a view that shows the latest record for each userID. This is great but when I'm looking at reports, I often need to correlate information that was for a user at the time of the entry.
I want the view to show all scores and display the team (at the time of the current score timestamp):
datestamp | userid | team | score
----------|--------|------|-------
1/2/2012 | 1 | Blue | 100
1/3/2012 | 2 | Red | 98
1/4/2012 | 1 | Red | 95
1/5/2012 | 1 | Red | 99
As you can see user 1 changed teams on the 3rd, so the Team column adjusts.
I tried:
SELECT a.datestamp, a.id, b.team, a.score
FROM #scores a
INNER JOIN (
SELECT TOP 1 *
FROM #users c
WHERE c.id = a.id
AND c.datestamp < a.datestamp
) b ON a.id = b.id
But it looks like the nested query can't reference the outside tables this way. I've also attempted this with a function.
INNER JOIN function('1/3/2011') ONworks butINNER JOIN function(a.datestamp) ON
doesn't.
Any sugegstions?
Sample Data:
CREATE TABLE #users ( datestamp datetime, id int, name varchar(10), team varchar(10) )
INSERT INTO #users (datestamp, id, name, team) VALUES ('1/1/2012', 1,'Jason', 'Blue')
INSERT INTO #users (datestamp, id, name, team) VALUES ('1/1/2012', 2,'Mike', 'Red')
INSERT INTO #users (datestamp, id, name, team) VALUES ('1/4/2012', 1,'Jason', 'Red')
CREATE TABLE #scores ( datestamp datetime, id int, score int)
INSERT INTO #scores (datestamp, id, score) VALUES ('1/2/2012',1,100)
INSERT INTO #scores (datestamp, id, score) VALUES ('1/3/2012',1,98)
INSERT INTO #scores (datestamp, id, score) VALUES ('1/4/2012',1,95)
INSERT INTO #scores (datestamp, id, score) VALUES ('1/5/2012',1,99)
INNER JOIN [return results based on date] ON ...
Posted - 01/24/2012 : 14:17:30
