Using Min/Max Functions - 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

General SQL Server Forums

New to SQL Server Programming

Using Min/Max Functions

Author

Topic

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2008-11-21 : 13:55:14

Hi all. Very new to this board so please excuse me as I'm sure this may have been answered before.

I am pulling data on drugs based off of an ID number. Now what I need to do is find the price of that drug the first time it was utilized in a given month.

I have 4 columns...

dt.Product_ID,
dt.Product_Name,
Min(dt.DateOfService),
dt.Cost

I believe that the function works off of grouping, but having Group By Product_ID, Product_Name is still giving me the minimum date of each individual cost. (ie on Jan 1 the $ is $1.00, Jan 4 the $ is $.50 and Jan 16 $ is $.50 it is returning two lines... Jan 1, $1.00 and Jan 4, $.50) What I need is one line...
Product ID, Product Name,Jan 1, $1.00

Can anyone help with my grouping, or give me some ideas on how to get my desired results.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-11-21 : 14:30:40

select dt.Product_ID,
dt.Product_Name,
dt.DateOfService,
dt.Cost
from tbl dt
join (select Product_ID, DateOfService = min(DateOfService) fom tbl dt group by Product_ID) dtmin
on dt.Product_ID = dtmin.Product_ID
and dt.DateOfService = dtmin.DateOfService

Can also group by the month in the derived table if you want it for each month.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2008-11-21 : 16:11:29

Thanks I appreciate the help, It did not work in my case because I am using a different querying system at work and was hoping to correlate your method with what I know to work already and they just don't seem to be compatible.

The application I run off, some limited SPSS program, allows for subqueries only in the WHERE statement. so here would be the SQL statement I have to use now...(which does not give me the desired results...)

SELECT
ProductID,
ProductName,
MIN( DateofService ) AS "MinMxtdnb",
Cost
FROM
RXTPRDFIL.RT200P RT200P
WHERE
( Carrier = 'MPS'
AND YEAR = 2008
AND MONTH = 11
AND ProductID IN( 0001,0002 ) <---there is a list here
AND Claim_Status IN( 'P', 'X' ))

GROUP BY
ProductID,
ProductName,
Cost

I can create an implied inner join by writing...
FROM
RXTPRDFIL.RT200P RT200P,
RXTPRDFIL.RT200P RT200P_2

I can even write conditions between the fields..
RT200P.ProductID = RT200P_2.ProductID

But since RT200P_2 contains the exact same data I can't get anything out of it...

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-11-21 : 16:44:54

quote:
Originally posted by DP978

I am pulling data on drugs

Sorry, couldn't resist.....I haven't done that in years (pulling data on drugs, that is)! Anyway, what querying system are you using at work? This is a MS SQL website and you'll surely get some very helpful advice but will it really translate well to whatever it is you're using? Isn't there a forum for the tool you're using where you'd possibly get more relevant advice?

Terry

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-21 : 16:51:35

What querying system are you talking about here, please.
Just give this a try anyhow. Might just work for you, Should give you an idea atleast.
I've tried putting it in the format you described earlier.


SELECT
	A.ProductID,A.ProductName,B.Cost 

FROM


	(
	SELECT
		ProductID,
		ProductName,
		MIN( DateofService ) AS MinMxtdnb

	FROM 
		RXTPRDFIL.RT200P RT200P 
	WHERE 
		( Carrier = 'MPS'
		AND YEAR = 2008
		AND MONTH = 11
		AND ProductID IN( 0001,0002 ) 
		AND Claim_Status IN( 'P', 'X' )) 

	GROUP BY 
		ProductID,
		ProductName
	)A,

	(
	SELECT
		ProductID,
		DateofService,
		Cost

	FROM 
		RXTPRDFIL.RT200P RT200P 
	WHERE 
		( Carrier = 'MPS'
		AND YEAR = 2008
		AND MONTH = 11
		AND ProductID IN( 0001,0002 ) 
		AND Claim_Status IN( 'P', 'X' )) 
	)B
WHERE 
	A.MinMxtdnb=B.DateofService
	and A.ProductID=B.ProductID

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2008-11-21 : 17:00:09

Thanks for the response,

The reason I have to come here is because no one around me knows what product we are using or why, we are transitioning to our own MS SQL server to house our data internally within the next year or so. So i've been scouring these forums getting the head start for when things go live.

We use a very derelict version of SPSS that we pay for through another 3rd party who stores our data. So this 3rd party is the one that holds control over everythign and all the documentation. I believe the tools are very old, circa 2003, and use out of date technology. Ive been forced to make due with what I have and use intuitiveness to get the tool to do what I want.

The help topics within the program say nothing about subqueries or many of the other functions ive come across and used...and its not good when I send samples of my Working queries to said 3rd party and they cant digest it...

Emailing their help desk is like pulling teeth as well.. since I think they have to go to other 'experts' to find answers...

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2008-11-24 : 09:42:27

Sakets,

Thank you for the response, I have not had a chance to test this yet as i had a very busy weekend. I did not want to seem rude and not respond or come off ungrateful. I think what you all do here is tremendous and it is wonderful to see so many people share their knowledge and time with others.

So thanks again, and I will update this soon.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2008-11-24 : 16:11:18

This still will not work... :( this is the code without Blinding any of the fields.

SELECT
A.MXT9C1,
A.MXXJTX,
B.MXONN1
FROM
(SELECT
RT200P.MXT9C1,
RT200P.MXXJTX,
MIN(RT200P.MXTDNB) as "MinDate"
FROM
RXTPRDFIL.RT200P RT200P
WHERE
(RT200P.MXUKC2 = 'MPSNHP'
AND RT200P.MXLHNB = 2008
AND RT200P.MXOEN1 = 1
AND RT200P.MXZSST IN( 'P', 'X' ) )
) A,
(SELECT
RT200P.MXT9C1,
RT200P.MXTDNB,
RT200P.MXONN1
FROM
RXTPRDFIL.RT200P RT200P
WHERE

(RT200P.MXUKC2 = 'MPSNHP'
AND RT200P.MXLHNB = 2008
AND RT200P.MXOEN1 = 1
AND RT200P.MXZSST IN( 'P', 'X' ))
) B

WHERE
A.MinDate = B.MXTDNB
AND A.MXT9C1 = B.MXT9C1

I get the error..

"The Parser encountered a syntax error at token: select"

I am using an SPSS program that is simply called "Query" the copyright is to Strategy and it connects to a ShowCase Server. (if that helps at all)

So far I have tried two methods adding subqueries in the Select statements, and adding queries in the From statements...

What is odd is that it will accept...

Select
MemberName
From
RT200P
Where
MemberName = ANY(Select MemberName From .....)
MemberName = 'Joe'

it is so odd to me that I can write more statements in the Where clause, but no where else.

Anyone see any mistakes in my version? or any help... I really really do appreciate it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources