Solved

Need to optimise SQL query

Posted on 2014-11-10
14
30 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
  • 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 47

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

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

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 47

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 47

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 47

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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

809 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