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 | 1001/3/2012 | 2 | Red | 981/4/2012 | 1 | Red | 951/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.scoreFROM #scores aINNER 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 - 2012-01-24 : 14:17:30
