Need to optimise SQL query

I have created a query which pulls a part Cost for the record where the vld_to_dtm is the MAX value AND  chg_dtm is the MAX value

Ie; find the record where vld_to_dtm is the MAX value, and for that value, find the MAX value of chg_dtm.
Then for these two values, extract the Cost.

The query is below but I'm wondering if there's a more basic/simplistic way to write the query (and maybe a more efficient way)?
Thanks
Fergal

SELECT  
RIGHT(recs.mm_nbr,6) AS mm_nbr,
recs.un_prc AS [Price],
max_chg_dtm,
max_vld_to_dtm
FROM mytable recs
INNER JOIN 
(
		SELECT 
		RIGHT(chg_dtm.mm_nbr,6) AS mm_nbr,
		MAX(chg_dtm.chg_dtm) AS max_chg_dtm, 
		vld_dtm.max_vld_to_dtm
		FROM mytable chg_dtm
			INNER JOIN 
			(
				SELECT RIGHT(mm_nbr,6) AS mm_nbr, MAX(vld_to_dtm) max_vld_to_dtm
				FROM mytable
				WHERE sls_org = 'AM01' AND dist_chnl = 02
				GROUP BY right(mm_nbr,6)
			) vld_dtm	
			ON	vld_dtm.mm_nbr  = RIGHT(chg_dtm.mm_nbr,6) AND
				chg_dtm.vld_to_dtm = vld_dtm.max_vld_to_dtm
		WHERE sls_org = 'AM01' AND dist_chnl = 02
		GROUP BY right(chg_dtm.mm_nbr,6), vld_dtm.max_vld_to_dtm
) maxdates
ON  maxdates.mm_nbr = RIGHT(recs.mm_nbr,6)
AND maxdates.max_chg_dtm = recs.chg_dtm
AND maxdates.max_vld_to_dtm = recs.vld_to_dtm
WHERE sls_org = 'AM01' AND dist_chnl = 02  AND RIGHT(recs.mm_nbr,6) = '908066'

Open in new window

fjkilkenAsked:
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.

Haris DulicCommented:
Try this:

SELECT  
RIGHT(recs.mm_nbr,6) AS mm_nbr,
recs.un_prc AS [Price],
maxdates.max_chg_dtm,
maxdates.max_vld_to_dtm
FROM mytable recs
INNER JOIN 
(
		SELECT 
		RIGHT(mm_nbr,6) AS mm_nbr, MAX(chg_dtm) AS max_chg_dtm, MAX(vld_to_dtm) max_vld_to_dtm 
		FROM mytable 
		WHERE sls_org = 'AM01' AND dist_chnl = 02
		GROUP BY right(mm_nbr,6)
) maxdates
ON  maxdates.mm_nbr = RIGHT(recs.mm_nbr,6)
AND maxdates.max_chg_dtm = recs.chg_dtm
AND maxdates.max_vld_to_dtm = recs.vld_to_dtm
WHERE sls_org = 'AM01' AND dist_chnl = 02  AND RIGHT(recs.mm_nbr,6) = '908066'

Open in new window

0
fjkilkenAuthor Commented:
Afraid that does not work - I had tried something similar myself previously, the two MAX() conditions don't work as you may expect, they operate individually on each date column
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this one:
WITH MAX_CTE (max_chg_dtm, max_vld_to_dtm)
AS
(SELECT MAX(chg_dtm.chg_dtm), MAX(vld_to_dtm)
FROM mytable
WHERE sls_org = 'AM01' AND dist_chnl = 02)


SELECT  RIGHT(recs.mm_nbr,6) AS mm_nbr, recs.un_prc AS [Price], recs.chg_dtm, recs.vld_to_dtm
FROM mytable recs
INNER JOIN MAX_CTE
	ON  MAX_CTE.max_chg_dtm = recs.chg_dtm
	AND MAX_CTE.max_vld_to_dtm = recs.vld_to_dtm
WHERE RIGHT(recs.mm_nbr,6) = '908066'

Open in new window

I just don't understand why you need the criteria WHERE RIGHT(recs.mm_nbr,6) = '908066'. That means that you already know which record to be returned.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

fjkilkenAuthor Commented:
that was just there for testing purposes - sorry for the confusion
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. Then you can delete the where clause so will return the correct record.
0
fjkilkenAuthor Commented:
afraid that does not work due to the code section: "SELECT MAX(chg_dtm), MAX(vld_to_dtm)"
all this is doing is taking the MAX value of each date, without any consideration of the "mm_nbr" field, and also both these MAX values need to be in the same record
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
It's returning the MAX values for the criteria sls_org = 'AM01' AND dist_chnl = 02.
Can you post an example of data and which records should be returned by your example? It will be more easy to work with some data.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Without data samples I can only guess. So here's another try:
WITH MAX_CTE (max_chg_dtm, max_vld_to_dtm)
AS
(SELECT MAX(chg_dtm.chg_dtm), vld_to_dtm
FROM mytable
WHERE sls_org = 'AM01' AND dist_chnl = 02 
	AND vld_to_dtm = (SELECT MAX(vld_to_dtm) FROM mytable)
GROUP BY vld_to_dtm)

SELECT RIGHT(recs.mm_nbr,6) AS mm_nbr, recs.un_prc AS [Price], recs.chg_dtm, recs.vld_to_dtm
FROM mytable recs
INNER JOIN MAX_CTE
	ON  MAX_CTE.max_chg_dtm = recs.chg_dtm
	AND MAX_CTE.max_vld_to_dtm = recs.vld_to_dtm

Open in new window

0
fjkilkenAuthor Commented:
here's the 'raw' recordset:
mm_nbr      Price      chg_dtm      vld_to_dtm
908066      145.47      2011-12-02 16:43:24.087      9999-12-31 00:00:00.000
908066      142.56      2012-02-04 22:09:15.393      9999-12-31 00:00:00.000
908066      139.05      2012-04-29 22:13:43.267      9999-12-31 00:00:00.000
908066      116.29      2012-08-04 22:11:32.323      9999-12-31 00:00:00.000
908066      96.94      2012-11-04 22:14:27.590      9999-12-31 00:00:00.000
908066      95.98      2013-05-05 22:14:07.700      9999-12-31 00:00:00.000
908066      97.96      2013-11-02 22:12:01.937      9999-12-31 00:00:00.000
908066      95.02      2013-11-30 22:11:09.440      9999-12-31 00:00:00.000
908066      97.96      2014-02-01 22:19:38.197      9999-12-31 00:00:00.000
908066      94.51      2014-02-15 22:10:34.173      9999-12-31 00:00:00.000
908066      93.56      2014-05-03 22:10:48.040      9999-12-31 00:00:00.000
908066      92.12      2014-08-30 22:15:21.757      9999-12-31 00:00:00.000
908066      93.56      2014-08-30 22:15:22.433      2014-08-30 00:00:00.000
908066      94.51      2014-05-03 22:10:48.390      2014-05-03 00:00:00.000
908066      97.96      2014-02-15 22:10:33.190      2014-02-15 00:00:00.000
908066      95.02      2014-02-01 22:19:39.647      2014-01-30 00:00:00.000
908066      97.96      2013-11-30 22:11:09.130      2013-11-30 00:00:00.000
908066      95.98      2013-11-02 22:12:03.290      2013-11-02 00:00:00.000
908066      96.94      2013-05-05 22:14:06.997      2013-05-04 00:00:00.000
908066      116.29      2012-11-04 22:14:27.957      2012-11-03 00:00:00.000
908066      139.05      2012-08-04 22:11:32.630      2012-08-04 00:00:00.000
908066      142.56      2012-04-29 22:13:43.657      2012-04-28 00:00:00.000
908066      145.47      2012-02-04 22:09:14.913      2012-02-04 00:00:00.000
908066      146.94      2011-12-02 16:43:24.087      2011-11-26 00:00:00.000
908066      149.93      2011-12-02 16:43:24.087      2011-07-30 00:00:00.000
908066      152.99      2011-12-02 16:43:24.087      2011-04-30 00:00:00.000
908066      154.53      2011-12-02 16:43:24.087      2010-09-25 00:00:00.000

here's the correct returned recordset:
mm_nbr      Price      max_chg_dtm      max_vld_to_dtm
908066      92.12      2014-08-30 22:15:21.757      9999-12-31 00:00:00.000
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
What my last query returns?
0
fjkilkenAuthor Commented:
it only returned a single record, there should be several thousand records
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I used your data sample and run my query against it and returns the expected record:
mm_nbr      Price      chg_dtm      vld_to_dtm
908066      92.12      2014-08-30 22:15:21.757      9999-12-31 00:00:00.000

Can't see what more records are you waiting for.
Don't forget to run without the WHERE clause:
WITH MAX_CTE (max_chg_dtm, max_vld_to_dtm)
AS
(SELECT MAX(chg_dtm), vld_to_dtm
FROM mytable
WHERE sls_org = 'AM01' AND dist_chnl = 02 
	AND vld_to_dtm = (SELECT MAX(vld_to_dtm) FROM mytable)
GROUP BY vld_to_dtm)

SELECT RIGHT(recs.mm_nbr,6) AS mm_nbr, recs.price AS [Price], recs.chg_dtm, recs.vld_to_dtm
FROM mytable recs
INNER JOIN MAX_CTE
	ON  MAX_CTE.max_chg_dtm = recs.chg_dtm
	AND MAX_CTE.max_vld_to_dtm = recs.vld_to_dtm

Open in new window

0

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
fjkilkenAuthor Commented:
ok - thanks - let me check it again and advise
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
Microsoft SQL Server

From novice to tech pro — start learning today.