Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Get latest date from table join

Posted on 2013-11-24
10
Medium Priority
?
398 Views
Last Modified: 2013-11-24
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
0
Comment
Question by:zolf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39673812
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
 

Author Comment

by:zolf
ID: 39673828
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
 
LVL 11

Expert Comment

by:Louis01
ID: 39673829
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39673831
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39673833
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
 

Author Comment

by:zolf
ID: 39673840
I get this error when I run your second query

 No column name was specified for column 2 of 'X'.
0
 

Author Comment

by:zolf
ID: 39673842
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
 
LVL 11

Accepted Solution

by:
Louis01 earned 2000 total points
ID: 39673850
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
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39673856
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
 

Author Closing Comment

by:zolf
ID: 39673857
thanks it worked perfect
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question