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!
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!
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Nice! Thank you very much!
Knowing how you determine which effective date to use is essential to know how to query the second table.