Solved

Need to optimise SQL query

Posted on 2014-11-10
14
26 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 45

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 45

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 45

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 45

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 45

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 45

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

747 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

13 Experts available now in Live!

Get 1:1 Help Now