What I am trying to do is get the Max Activity Date and the min admit date.
Here is the Data...
My first SQL Statement
Data Outcome
Second SQL Statement
Desired Outcome Result
thanks
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