Zolf
asked on
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
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
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
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
ASKER
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)
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)
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
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
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
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
ASKER
I get this error when I run your second query
No column name was specified for column 2 of 'X'.
No column name was specified for column 2 of 'X'.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
thanks it worked perfect
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)