Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Can anyone Reduce this code to one SQL statement

Status
Not open for further replies.

aumstu

MIS
Jan 20, 2006
40
US
What I am trying to do is get the Max Activity Date and the min admit date.

Here is the Data...

Code:
ID	Effect Date	Admit Date	Activity Date
17189	199603	199201	1997-04-14
17189	200303	200303	2003-02-13
17189	200302	200302	2003-02-13
17189	199503	199201	1995-11-15
17189	199201	199201	1995-11-15

My first SQL Statement

Code:
SELECT t1.TABLENAME_ID, MIN(t1.TABLENAME_ADMIT), MIN(t1.TABLENAME_EFFECT), MAX(TABLENAME_ACTIVITY_DATE) 
FROM TABLENAME t1
JOIN (SELECT TABLENAME_ID, MAX(TABLENAME_ACTIVITY_DATE) AS MAXACT, MIN(TABLENAME_ADMIT) AS MINADMIT, MIN(TABLENAME_EFFECT) AS MINEFF
FROM TABLENAME
GROUP BY TABLENAME_ID, TABLENAME_ADMIT, TABLENAME_EFFECT) X
on t1.TABLENAME_ID = X.TABLENAME_ID
and t1.TABLENAME_ADMIT = X.MINADMIT
and t1.TABLENAME_EFFECT = X.MINEFF
and t1.TABLENAME_ACTIVITY_DATE = X.MAXACT
and t1.TABLENAME_ID = 17189
GROUP BY t1.TABLENAME_ID, t1.TABLENAME_ACTIVITY_DATE

Data Outcome
Code:
ID	Effect Date	Admit Date	Activity Date
17189	199201	199201	1995-11-15
17189	199201	199603	1997-04-14
17189	200302	200302	2003-02-13

Second SQL Statement


Code:
SELECT t1.TABLENAME_ID, t1.MINADMIT, t1.MINEFF, t1.MAXACT
FROM SV1STDN_tbl t1
JOIN (SELECT TABLENAME_ID, MAX(MAXACT) AS MAXACT
FROM SV1STDN_tbl
GROUP BY TABLENAME_ID) X
on t1.TABLENAME_ID = X.TABLENAME_ID
and t1.MAXACT = X.MAXACT
and t1.TABLENAME_ID = 17189
GROUP BY t1.TABLENAME_ID, t1.MINADMIT, t1.MINEFF, t1.MAXACT

Desired Outcome Result
Code:
ID	Effect Date	Admit Date	Activity Date
17189	200302	200302	2003-02-13

thanks

 
Your desired output doesn;t match the first sentence of what you said. It has the max activity date not the min in it. Which do you want?

Questions about posting. See faq183-874
Click here to help with Hurricane Relief
 
Thanks for responding…

I need to get the Max Activity Date (there are two dates that match 2003-02-13) and the Min Admit Date (which would be 200302 and not the 200303)

I could only do this using two statements…I imagine that there has to be a faster way

Thanks again
 
Faster does not necessarly mean one sql statement. Sometimes it is better to break up a process into multiple sql statements.

Jim
 
Hi,
Have a try with this code....

Code:
Create Table Test (ID Int, EffectDate Int, AdmitDate Int, ActivityDate DateTime)
Insert Into Test Values(17189, 199603,199201,'19970414')
Insert Into Test Values(17189,200303,200303,'20030213')
Insert Into Test Values(17189,200302,200302,'20030213')
Insert Into Test Values(17189,199503,199201,'19951115')
Insert Into Test Values(17189,199201,199201,'19951115')


select ID, EffectDate, AdmitDate, ActivityDate from test
Where activitydate in 
	(Select Top 1 Max(ActivityDate) From Test Group By ID)
	And AdmitDate in
	(Select Min(AdmitDate) From Test 
		Where ActivityDate in (Select Top 1 Max(ActivityDate) From Test Group By ID))
 
Thanks for the replies...

Geoka, I think the code would work great if I only had one ID, I did not mention in the orginal post that there are several different IDs. So, this code returns the max and min for the entire table. I think I will just use the two sql statements for now and take jbenson001 advice.

Thanks again for the posts
 
Hi Aumstu

Why don't you try with this code before applying two sql statements :).

Code:
[COLOR=blue]
Select Distinct ID, EffectDate, AdmitDate, ActivityDate From Test T1 Where
ActivityDate In 
	(Select Distinct Max(ActivityDate) From Test Where Test.ID=T1.ID)
		And AdmitDate =
			(Select Distinct Min(AdmitDate) From Test Where Test.ID=T1.ID 
				And ActivityDate In
					(Select Distinct Max(ActivityDate) From Test Where Test.ID=T1.ID))
[/color]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top