Joining two tables by the latest date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Joining two tables by the latest date

Author

Topic

micmic
Starting Member

7 Posts

Posted - 2009-04-23 : 04:47:58

Hi

I have these two set of tables. One stores the productID and description, another stores the history of the product.
The result that i want to achieve is to get the most updated price of each product, base on the VersionDate of the product.
Any idea on how can i achieve this?

Table: Product

ProductID | ProductDesc
1 | Apple
2 | Orange
3 | Mango

Table: PriceHistory

ProductID| VersionDate | Price
1 | 1 Jan 2009 |$1.20
1 | 22 Jan 2009 |$1.80
1 | 10 Mar 2009 |$1.40
2 | 1 Feb 2009 |$2.10
2 | 7 Apr 2000 |$1.50
3 | 3 Mar 2009 |$1.00

Result:

ProductID | ProductDesc | VersionDate | Price
1 | Apple | 10 Mar 2009 | $1.40
2 | Orange | 7 Apr 2009 | $1.50
3 | Mango | 3 Mar 2009 | $1.00

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 05:08:47

[code]DECLARE @Product TABLE
(
ProductID INT,
ProductDesc VARCHAR(20)
)

INSERT @Product
SELECT 1, 'Apple' UNION ALL
SELECT 2, 'Orange' UNION ALL
SELECT 3, 'Mango'

DECLARE @PriceHistory TABLE
(
ProductID INT,
VersionDate DATETIME,
Price MONEY
)

INSERT @PriceHistory
SELECT 1, '1 Jan 2009', $1.20 UNION ALL
SELECT 1, '22 Jan 2009', $1.80 UNION ALL
SELECT 1, '10 Mar 2009', $1.40 UNION ALL
SELECT 2, '1 Feb 2009', $2.10 UNION ALL
SELECT 2, '7 Apr 2000', $1.50 UNION ALL
SELECT 3, '3 Mar 2009', $1.00

SELECT p.ProductID,
p.ProductDesc,
ph.VersionDate,
ph.Price
FROM (
SELECT ProductID,
VersionDate,
Price,
ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY VersionDate DESC) AS RecID
FROM @PriceHistory
) AS ph
INNER JOIN @Product AS p ON p.ProductID = ph.ProductID
WHERE ph.recID = 1[/code]

E 12°55'05.63"
N 56°04'39.26"

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-04-23 : 05:09:29

Have a look at this:

SELECT p.ProductID,p.ProductDesc,ph.VersionDate,ph.Price
FROM Product p
INNER JOIN PriceHistory ph ON p.ProductID=ph.ProductID 
WHERE ph.VersionDate = (SELECT max(VersionDate) FROM PriceHistory phi WHERE phi.ProductID=ph.ProductID)

Its a bit messy and i'd be open to any better suggestions but i've pulled Product ID and Product Desc from the Products table. Then I've joined to the PriceHistory table on Product ID. Then, in order to only get the latest version, I've filtered the results so the version date is equal to the MAX date (i.e. the highest date for each product ID) This is an embedded subquery and its not great performance-wise, but it does the job.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-04-23 : 05:12:39

I win the 'using less code but getting the same results' contest there I think.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

micmic
Starting Member

7 Posts

Posted - 2009-04-23 : 05:29:10

Thanks for the replies.

I have came out with 1 solution too. Please advise ...

SELECT DISTINCT prd.ProductID, prd.ProductDesc, hist.VersionDate, hist.Price
FROM Product AS prd INNER JOIN ProductHistory AS hist
ON prd.ProductID = hist.ProductID
WHERE hist.VersionDate IN
(
SELECT MAX(VersionDate) FROM ProductHistory GROUP BY ProductID
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 05:36:19

It's good to see you tried for yourself too.
But... There is the downside with your attempt.

If ProductID 1 has two versiondates, 1 jan 2009 and 2 jan 2009, while ProductID 2 only has 1 jan 2009, you will get two results from ProductID since the versiondates for this product satisfies both dates.
See this example

-- Prepare sample data
DECLARE	@Product TABLE
	(
		ProductID INT,
		ProductDesc VARCHAR(20)
	)

INSERT	@Product
SELECT	1, 'Apple' UNION ALL
SELECT	2, 'Orange' UNION ALL
SELECT	3, 'Mango'

DECLARE	@PriceHistory TABLE
	(
		ProductID INT,
		VersionDate DATETIME,
		Price MONEY
	)

INSERT	@PriceHistory
SELECT	1, '1 Jan 2009', $1.20 UNION ALL
SELECT	1, '2 Jan 2009', $1.80 UNION ALL
SELECT	2, '1 Jan 2009', $1.50

-- Peso
SELECT		p.ProductID,
		p.ProductDesc,
		ph.VersionDate,
		ph.Price
FROM		(
			SELECT	ProductID,
				VersionDate,
				Price,
				ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY VersionDate DESC) AS RecID
			FROM	@PriceHistory
		) AS ph
INNER JOIN	@Product AS p ON p.ProductID = ph.ProductID
WHERE		ph.recID = 1

-- Micmic
SELECT DISTINCT	prd.ProductID,
		prd.ProductDesc,
		hist.VersionDate,
		hist.Price
FROM		@Product AS prd
INNER JOIN	@PriceHistory AS hist ON prd.ProductID = hist.ProductID
WHERE		hist.VersionDate IN ( SELECT MAX(VersionDate) FROM @PriceHistory GROUP BY ProductID)

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-23 : 05:41:21

hi micmic,
u can use the above solution then u will get the desired solution. use exists than in if u have large data exists will have good performance than in ..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 05:44:55

Add this code snippet to above sample data and try

-- bklr
SELECT DISTINCT	prd.ProductID,
		prd.ProductDesc,
		hist.VersionDate,
		hist.Price
FROM		@Product AS prd
INNER JOIN	@PriceHistory AS hist ON prd.ProductID = hist.ProductID
WHERE		EXISTS (SELECT * FROM @PriceHistory GROUP BY ProductID HAVING MAX(VersionDate) = hist.VersionDate)

First of all the code must return the correct data. Then we can begin argumenting about performance.
But that's just my simple mind...

E 12°55'05.63"
N 56°04'39.26"

micmic
Starting Member

7 Posts

Posted - 2009-04-23 : 05:50:58

Thanks for all the replies.

Well I'll tried to look into [OVER] then. Still trying to understand the usage of [OVER]. ^^

Cheers
micmic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 05:53:00

The OVER part together with PARTITION is called a windowed function.
It slices the complete resultset into smaller horizontal areas. Which areas is depending in the PARTITION (slice) part.

Have a look at Books Online for more information.

E 12°55'05.63"
N 56°04'39.26"

micmic
Starting Member

7 Posts

Posted - 2009-04-23 : 06:16:54

Yeah have figured OVER clause and successfully applied into my project. Thanks alot!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 06:19:14

For other uses, see
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

E 12°55'05.63"
N 56°04'39.26"

micmic
Starting Member

7 Posts

Posted - 2009-04-23 : 06:39:43

I've another problem over here. Based on the same result, I need to filter the result on conditional WHERE clause.

For example, I can filter the result by the following fields: ProductID, ProductDesc, Price, VersionDate.

if @ProductID != empty
select fields where ProductID LIKE '%' + @ProductID + '%'
if @ProductDesc != empty
AND ProductDesc LIKE '%' + @ProductDesc + '%'
if @ProductPrice != empty etc ...

How can I achieve this?

Table: Product

ProductID | ProductDesc
1 | Apple
2 | Orange
3 | Mango

Table: PriceHistory

ProductID| VersionDate | Price
1 | 1 Jan 2009 |$1.20
1 | 22 Jan 2009 |$1.80
1 | 10 Mar 2009 |$1.40
2 | 1 Feb 2009 |$2.10
2 | 7 Apr 2000 |$1.50
3 | 3 Mar 2009 |$1.00

Result:

ProductID | ProductDesc | VersionDate | Price
1 | Apple | 10 Mar 2009 | $1.40
2 | Orange | 7 Apr 2009 | $1.50
3 | Mango | 3 Mar 2009 | $1.00

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 06:53:42

[code]-- Prepare sample data
DECLARE @Product TABLE
(
ProductID INT,
ProductDesc VARCHAR(20)
)

INSERT @Product
SELECT 1, 'Apple' UNION ALL
SELECT 2, 'Orange' UNION ALL
SELECT 3, 'Mango'

DECLARE @PriceHistory TABLE
(
ProductID INT,
VersionDate DATETIME,
Price MONEY
)

INSERT @PriceHistory
SELECT 1, '1 Jan 2009', $1.20 UNION ALL
SELECT 1, '2 Jan 2009', $1.80 UNION ALL
SELECT 2, '1 Jan 2009', $1.50

-- Peso
SELECT ProductID,
ProductDesc,
VersionDate,
Price
FROM (
SELECT p.ProductID,
p.ProductDesc,
ph.VersionDate,
ph.Price,
ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY VersionDate DESC) AS RecID
FROM @PriceHistory AS ph
INNER JOIN @Product AS p ON p.ProductID = ph.ProductID
WHERE (@ProductID IS NULL OR p.ProductID LIKE '%' + @ProductID + '%')
AND (@ProductDesc IS NULL OR p.ProductDesc LIKE '%' + @ProductDesc + '%')
AND (@Price IS NULL OR @Price = p.Price)
) AS ph
WHERE recID = 1[/code]

E 12°55'05.63"
N 56°04'39.26"

micmic
Starting Member

7 Posts

Posted - 2009-04-23 : 22:34:31

Hmm .. but this doesn't work. The scenario is, I have this advanced search for the user to enter search keywords to the fields.
For example user can search by:

Product ID: _______
Product Desc: ________
Product Price: _________

Back end I will not know what is the user input. So in my stored prod i need to check if the params is empty
Let's say the input is
@productID = ''
@productdesc = 'e'
@productprice = '1'

i should do something like this
select * from tbl where productdesc like '%'+@productdesc+'%' AND productprice like '%'+@productprice+'%'
result:
1 | Apple | 10 Mar 2009 | $1.40
2 | Orange | 7 Apr 2009 | $1.50

but the problem is how can i check if the param is empty, i will not filter that field?

micmic
Starting Member

7 Posts

Posted - 2009-04-23 : 22:55:26

Alright I have did some modification to the sql and it works! Please advise if i'm wrong.

SELECT p.ProductID,
p.ProductDesc,
ph.VersionDate,
ph.Price
FROM (
SELECT ProductID,
VersionDate,
Price,
ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY VersionDate DESC) AS RecID
FROM @PriceHistory
) AS ph
INNER JOIN @Product AS p ON p.ProductID = ph.ProductID
WHERE ph.recID = 1
AND ProductID LIKE ISNULL('%' + @ProductID + '%', ProductID)
AND ProductDesc LIKE ISNULL('%' + @ProductDesc + '%', ProductDesc )
AND Price LIKE ISNULL('%' + @Price + '%', Price )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources