If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lag 1 day relationship
Sorry if this is a duplicate, but I tried lots of searches here. I can't
seem to find my answer anywhere. I have 2 tables that I need to join by date and ID. The problem is that there is a day lag in one of the tables, i.e. date = 5/12/10 in T1 is equivalent to date = 5/11/10 in T2. I cannot change the structure of either of these tables, so please do not suggest that. I need to be sure I'm comparing the proper dates between tables. I can't simply do a dateadd -1 since there are holidays and weekends to consider. I also don't want to have to create a holiday table since it is one more thing that would have to be maintained. (Meaning it would get forgotten.) I can do this if I only pull 1 date, but I have to pull MTD data, i.e. a range. My current technique won't easily work for a range. Is there a way to create a date2 column in T1 so I can query the same range in both tables, but date2 would contain the -1 business day that I could then use to join the 2 tables? I was thinking there might a way to look for the next lower date using the date column as a base. Hopefully this all makes sense. Here's my data structure, and my proposed date2 column... T1 date ID date2 5/12/10 1 5/11/10 5/12/10 2 5/11/10 5/11/10 1 5/10/10 5/11/10 2 5/10/10 5/10/10 1 5/7/10 5/10/10 2 5/7/10 5/7/10 1 5/6/10 5/7/10 2 5/6/10 T2 date ID 5/11/10 1 5/11/10 2 5/10/10 1 5/10/10 2 5/7/10 1 5/7/10 2 5/6/10 1 5/6/10 2 This way I could join using date in T2 and date2 in T1. Any help is greatly appreciated. I'm totally stuck. -- Cheers, Michelle "Have no fear of perfection - you’ll never reach it." - Salvador Dali |
Thread Tools | |
Display Modes | |
|
|