Max Date Distinct Cost - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Max Date Distinct Cost

Author

Topic

chapo
Starting Member

39 Posts

Posted - 2009-05-29 : 15:13:24

I have a view

SELECT TOP (100) PERCENT Pricing.dbo.ftpofile_base.TYPE, Pricing.dbo.ftpofile_base.DIM, Pricing.dbo.ftpofile_base.METRIC,
Pricing.dbo.ftpofile_base.COSTDESIG, Pricing.dbo.ftpofile_base.GRADENAME,
MAX(DISTINCT Pricing.dbo.ftpomast_base.DATECREATE) AS DATECREATE
FROM Pricing.dbo.ftpomast_base INNER JOIN
Pricing.dbo.ftpofile_base ON Pricing.dbo.ftpomast_base.PONUMBER = Pricing.dbo.ftpofile_base.PONUMBER
GROUP BY Pricing.dbo.ftpofile_base.TYPE, Pricing.dbo.ftpofile_base.DIM, Pricing.dbo.ftpofile_base.METRIC,
Pricing.dbo.ftpofile_base.COSTDESIG, Pricing.dbo.ftpofile_base.GRADENAME
HAVING (Pricing.dbo.ftpofile_base.METRIC = '250x22')
ORDER BY MAX(DISTINCT Pricing.dbo.ftpomast_base.DATECREATE) DESC

if I leave it as is I only get the number of records I want problem is I need to know the latest pricing we pay as soon as I add the Cost fiel all of a suddent I get a bunch of extra records because it's giving me all the distinct pricing for the max dates. All I need is to give me the pricing we pay for the latest date for any given Type,Dim,Gradename,Costdesig

Any help would be greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 15:15:55

1. Why are you having DISTINCT in the MAX clause? It will only make the query scan the table twice
2. Put the HAVING part into a WHERE clause instead

E 12°55'05.63"
N 56°04'39.26"

chapo
Starting Member

39 Posts

Posted - 2009-05-29 : 15:22:59

quote:
Originally posted by Peso

1. Why are you having DISTINCT in the MAX clause? It will only make the query scan the table twice
2. Put the HAVING part into a WHERE clause instead

E 12°55'05.63"
N 56°04'39.26"

Thanks for the tip of eliminating the double scanning could you please give me a sample for putting the HAVING part into a WHERE clause please.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 15:25:24

~~HAVING (Pricing.dbo.ftpofile_base.METRIC = '250x22')~~
WHERE Pricing.dbo.ftpofile_base.METRIC = '250x22'

WHERE is applied before aggregation and grouping, HAVING is applied after aggregation and grouping.

E 12°55'05.63"
N 56°04'39.26"

chapo
Starting Member

39 Posts

Posted - 2009-05-29 : 15:37:26

quote:
Originally posted by Peso

~~HAVING (Pricing.dbo.ftpofile_base.METRIC = '250x22')~~
WHERE Pricing.dbo.ftpofile_base.METRIC = '250x22'

WHERE is applied before aggregation and grouping, HAVING is applied after aggregation and grouping.

E 12°55'05.63"
N 56°04'39.26"

Still not there Peso the 1st query gives me 5 records as it should but as soon as I add the cost i get 341 records I still only want to see the 5 records with there corresponding price. The only reasond I have the HAVING (Pricing.dbo.ftpofile_base.METRIC = '250x22' is because i'm only showing one metric size to see if it works eventually I won't be doing any filtering of any metric size.

here is what the view looks when I add the cost field

SELECT TOP (100) PERCENT Pricing.dbo.ftpofile_base.TYPE, Pricing.dbo.ftpofile_base.DIM, Pricing.dbo.ftpofile_base.METRIC,
Pricing.dbo.ftpofile_base.COSTDESIG, Pricing.dbo.ftpofile_base.GRADENAME, MAX(Pricing.dbo.ftpomast_base.DATECREATE)
AS DATECREATE, Pricing.dbo.ftpofile_base.COST
FROM Pricing.dbo.ftpomast_base INNER JOIN
Pricing.dbo.ftpofile_base ON Pricing.dbo.ftpomast_base.PONUMBER = Pricing.dbo.ftpofile_base.PONUMBER
GROUP BY Pricing.dbo.ftpofile_base.TYPE, Pricing.dbo.ftpofile_base.DIM, Pricing.dbo.ftpofile_base.METRIC,
Pricing.dbo.ftpofile_base.COSTDESIG, Pricing.dbo.ftpofile_base.GRADENAME, Pricing.dbo.ftpofile_base.COST
HAVING (Pricing.dbo.ftpofile_base.METRIC = '250x22')
ORDER BY MAX(Pricing.dbo.ftpomast_base.DATECREATE) DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 15:44:18

Aggregate the cost too?

SELECT 		TOP (100) PERCENT
		Pricing.dbo.ftpofile_base.TYPE,
		Pricing.dbo.ftpofile_base.DIM,
		Pricing.dbo.ftpofile_base.METRIC, 
		Pricing.dbo.ftpofile_base.COSTDESIG,
		Pricing.dbo.ftpofile_base.GRADENAME,
		MAX(Pricing.dbo.ftpomast_base.DATECREATE) AS DATECREATE,
		MIN(Pricing.dbo.ftpofile_base.COST) AS Cost 
FROM 		Pricing.dbo.ftpomast_base
INNER JOIN	Pricing.dbo.ftpofile_base ON Pricing.dbo.ftpomast_base.PONUMBER = Pricing.dbo.ftpofile_base.PONUMBER
WHERE		Pricing.dbo.ftpofile_base.METRIC = '250x22'
GROUP BY 	Pricing.dbo.ftpofile_base.TYPE,
		Pricing.dbo.ftpofile_base.DIM,
		Pricing.dbo.ftpofile_base.METRIC, 
		Pricing.dbo.ftpofile_base.COSTDESIG,
		Pricing.dbo.ftpofile_base.GRADENAME
ORDER BY 	MAX(Pricing.dbo.ftpomast_base.DATECREATE) DESC

E 12°55'05.63"
N 56°04'39.26"

chapo
Starting Member

39 Posts

Posted - 2009-05-29 : 15:51:53

quote:

Originally posted by Peso

Aggregate the cost too?

SELECT 		TOP (100) PERCENT
		Pricing.dbo.ftpofile_base.TYPE,
		Pricing.dbo.ftpofile_base.DIM,
		Pricing.dbo.ftpofile_base.METRIC, 
		Pricing.dbo.ftpofile_base.COSTDESIG,
		Pricing.dbo.ftpofile_base.GRADENAME,
		MAX(Pricing.dbo.ftpomast_base.DATECREATE) AS DATECREATE,
		MIN(Pricing.dbo.ftpofile_base.COST) AS Cost 
FROM 		Pricing.dbo.ftpomast_base
INNER JOIN	Pricing.dbo.ftpofile_base ON Pricing.dbo.ftpomast_base.PONUMBER = Pricing.dbo.ftpofile_base.PONUMBER
WHERE		Pricing.dbo.ftpofile_base.METRIC = '250x22'
GROUP BY 	Pricing.dbo.ftpofile_base.TYPE,
		Pricing.dbo.ftpofile_base.DIM,
		Pricing.dbo.ftpofile_base.METRIC, 
		Pricing.dbo.ftpofile_base.COSTDESIG,
		Pricing.dbo.ftpofile_base.GRADENAME
ORDER BY 	MAX(Pricing.dbo.ftpomast_base.DATECREATE) DESC

E 12°55'05.63"
N 56°04'39.26"

Thanks but still don't get the correct Cost data I need since it give me the min cost I need the actual cost value that belongs to that particular Max date.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 16:03:25

Now your question finally makes sense!

SELECT 		TYPE,
		DIM,
		METRIC, 
		COSTDESIG,
		GRADENAME,
		DATECREATE,
		COST
FROM		(
			SELECT 		p.TYPE,
					p.DIM,
					p.METRIC, 
					p.COSTDESIG,
					p.GRADENAME,
					m.DATECREATE,
					p.COST,
					ROW_NUMBER() OVER (PARTITION BY m.PONUMBER ORDER BY m.DATECREATE DESC) AS recID
			FROM 		Pricing.dbo.ftpomast_base AS m
			INNER JOIN	Pricing.dbo.ftpofile_base AS p ON p.PONUMBER = m.PONUMBER
			WHERE		p.METRIC = '250x22'
		) AS d
WHERE		recID = 1
ORDER BY	DATECREATE DESC

E 12°55'05.63"
N 56°04'39.26"

chapo
Starting Member

39 Posts

Posted - 2009-05-29 : 16:18:06

quote:

Originally posted by Peso

Now your question finally makes sense!

SELECT 		TYPE,
		DIM,
		METRIC, 
		COSTDESIG,
		GRADENAME,
		DATECREATE,
		COST
FROM		(
			SELECT 		p.TYPE,
					p.DIM,
					p.METRIC, 
					p.COSTDESIG,
					p.GRADENAME,
					m.DATECREATE,
					p.COST,
					ROW_NUMBER() OVER (PARTITION BY m.PONUMBER ORDER BY m.DATECREATE DESC) AS recID
			FROM 		Pricing.dbo.ftpomast_base AS m
			INNER JOIN	Pricing.dbo.ftpofile_base AS p ON p.PONUMBER = m.PONUMBER
			WHERE		p.METRIC = '250x22'
		) AS d
WHERE		recID = 1
ORDER BY	DATECREATE DESC

E 12°55'05.63"
N 56°04'39.26"

I'm getting the following error message
The OVER SQL construct or statement is not supported.
I'm using SQL 2005

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 16:19:15

Change your compatibility level to 90 or higher

E 12°55'05.63"
N 56°04'39.26"

chapo
Starting Member

39 Posts

Posted - 2009-05-29 : 16:24:06

quote:
Originally posted by Peso

Change your compatibility level to 90 or higher

E 12°55'05.63"
N 56°04'39.26"

Unfortunately this particular database compatibility level is 70

and that is something we can't change is there any other way you know of doing the view and get the same result as with the over command.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 16:31:59

I sincerely hope this is the last surprise and untold/unwritten piece of information?

SELECT 		d.TYPE,
		d.DIM,
		d.METRIC, 
		d.COSTDESIG,
		d.GRADENAME,
		d.DATECREATE,
		d.COST
FROM		(
			SELECT 		m.PONUMBER,
					p.TYPE,
					p.DIM,
					p.METRIC, 
					p.COSTDESIG,
					p.GRADENAME,
					m.DATECREATE,
					p.COST
			FROM 		Pricing.dbo.ftpomast_base AS m
			INNER JOIN	Pricing.dbo.ftpofile_base AS p ON p.PONUMBER = m.PONUMBER
			WHERE		p.METRIC = '250x22'
		) AS d
INNER JOIN	(
			SELECT		PONUMBER,
					MAX(DATECREATE) AS dc
			FROM		Pricing.dbo.ftpomast_base
			GROUP BY	PONUMBER
		) AS x ON x.PONUMBER = d.PONUMBER  
WHERE		d.DATECREATE = x.dc
ORDER BY	d.DATECREATE DESC

E 12°55'05.63"
N 56°04'39.26"

chapo
Starting Member

39 Posts

Posted - 2009-05-29 : 16:36:34

quote:

Originally posted by Peso

I sincerely hope this is the last surprise and untold/unwritten piece of information?

SELECT 		d.TYPE,
		d.DIM,
		d.METRIC, 
		d.COSTDESIG,
		d.GRADENAME,
		d.DATECREATE,
		d.COST
FROM		(
			SELECT 		m.PONUMBER,
					p.TYPE,
					p.DIM,
					p.METRIC, 
					p.COSTDESIG,
					p.GRADENAME,
					m.DATECREATE,
					p.COST
			FROM 		Pricing.dbo.ftpomast_base AS m
			INNER JOIN	Pricing.dbo.ftpofile_base AS p ON p.PONUMBER = m.PONUMBER
			WHERE		p.METRIC = '250x22'
		) AS d
INNER JOIN	(
			SELECT		PONUMBER,
					MAX(DATECREATE) AS dc
			FROM		Pricing.dbo.ftpomast_base
			GROUP BY	PONUMBER
		) AS x ON x.PONUMBER = d.PONUMBER  
WHERE		d.DATECREATE = x.dc
ORDER BY	d.DATECREATE DESC

E 12°55'05.63"
N 56°04'39.26"

Thanks for all your help but I have to go know I'll let you know how it turn out once again thanks for your hard work

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources