SQL Query for Updating Dates table

I've been having a lot of trouble with this, so hopefully you experts can help me.  I am using SQL Server 2008 R2.  I have a table of dates and I need to update the Effective Date based on a second table.  Here are the tables:
Table 1:
ID      Effective_Date
1      10/30/2011
1      11/10/2011
1      11/16/2011
1      11/30/2011

Table 2:
Date      Effective_Date
11/6/2011      NULL
11/7/2011      NULL
11/8/2011      NULL
11/9/2011      NULL
11/10/2011      NULL
11/11/2011      NULL
11/12/2011      NULL
11/13/2011      NULL
11/14/2011      NULL
11/15/2011      NULL
11/16/2011      NULL
11/17/2011      NULL
11/18/2011      NULL
11/19/2011      NULL
11/20/2011      NULL
11/21/2011      NULL
11/22/2011      NULL
11/23/2011      NULL
11/24/2011      NULL
11/25/2011      NULL
11/26/2011      NULL
11/27/2011      NULL
11/28/2011      NULL
11/29/2011      NULL
11/30/2011      NULL
12/1/2011      NULL
12/2/2011      NULL
12/3/2011      NULL
12/4/2011      NULL
12/5/2011      NULL
12/6/2011      NULL

What I need to do is update Table 2 with the Effective Date from Table 1.  The tricky part is that the Effective_Date can be earlier than the Date. The results should look like this:

Date      Effective_Date
11/6/2011      10/30/2011
11/7/2011      10/30/2011
11/8/2011      10/30/2011
11/9/2011      10/30/2011
11/10/2011      11/10/2011
11/11/2011      11/10/2011
11/12/2011      11/10/2011
11/13/2011      11/10/2011
11/14/2011      11/10/2011
11/15/2011      11/10/2011
11/16/2011      11/16/2011
11/17/2011      11/16/2011
11/18/2011      11/16/2011
11/19/2011      11/16/2011
11/20/2011      11/16/2011
11/21/2011      11/16/2011
11/22/2011      11/16/2011
11/23/2011      11/16/2011
11/24/2011      11/16/2011
11/25/2011      11/16/2011
11/26/2011      11/16/2011
11/27/2011      11/16/2011
11/28/2011      11/16/2011
11/29/2011      11/16/2011
11/30/2011      11/30/2011
12/1/2011      11/30/2011
12/2/2011      11/30/2011
12/3/2011      11/30/2011
12/4/2011      11/30/2011
12/5/2011      11/30/2011
12/6/2011      11/30/2011

Any help would be greatly appreciated!  Thanks in advance!
LVL 8
Forefront_Data_SolutionsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale BurrellDirectorCommented:
Can you explain the logic for how you select the effective date. It looks like you might be trying to fit them into weeks or similar?

Knowing how you determine which effective date to use is essential to know how to query the second table.
0
Forefront_Data_SolutionsAuthor Commented:
Our users can change their profile data as often as once per day.  When they change their data, we insert a new row with the date (Effective_Date) into Table 1.   That data is "in effect" until they change it.  What will happen eventually is that a user can select a range of dates and interval (daily, weekly, monthly, or yearly), and we will return some values based on the effective date.  Table 2 is the date intervals.  So, if the start date they select is 11/6/2011, I need to find the data that was in effect on that day.  In this case, the last time they made a change was on 10/30/2011, so that is the Effective_Date for 11/6.  On 11/10/2011, the Effective_Date would change to 11/10/2011 since they made a change on that day.  That date would remain in effect until 11/16, etc.  Hope this makes sense.
0
Dale BurrellDirectorCommented:
Hmmm... I think I understand... see if the following works as a query:

select Date,
  (
    select top 1 Effective_Date
    from Table1
    where Effective_Date <= Table2.Date -- and Table1.UserId = Table2.UserId??? I don't know what fields you are using
    order by Effective_Date desc
  )
from Table2

then if that works convert it into an update.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Forefront_Data_SolutionsAuthor Commented:
Nice!  Thank you very much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.