Can anyone Reduce this code to one SQL statement

[aumstu]

What I am trying to do is get the Max Activity Date and the min admit date.

Here is the Data...

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

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

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

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

ID	Effect Date	Admit Date	Activity Date
17189	200302	200302	2003-02-13

thanks

 

[SQLSister]

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

 

[aumstu]

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

 

[jbenson001]

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

Jim

 

[Geoka]

Hi,
Have a try with this 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))

 

[aumstu]

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

 

[Geoka]

Hi Aumstu

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

[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]