SQL Server 2005 - How to take a record from one table and loop through another table looking for match
I have 2 tables. for this example I will use only one users records. The first table has the user name and an evaluation date as such:
USER EVALDATE -------------- bobr 6/7/2010 bobr 9/20/2010 bobr 9/21/2010
The above table needs to be joined against this user history table, which has the history of the ID's and the dates they were valid, to look for a match (the NULL date means current):
USER STARTDATE ENDDATE ---------------------------- bobr 2/20/2006 4/18/2010 bobr2 4/19/2010 9/7/2010 bobr 9/8/2010 null
What I'm trying to do in SQL Server 2005 is take the first record from the first table, loop it through the second table and when(if) the EVALDATE is within one of these date ranges and the IDs match, then flag that record from the first table as valid.
The current code takes the record from the first table and runs against all rows of the second table and kicks out a record for each invalid evaldate, so it kicks out a record when joined against the second table because the evaldate is not between the dates of the first record on the history table, even though the record is fine because the evaldate is between the start and end dates of the third record in the history table.
I hope this makes sense! In something like SAS I can create an array and loop through checking against each record in the history table. How do I do this in SQL? What I was trying to do was just update the first table with a flag if the records dates are invalid. Any ideas? Thanks!!!
SELECT [USER] ,[EVALDATE] ,CASE WHEN ( SELECT COUNT(*) FROM [UserStartEndDates] b WHERE [a].[USER] = [b].[User] AND [EVALDATE] BETWEEN [STARTDATE] AND COALESE([ENDDATE],[EVALDATE]) ) > 0 THEN 1 ELSE 0 END AS [IsValid] FROM [Evaluations] a
You can try something like
Select * FROM Users u INNER JOIN UserHistory uh ON u.User = uh.User AND u.EvalDate BETWEEN uh.StartDate AND ISNULL(uh.EndDate, u.EvalDate)
Try this for all values from User
Select u.*, CASE WHEN uh.User IS NULL THEN 'Invalid' ELSE 'Valid' END Validity FROM Users u LEFT JOIN UserHistory uh ON u.User = uh.User AND u.EvalDate BETWEEN uh.StartDate AND ISNULL(uh.EndDate, u.EvalDate)