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