Solved

Need to optimise SQL query

Posted on 2014-11-10
14
29 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 46

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
 

Author Comment

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

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 46

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 46

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 46

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 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how can i make refactoring for below code for making faster? 4 29
Get Duration of last Status Update 4 31
Need a starter for ETL protocol? 4 42
Sql query 107 29
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now