joining on most recent date
-
Hi,
I have two tables:
Table A:
product_id int
purch_date datetime
Table B:
Product_id int
updated_date datetime
Table B hold a history of a profile of a product.
I wish to create a join between the table that will link a product sold to a product profile on that day. Sounds easy but of a day when a product is sold the latest version (on table B) but be a few days previous.
Any help welcome,
Eamon
-
select *
from TableA
inner join TableB
on purch_date = b.updated_date
where b.updated_date = (select max( c.updated_Date)
from TableB c
where c.product_id = b.productid)
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
running this gives me
Server: Msg 147, Level 16, State 2, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
i'm using sql 7 also
-
also.....
a product may be purchased on say 5th Dec 2005
but the product's last profile update may be 23 Nov 2005.
just though i would add that in just in case.
Eamon
-
Please post the exact statement you are running that is giving you the error.
ron
-
apologies, that reported error message was a typo !
but it still returns the wrong information.
basically i'm looking for the recent match.
-
Use a derived table to locatethe most recent update per product, and join to it.
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
Inner Join
(
Select Product_Id, Max(updated_date) As MostRecentUpdate
From B
Group By Product_Id
) dt Recent
On (dtRecent.MostRecentUpdate = B.Updated_date)
-
i knew this was tricky.
the join has to exist to, yes, the most recent, but yes, recent as per the purchase date. we don't want to reference an edition of the product after the purchase date.
Product_id product_update_date
0001 05-Dec-2005
0001 01-Dec-2005
0001 23-Nov-2005
0001 20-Nov-2005
Product_id purchase_date
00001 30_Nov-2005
I wish to link to the product purchase date to product edition of the 20-Nov-2005
It's actually a view I am creating.
Thanks all for your help so far
Eamon
-
Building on PW's statement I think this will work:
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
Inner Join
(
Select Product_Id, Max(updated_date) As MostRecentUpdate
From B
Where updated_date <= A.Purch_Date
Group By Product_Id
) dt Recent
On (dtRecent.MostRecentUpdate = B.Updated_date)
Ron
-
Oops, I totally missed that.
Alas, you can't reference the outer Table A inside a derived table, so you need to build a different derived table that introduces the purchase date, since you need the most recent profile update per purchase, per product:
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)
-
/*I assume from your post Product_id 00001 in the second table is a typo instead of 0001 and you need '23-Nov-2005' in the resultset instead if '20-Nov-2005'. If you really need '20-Nov-2005' what is the logic.*/
set nocount on
declare @Prod table (Product_id varchar(10), product_update_date datetime) profile_date
insert @Prod
select '0001', '05-Dec-2005' UNION
select '0001', '01-Dec-2005' UNION
select '0001', '23-Nov-2005' UNION
select '0001', '20-Nov-2005'
declare @purchase table(Product_id varchar(10), purchase_date datetime)
insert @purchase
select '0001', '30-Nov-2005'
select A.Product_id, max(A.product_update_date)
from
@Prod A
JOIN
@purchase B
ON
A.product_update_date <= B.purchase_date
GROUP BY A.Product_id
Regards,
gova -
Right, but what if you want other columns from B in the resultset ?
The original question was to join a product's purchase to its most recent "profile". Maybe I'm wrong in assuming that there is more to table "B" and the concept of "Profile" than just the updated_date ?
-
If we assume product_id and date are unique.
set nocount on
declare @Prod table (Product_id varchar(10), product_update_date datetime, haha varchar(10), hihi varchar(10))
insert @Prod
select '0001', '05-Dec-2005', 'aaa', 'bbb' UNION
select '0001', '01-Dec-2005', 'aaaa', 'bbbb' UNION
select '0001', '23-Nov-2005', 'aaaaa', 'bbbbb' UNION
select '0001', '20-Nov-2005', 'aaaaaa', 'bbbbbb'
declare @purchase table(Product_id varchar(10), purchase_date datetime)
insert @purchase
select '0001', '30-Nov-2005'
select main.*
from
@Prod main
join
(
select A.Product_id, max(A.product_update_date) product_update_date
from
@Prod A
JOIN
@purchase B
ON
A.product_update_date <= B.purchase_date
GROUP BY A.Product_id) pre
on
main.Product_id = pre.Product_id
and main.product_update_date = pre.product_update_date
Regards,
gova -
insert @purchase
select '0001', '30-Nov-2005'
OK, taking your test data, add another purchase of the same product:
insert @purchase
select '0001', '02-Dec-2005'
Resultset is only 1 record. Is this correct ? Only original poster knows I guess.
My assumption was resultset should be every sale combined with most recent profile as it existed at the time of the sale. Hence why my derived table also pulled purchase date for joining back to A to get every sale.
-
Original poster said
the join has to exist to, yes, the most recent, but yes, recent as per the purchase date. we don't want to reference an edition of the product after the purchase date.
so one result set is correct.
But as you said if one product_id has more than one purchase date then only original poster would know.
Regards,
gova
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply