SQL - Getting the max effective date less than a date in another table -

I am currently working on a conversion script which transfers a group of earlier data from a SQL Server 2000 database And a SQL Server 2008. During this thing I am trying to complete, from which to eliminate all things and change them with the "proper" primary key. Obviously, when I move data, Foreign workers in table structures Minister's values ​​need to be injected.

I am currently living with a data set and I can not get my head around it, in a set based fashion, the two tables I am working with are called Charges and Laws. They have 1: 1 relationship and "link" on three columns. The first two LawSource and LawStatue columns have the same link, but the third column is causing problems to me. The Chargedate column should be linked to the law related columns, where Loudet and Lt; = Charge date

My current question is returning more than one line (in some cases) for a single fee because there can be more than one law in law which is less than or equal to the charget.

What I currently have here:

  Select lawId from law to be included on b b. LawSource = a.LawSource and B.LawStatute = a .LawStatute and b.ChargeDate & gt; = A.LawDate  

In any way, I can rewrite this to get the most recent entry in the law table, which is similar (or earlier) on date on the date of the charge < /p>

This will be easy with the split function in SQL 2008 (hence, it should be easy for you in the future) .

Common warnings of "I do not have your schema, so it is not checked", but I think you should do whatever you need.

  Select L. Join Lawe Law I (b.LawSource = a.LawSource and b.LawStatute = a.LawStatute and b.ChargeDate> =) to join law with Lawsource, A.Lostatieu, Max (A.Londet) method. Choose from a.LawDate group. A.Lawsource, a.LawStatue) at L. LAS source = D. Los Source and L. Lostatat = D. Lostast and L. Loud = D. Loud