Imagine I have a database with historic price data and a second table with dates
A new row was entered into the price history table every time the price changed.
How can I (efficiently) join the two tables so that I can get the price each customer had to pay on that day?
I need the syntax for teradata, thanks!
Best Answer
I'm afraid that I cannot guarantee 100% Teradata compatibility for my answer as there are, to my knowledge, no Teradata fiddles available. However, I have used PostgreSQL, one of the more standards compliant systems and there are no PostgreSQL specific constructs used in my answer (see the fiddle
here
), so I think we're good to go!I glanced at the Teradata documentation for Window functions (
LAG and LEAD
) and for Common Table Expressions (CTE
s) and comparing it with PostgreSQL's syntax (Window
functions,CTE
s), it all seems fairly vanilla.First thing I did was
CREATE
and populate the tables. As an aside, you should avoid using images on StackExchange for the reasons outlined in this link. Use text for DDL and DML or, better yet, provide a fiddle - if you can't find a Teradata one (I looked and couldn't), use something like PostgreSQL which should get you most of the way there.Another small note, it's not a good idea to name your fields using
SQL keywords
. Liberal use of the_
(underscore) character is a big help here.and
I then ran the following queries - I've left in extraneous fields which aren't required for the final calculation so that my thought processes could be followed - using
LAG
andLEAD
can be tricky and it's easy to mix up which field you should be comparing with which other one, so my policy is to leave them all in and let the OP (Original Poster - i.e. you) decide which you find useful and want to keep and which you wish to eliminate.If you don't have a version with
CTE
s, you can always use a subquery.Result:
Notice the use of the defaults
2000-01-01
and2038-01-01
- I'm implicitly assuming that the prices before the specified dates in theprice
table go back to 2000 and that prices after will continue till 2038 when*nix time
will explode! Obviously, you can change these for values more suited to your use case. I've left it up to you to provide default prices, again as per your use case.Then, I ran:
Result (again, cull fields as you see fit):
Which is, AFAICT, the correct answer. You may want to test with edge cases which I may have overlooked - if there's a problem, get back to me. +1 for an interesting and challenging question and welcome to the forum!