Inner Join or CTE using a lookup table
Posted on 2013-06-18
I want to return a result set that compares the rows in a table to the very same same that are offset by a day. I want to do this using a Lookup table rather than the DateDiff function.
I have one table named UPTO. It contains 4 columns, IDNo, Name, Amt and TimePoint. The second table is a lookup table, DateLookup. It contains two Columns, TimePoint and IntDate.
UPTo.TimePoint is a Date Column. I populate it with the Date and Hour. Minutes and seconds are always 0.
Sample Upto Table data:
1, Loc1, 5,01/01/2013 01:00:00
1, Loc1, 8,01/02/2013 01:00:00
1, Loc1, 10,01/03/2013 01:00:00
In the DateLookup table the IntDate column is just an integer that increases by 1 for each new day.
01/01/2013 01:00:00, 100
01/02/2013 01:00:00, 101
01/03/2013 01:00:00, 102
What I would like to accomplish is to compare the records in the UpTo table to the Day before records in the UpTo table. Sorry for the fuzzy explanation.
Here is an example.
I want to compare the Amt on 01/01/2013 01:00:00 to the Amt on 01/02/2013. The results set would be:
Then if I did the next combination 01/02/2013 compared to 01/30/2013, that result set would be:
I want to use the lookup table and subtract 1 from the IntDate column. I do not want to use a DateDiff directly on the TimePoint column. The actual tables have 100's of millions of rows and I want to avoid the DateDiff overhead.
I think this requires a CTE but I am not sure.
Would someone please propose a possible query to get the desired result set?