SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 INNER JOIN [return results based on date] ON ...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Jaypoc
Starting Member

USA
46 Posts

Posted - 01/24/2012 :  14:17:30  Show Profile  Reply with Quote
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') ON
works but
INNER 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)

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/25/2012 :  11:51:12  Show Profile  Reply with Quote
I think you need to use correlated subquery here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Jaypoc
Starting Member

USA
46 Posts

Posted - 01/25/2012 :  13:43:08  Show Profile  Reply with Quote
How would you call the "TOP 1" result from within the correlated subquery?

If I match against the ID number, I get 2 results (team red and tem blue). I need the latest value up to a certain date. That date could be the same or a few days before the target date, or could be several years before. There could be 1 change/update per week, or 7 per day, so I would need just the latest value. (To simplify my example, i left timestamps out, but in the real database they are present.)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000