T-SQL Query Syntax to get most recent effective record and min/max within

I have this table "Job"

Job      PartNum
1      ABC
2      DEF

and this table "Price"
PartNum      EffDate      BreakQty      Price
ABC      11/1/17      1      10.00
ABC      11/1/17      5      8.00
ABC      11/1/17      10      6.00
ABC      1/1/18      1      11.00
ABC      1/1/18      5      9.00
ABC      1/1/18      10      7.00
DEF      1/20/18      1      8.50
DEF      1/20/18      2      6.50
DEF      1/20/18      3      4.50
DEF      1/15/18      1      8.00
DEF      1/15/18      2      6.00
DEF      1/15/18      3      4.00


I need to create a query that will return:
Job, PartNum, EffectiveDate, MinPrice, MaxPrice

It needs to use:
the most recent price effective date
min price = the highest break quantity
max price = the lowst break quantity

The results would look like this:
1,ABC,1/1/18,7.00,11.00
2,DEF,1/20/18,4.50,8.50

Need help with the query syntax.
maverick0728Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this (I couldn't test it):
SELECT J.Job, P.PartNum, P.EffectiveDate, MIN(Price) MinPrice, MAX(Price) MaxPrice
FROM Job J
	INNER JOIN Price P ON J.PartNum = P.PartNum
WHERE P.EffectiveDate = (SELECT MAX(Date) 
			FROM Price P2
			WHERE P2.PartNum = P.PartNum)

Open in new window

0
Scott PletcherSenior DBACommented:
SELECT J.Job, P.PartNum, P.EffDate, P.MinPrice, P.MaxPrice
FROM (
    SELECT P2.PartNum, P2.EffDate, MIN(Price) AS MinPrice, MAX(Price) AS MaxPrice
    FROM (
        SELECT *, DENSE_RANK() OVER(PARTITION BY PartNum ORDER BY EffDate DESC) AS dense_rank
        FROM dbo.Price
    ) AS P2
    WHERE P2.dense_rank = 1
    GROUP BY P2.PartNum, P2.EffDate
) AS P
INNER JOIN dbo.Job J ON J.PartNum = P.PartNum
GROUP BY P.PartNum
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Oops, just noticed, as you've no doubt already decided yourself, but just to be very, the last "GROUP BY P.PartNum" is not needed.  It was left over from an earlier iteration of the code as I was developing it.
0
Garfield SamuelsProject ManagerCommented:
A simpler way yo do it is:
SELECT DISTINCT j.job, p.PartNum,
     m.EffDate,
     m.maxprice,
     m.minprice  
FROM job as j
Left join parts as p 
on j.PartNum = p.PartNum
left join 
(SELECT  PartNum, Max(EffDate) as EffDate, MAX(Price) as maxprice, MIN(Price) as minprice from parts group by PartNum)as M 
on p.PartNum = m.PartNum;

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

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.