Solved

Need to optimise SQL query

Posted on 2014-11-10
14
33 Views
Last Modified: 2016-06-15
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

0
Comment
Question by:fjkilken
[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
  • 6
  • 6
14 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40432408
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
 

Author Comment

by:fjkilken
ID: 40432415
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40432501
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:fjkilken
ID: 40432504
that was just there for testing purposes - sorry for the confusion
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40432513
Ok. Then you can delete the where clause so will return the correct record.
0
 

Author Comment

by:fjkilken
ID: 40432570
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40432574
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40432592
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
 

Author Comment

by:fjkilken
ID: 40432599
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40432664
What my last query returns?
0
 

Author Comment

by:fjkilken
ID: 40432676
it only returned a single record, there should be several thousand records
0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40432879
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
 

Author Comment

by:fjkilken
ID: 40433390
ok - thanks - let me check it again and advise
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

752 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