mpdillon
asked on
Inner Join or CTE using a lookup table
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.
e.g. Typical
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:
01/02/2013, 3.
Then if I did the next combination 01/02/2013 compared to 01/30/2013, that result set would be:
01/03/2013, 2
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?
Thanks,
pat
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.
e.g. Typical
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:
01/02/2013, 3.
Then if I did the next combination 01/02/2013 compared to 01/30/2013, that result set would be:
01/03/2013, 2
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?
Thanks,
pat
I'm a little confused, by this:
>>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
are you looking to do a "self join" like this?
select *
from UPTO as u1
|inner|left| join upto as u2 on dateadd(day,-1,u1.timepoin
and ...
but wanting to avoid a date function in that join?
also: What version of SQL Server is this for?
>>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
are you looking to do a "self join" like this?
select *
from UPTO as u1
|inner|left| join upto as u2 on dateadd(day,-1,u1.timepoin
and ...
but wanting to avoid a date function in that join?
also: What version of SQL Server is this for?
in particular, mssql 2012: lead/lag analytic functions
http://msdn.microsoft.com/en-us/library/hh213125.aspx
http://msdn.microsoft.com/en-us/library/hh231256.aspx
http://msdn.microsoft.com/en-us/library/hh213125.aspx
http://msdn.microsoft.com/en-us/library/hh231256.aspx
by the way, are the there any instances where you want "the previous record" but that previous record is more than 1 day different (e.g. over a week-end)?
ThisRecord PreviousRecord
Monday FridayOfWeekBefore
ThisRecord PreviousRecord
Monday FridayOfWeekBefore
ASKER
Racimo,
I have never used a “persisted table valued function column”. I googled it and it seems to be adding an extra column to the table which is calculated by a function. If that is correct, I am concerned it will cause storage problems. Currently this table and indexes are just under 1 TB. I would need to add 5 calculated columns, Year, Month, Day, Hour and IntDay.
I have used the lookup table to do some calculation already. My testing indicated a 10 to 20 fold increase in performance over using the DateDiff and DatePart functions. So I would like to stay with the lookup table for now.
PortletPaul,
Yes the self-join is what I am looking for. I have already done the self-join in the format you have shown. But I want to eliminate the DateAdd function and replace it with the Lookup table using subtraction.
To your second question, the days are always continuous.
I have never used a “persisted table valued function column”. I googled it and it seems to be adding an extra column to the table which is calculated by a function. If that is correct, I am concerned it will cause storage problems. Currently this table and indexes are just under 1 TB. I would need to add 5 calculated columns, Year, Month, Day, Hour and IntDay.
I have used the lookup table to do some calculation already. My testing indicated a 10 to 20 fold increase in performance over using the DateDiff and DatePart functions. So I would like to stay with the lookup table for now.
PortletPaul,
Yes the self-join is what I am looking for. I have already done the self-join in the format you have shown. But I want to eliminate the DateAdd function and replace it with the Lookup table using subtraction.
To your second question, the days are always continuous.
Thanks. I don't disagree with your desire to avoid the function in the join - just trying to clarify.
you didn't answer: what sql server version?
you didn't answer: what sql server version?
ASKER
PortletPaul,
Sorry. both SQL 2012 and 2008 R2.
pat
Sorry. both SQL 2012 and 2008 R2.
pat
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I never could get the Inner Join to work. I used a CTE
with CTEData(Name, Amt, TimePoint, INtDate)
as
(
Select U.Name, U.Amt, U.TImePoint, D.IntDate
)
Select A.Name, A.Amt-B.Amt, A.TimePoint, A.IntDate
From CTEData A
Inner join CTEData B
on A.TimePoint = B.TimePoint - 1
with CTEData(Name, Amt, TimePoint, INtDate)
as
(
Select U.Name, U.Amt, U.TImePoint, D.IntDate
)
Select A.Name, A.Amt-B.Amt, A.TimePoint, A.IntDate
From CTEData A
Inner join CTEData B
on A.TimePoint = B.TimePoint - 1
I think this requires a CTE but I am not sure.
You could do this using a CTE but it won't save much resources if you are using two tables because the main overhead you'd have to face is the JOIN. My first guess is that you'd save more performance by either adding a persisted table valued function column on UPTO then using a to minimize IO's. Try to test it to see.