Get latest date from table join

Hello there,

I have this query which gets back the records of products for a particular supplier. but now I want to filter the records to the latest purchase price date. see below my returned data.i am using mssql 2008 db. it should return id 6 and 8

cheers
Zolf

select
  pp.id,
  pp.priceDate,
  pp.purchasePrice,
  pp.salesPrice,
  pp.consumerPrice,
  pp.productID,
  pp.description
 from
  productPricing pp cross 
 join
  product p 
 where
  pp.productID=p.id 
  and p.supplierID=3 

Open in new window


id      priceDate              purchasePrice      salesPrice          consumerPrice      productID
4       2013-10-23 00:00:00      3000      3200             3400                               3
5       2013-11-20 00:00:00      3600      3700             3800                               3
6       2013-11-23 00:00:00      4000      4200             4500                               3
7       2012-11-21 00:00:00      500      600             800                               4
8       2013-06-30 00:00:00      950      1050             1200                               4
zolfAsked:
Who is Participating?
 
Louis01Connect With a Mentor Commented:
My apologies pp. should be pp1.
select pp1.id, pp1.priceDate, pp1.purchasePrice, pp1.salesPrice, pp1.consumerPrice, p.productID, pp1.description
  from product p
        inner join productPricing pp1 
    on pp1.productID = p.ProductID
 where p.supplierID=3 
   and exists (select pp2.ProductID, MAX(pp2.priceDate) as max_priceDate 
                 from productPricing pp2 
                where pp2.productID = pp1.productID 
                group by pp2.ProductID 
               having MAX(pp2.priceDate) = pp1.priceDate)

Open in new window

0
 
Pratima PharandeCommented:
try this

select
  pp.id,
  pp.priceDate,
  pp.purchasePrice,
  pp.salesPrice,
  pp.consumerPrice,
  pp.productID,
  pp.description
 from
  productPricing pp inner
 join
  product p
 where
  pp.productID=p.id
  and p.supplierID=3
and  pp.priceDate in (

Select Max(p1.priceDate) from productPricing p1
where pp.productID = p1.id
group by p1.productID)
0
 
zolfAuthor Commented:
thanks for your feedback but no record is returned.....there was a mistake in your query(I replaced the where to ON near the inner join)
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Louis01Commented:
select pp.id, pp.priceDate, pp.purchasePrice, pp.salesPrice, pp.consumerPrice, p.productID, pp.description
  from product p
        inner join productPricing pp1 
    on pp1.productID = p.ProductID
 where p.supplierID=3 
   and exists (select pp2.ProductID, MAX(pp2.priceDate) as max_priceDate 
                 from productPricing pp2 
                where pp2.productID = pp1.productID 
                group by pp2.ProductID 
               having MAX(pp2.priceDate) = pp1.priceDate)

Open in new window

0
 
Pratima PharandeCommented:
try only this

Select p1.productID ,Max(p1.priceDate) from productPricing p1
group by p1.productID

this will return you two records you want
then you need to join this table with your query
This needs to work.
Let me know if this query gives you appropriate result
0
 
Pratima PharandeCommented:
select
  pp.id,
  pp.priceDate,
  pp.purchasePrice,
  pp.salesPrice,
  pp.consumerPrice,
  pp.productID,
  pp.description
 from
  productPricing pp cross
 join
  product p  
inner join
(
Select p1.productID ,Max(p1.priceDate) from productPricing p1
group by p1.productID

) X on X.productID = pp.productID
 where
  pp.productID=p.id
  and p.supplierID=3
0
 
zolfAuthor Commented:
I get this error when I run your second query

 No column name was specified for column 2 of 'X'.
0
 
zolfAuthor Commented:
Louis01

I get error when I run your query

Invalid column name 'ProductID'. 2) [Error Code: 4104, SQL State: S1000]  The multi-part identifier "pp.id" could not be bound. 3) [Error Code: 4104, SQL State: S1000]  The multi-part identifier "pp.priceDate" could not be bound. 4) [Error Code: 4104, SQL State: S1000]  The multi-part identifier "pp.purchasePrice" could not be bound. 5) [Error Code: 4104, SQL State: S1000]  The multi-part identifier "pp.salesPrice" could not be bound. 6) [Error Code: 4104, SQL State: S1000]  The multi-part identifier "pp.consumerPrice" could not be bound. 7) [Error Code: 207, SQL State: 42S22]  Invalid column name 'productID'. 8) [Error Code: 4104, SQL State: S1000]  The multi-part identifier "pp.description" could not be bound.
0
 
QuinnDexCommented:
select
  pp.id,
  pp.priceDate,
  pp.purchasePrice,
  pp.salesPrice,
  pp.consumerPrice,
  pp.productID,
  pp.description
 from
  productPricing pp cross
 join
  product p  
inner join
(
Select p1.productID ,Max(p1.priceDate) from productPricing p1
group by p1.productID

) productPricing X on X.productID = pp.productID
 where
  pp.productID=p.id
  and p.supplierID=3
0
 
zolfAuthor Commented:
thanks it worked perfect
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.