joining on most recent date
-
Guys, thanks for the help so far....the solution from addict (bottom of page 1)
Select A.Product_id, A.Purch_Date, B.*
From A
Inner Join B
On A.product_id = B.product_id
-- Join to derived table of most recent update per product, per purchase
Inner Join
(
Select A.Product_Id, A.Purch_Date, Max(B.updated_date) As MostRecentUpdate
From B
Inner Join A
On A.product_id = B.product_id
Where B.updated_date <= A.Purch_Date
Group By A.Product_Id, A.Purch_Date
) dtRecent
On (dtRecent.MostRecentUpdate = B.Updated_date And
dtRecent.Purch_Date = A.Purch_Date And
dtRecent.Product_Id = A.Product_ID)
gets me what I need but I have one further scenario.
there mightn't neccessarily be a record in the product edition table.
That is, no MostRecentUpdate value.
I've been trying left joins here and there that will return a product sold on a day along with NULL values for a missing product edition record
sorry to be a pain
ANy further help VERY welcome
Thanks in advance,
Eamon
-
Append this to the 1st query ...
Union All
Select A.Product_id, A.Purch_Date, NULL, [repeat as many Nulls as you need]
From A
Where Not Exists (
Select * From B
Where B.Product_id = A.Product_id
And B.updated_date <= A.purch_date
)
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply