Butterfly2
asked on
How to get the date of a status change
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]
from smsmir.mir_cen_hist
where orgz_cd = 'GCB0'
and pt_type_from = 'O'
and datepart(yy,pt_id_start_dt
and datepart(m,pt_id_start_dti
and nurs_sta in ('OBS1', 'OBS2', 'CPC', '4EM')
and episode_no = '753775998'
group by hosp_svc_from,
--nurs_sta,
episode_no ,
pt_id_start_dtime
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.
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]
from smsmir.mir_cen_hist
where orgz_cd = 'GCB0'
and pt_type_from = 'O'
and datepart(yy,pt_id_start_dt
and datepart(m,pt_id_start_dti
and nurs_sta in ('OBS1', 'OBS2', 'CPC', '4EM')
and episode_no = '753775998'
group by hosp_svc_from,
--nurs_sta,
episode_no ,
pt_id_start_dtime
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.
Move pt_type out of the where-clause and into a case-statement that pertains to each date:
If there are other pt_type that you want to exclude then add that filter back to the where-clause, something like pt_type IN ('O','S')
select episode_no as Acct
, pt_id_start_dtime as [Adm Date]
, MIN(case when pt_type_from = 'O' then xfer_eff_dtime end) as [PT Obs Start Date]
, MAX(case when pt_type_from = 'S' then xfer_eff_dtime end) as [PT Obs End Date]
, hosp_svc_from as OrigSvc
, max(nurs_sta) as ObsLoc
from smsmir.mir_cen_hist
where orgz_cd = 'GCB0'
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
, episode_no
, pt_id_start_dtime If there are other pt_type that you want to exclude then add that filter back to the where-clause, something like pt_type IN ('O','S')
ASKER
John_Vidmar, I tried the query you posted but the end date is comming out null
Would adding MVI to nurs_sta hurt anything?
nurs_sta in ('OBS1', 'OBS2', 'CPC', '4EM', 'MVI')ASKER
the client specifically asked for those nure stations, once this patient came out of observation they were moved to MVI, so is the patient is in mvi, they are no longer an observation patient. Remember there was nothing wrong with the patient obs start date, however when I plug in othere patient accounts the PT obs start date is coming up null.
Remember there is a PT_type_from and a PT type, only the pt_type_from has to be an O the pt_type can be anythin but an O
Remember there is a PT_type_from and a PT type, only the pt_type_from has to be an O the pt_type can be anythin but an O
select episode_no as Acct,
pt_id_start_dtime as [Adm Date],
MIN(xfer_eff_dtime) as [PT Obs Start Date],
MAX(CASE WHEN pt_typ <> 'O' THEN xfer_eff_dtime END) as [PT Obs End Date],
hosp_svc_from as OrigSvc,
max(nurs_sta) as ObsLoc
-- pt_type_from as [1st Pt Type]
from smsmir.mir_cen_hist
where orgz_cd = 'GCB0'
and pt_type_from = 'O'
and pt_id_start_dtime >= dateadd(month, datediff(month, 0, GETDATE()) - 1, 0)
and pt_id_start_dtime < dateadd(month, datediff(month, 0, GETDATE()), 0)
and (pt_typ <> 'O' or nurs_sta in ('OBS1', 'OBS2', 'CPC', '4EM'))
and episode_no = '753775998'
group by hosp_svc_from,
--nurs_sta,
episode_no ,
pt_id_start_dtime
pt_id_start_dtime as [Adm Date],
MIN(xfer_eff_dtime) as [PT Obs Start Date],
MAX(CASE WHEN pt_typ <> 'O' THEN xfer_eff_dtime END) as [PT Obs End Date],
hosp_svc_from as OrigSvc,
max(nurs_sta) as ObsLoc
-- pt_type_from as [1st Pt Type]
from smsmir.mir_cen_hist
where orgz_cd = 'GCB0'
and pt_type_from = 'O'
and pt_id_start_dtime >= dateadd(month, datediff(month, 0, GETDATE()) - 1, 0)
and pt_id_start_dtime < dateadd(month, datediff(month, 0, GETDATE()), 0)
and (pt_typ <> 'O' or nurs_sta in ('OBS1', 'OBS2', 'CPC', '4EM'))
and episode_no = '753775998'
group by hosp_svc_from,
--nurs_sta,
episode_no ,
pt_id_start_dtime
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I did the code my self, the other poster pointed me in a different direation.
ASKER
Acct xfer_eff_dtime nurs_st pt_typ Pt Type from
753789650 2013-12-31 10:01:00.000 NULL E NULL
753789650 2013-12-31 10:01:00.000 BED1 O NULL
753789650 2014-01-03 11:42:00.000 MVI S NULL
753789650 2014-01-03 13:08:00.000 NULL NULL S
753789650 2014-01-03 11:42:00.000 MVI S S
753789650 2013-12-31 10:01:00.000 BED1 E E
753789650 2013-12-31 13:47:00.000 BED1 E E
753789650 2013-12-31 14:27:00.000 BED1 E E
753789650 2013-12-31 14:27:00.000 BED1 O E
753789650 2014-01-03 13:08:00.000 NULL NULL O
*753789650 2013-12-31 15:21:00.000 4EM O O
753789650 2014-01-01 20:30:00.000 4EM O O
753789650 2014-01-02 14:50:00.000 4EM O O
**753789650 2014-01-03 11:42:00.000 MVI S O
*correct patient observeration start date
**correct patient observation enddate