My SQL Query is as follows:

Select pt_no, enc_no, adm_date, disch_date
from patient_encounter

Output:
pt_no enc no adm_date disch_date
5014 905690 01/02/04 01/14/04
5014 104780 01/16/04 01/16/04
5024 905700 12/10/03 12/12/03
5024 104790 01/08/04 01/08/04
5024 106782 01/15/04 01/15/04

Desired Output:
pt_no enc no adm_date disch_date adj disch date
5014 905690 01/02/04 01/14/04 01/14/04
5014 104780 01/16/04 01/16/04 01/31/04

columns continued from above:
Earliest_adm_date Latest_Adj_Dsch_Date
01/02/04 01/16/04
01/02/04 01/31/04


What modifications to my SQL is needed to obtain the "Adj Dsch Date" that should be the last day of the month for all "10" encounters (encounters that begin with "10") and provide the earliest admit date and the latest adjusted discharge date?

Thanks in advance!

Oracle,

select t.pt_no, t.enc_no, t.adm_date, t.disch_date,
(case when enc_no LIKE '10%' Then last_day(adm_date) ELSE adm_date END) As ADJ_DIS, v.min_am_date, v.max_disch_date
from table t
INNER JOIN
(select pt_no, min(t.adm_date) As min_am_date,
max(t.disch_date) As max_disch_date
from table t
group by pt_no) v ON
t.pt_no = v.pt_no;

r123456,

Thank you.

I will try the sql script.

My initial response is that the sql script that you provided does not incorporate a logic to output the "adjusted discharge date" as the last day of the month for the "10" encounters. At the same time, the query should give me the earliest admit date for all encounters, those encounters beginning with "9", "10", etc.

For example, the following additional information highlights three scenarios:

Disch Date Adj Disch Date Should Be
1/11/03 1/31/03
3/22/03 3/31/03
4/18/03 4/30/03

select v2.pt_no, v2.enc_no, v2.adm_date, v2.disch_date,
(CASE WHEN enc_no LIKE '10%' THEN last_day(disch_date) ELSE disch_date END) As ADJ_DISCH,
v2.min_am_date As Earliest_Adj_Dsch_Date,
(CASE WHEN v2.enc_no LIKE '10%' THEN last_day(disch_date) ELSE max_disch_date End) As Latest_Adj_Dsch_Date
from
(select t.pt_no, t.enc_no, t.adm_date, t.disch_date,
v.min_am_date, v.max_disch_date
from table t
INNER JOIN
(select pt_no, min(t.adm_date) As min_am_date,
max(t.disch_date) As max_disch_date
from table t
group by pt_no) v ON
t.pt_no = v.pt_no) v2;