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!!!

Answers


Try this:

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)

EDIT

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)

Need Your Help

Start alarm in broadcastreciever and turn off in service

android service alarmmanager android-pendingintent

I think I have a design issue, but I will ask you guys the question.

How to set amount of Spark executors?

java scala cluster-computing apache-spark yarn

How could I configure from Java (or Scala) code amount of executors having SparkConfig and SparkContext? I see constantly 2 executors. Looks like spark.default.parallelism does not work and is about

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.