How to get the date of a status change
Posted on 2014-02-11
Good Morning Experts,
I am doing a query on patients who are in observation, which means that I am filtering on pt_type = 'O'. Now I need to get the datetime of when the patient first went into observation which I can do with no problem but the trouble I am having is that when the patient leaves observation there patient type is no longer O. Here is my query
select episode_no as Acct,
pt_id_start_dtime as [Adm Date],
MIN(xfer_eff_dtime) as [PT Obs Start Date],
MAX(xfer_eff_dtime) as [PT Obs End Date],
hosp_svc_from as OrigSvc,
max(nurs_sta) as ObsLoc
-- pt_type_from as [1st Pt Type]
where orgz_cd = 'GCB0'
and pt_type_from = 'O'
and datepart(yy,pt_id_start_dtime) = datepart(yy,dateadd(m,-1,getdate()))
and datepart(m,pt_id_start_dtime) = datepart(m,dateadd(m,-1,getdate()))
and nurs_sta in ('OBS1', 'OBS2', 'CPC', '4EM')
and episode_no = '753775998'
group by hosp_svc_from,
the field xfer_eff_dtime is the date field that I have to use to get the date, but using the functions only gives me the latest date that was recorded for the person when they were in observation. My results are
PT Obs Start Date PT Obs End Date
2013-12-30 12:16:00.000 2014-01-01 11:14:00.000
But the actual date time is 2014-01-03 11:42:00.000 thats when pt _type went from 'O' to 'S'
Any assitance would be greatly appreciated.