• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

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!
0
Forefront_Data_Solutions
Asked:
Forefront_Data_Solutions
  • 2
  • 2
1 Solution
 
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
 
Forefront_Data_SolutionsAuthor Commented:
Nice!  Thank you very much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now