| DML for test data [message #396436] |
Mon, 06 April 2009 12:16  |
jen804
Messages: 22
Registered: March 2009
Location: USA
|
Junior Member |
|
|
I'm not sure if i'm posting or wording this question right, so here's a test...
I have a file which I made to a table with fields/columns:
Table: Test1
Columns:vendor_no, vendor_name, item_no, customer_no
I need to make a join to another table (test2) to find the most recent records before a certain date for price, rules and date.
The table I will be joining is below and has columns:
Table: Test2
Columns: item_no, customer_no, sell_price, price_code, contract_cost, effective_date, termination_date,etc
What I want to do is join item_no from test1 to item_no on test2 and join customer_no from test1 to customer_no from test2 to find the the contract effective date as of 1/1/2009. Since contract dates can be updated with new dates, I want to restrict so that I only get the most recent records before 1/1/2009.
I've got this far (query below) but I'm stuck as far as adding the next step to restrict the date. I may be taking the wrong approach on this:
select d.vb_no,
d.vendor_name,
d.item_no,
d.customer_no,
p.sell_price,
p.price_code,
p.contract_cost,
p.eff_date,
p.term_date,
p.current_flag
from test1 d, test2 p
where d.item_no=p.item_no
and d.bill_to=p.customer_no
[Updated on: Mon, 06 April 2009 12:21] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
