Solved

How to get the date of a status change

Posted on 2014-02-11
9
191 Views
Last Modified: 2014-02-17
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_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,
               --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.
0
Comment
Question by:Butterfly2
  • 5
  • 3
9 Comments
 

Author Comment

by:Butterfly2
ID: 39850065
of course my logic is wrong but here is sample data set, I am using the pt type from  as the maint patient type


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
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39850217
Move pt_type out of the where-clause and into a case-statement that pertains to each date:
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 

Open in new window


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')
0
 

Author Comment

by:Butterfly2
ID: 39850300
John_Vidmar, I tried the query you posted but the end date is comming out null
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39850338
Would adding MVI to nurs_sta hurt anything?

nurs_sta in ('OBS1', 'OBS2', 'CPC', '4EM', 'MVI')

Open in new window

0
 

Author Comment

by:Butterfly2
ID: 39850446
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
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39850686
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
0
 
LVL 11

Assisted Solution

by:John_Vidmar
John_Vidmar earned 500 total points
ID: 39850695
I didn't realize there was a field called pt_type_from and another called pt_type (thought that was a typo).

Your where-clause is filtering out data that is needed to calculate your end-date.  Try moving stuff from your where-clause that is specific to the start-date into the case-statement for the start-date (and do the same for end-date).
0
 

Accepted Solution

by:
Butterfly2 earned 0 total points
ID: 39854027
I was ablet to resolve the problem after John pointed me in a diffierent direction with his last post.  See below:

with a(Acct,
           [Adm Date],
           [PT Obs Start Date]
            --OrigLoc
         )
  as

(select  episode_no as Acct,
           pt_id_start_dtime as [Adm Date],
              Min(xfer_eff_dtime ) as [PT Obs End Date]
              --nurs_sta_from as OrigLoc
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 pt_type_from = 'O'
            --and      episode_no = '753789650'
            and nurs_sta_from  in ('OBS1', 'OBS2', 'CPC', '4EM')
group by episode_no,
               pt_id_start_dtime
              --nurs_sta_from  
  ),
               
  b( Acct,
     [PT Obs End Date]
     )
 as
  (select  episode_no as Acct,
              Min(xfer_eff_dtime ) as [PT Obs End Date]
              
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  pt_type_from <> pt_type
            and pt_type_from = 'O'
            --and      episode_no = '753789650'
group by episode_no
   )
             
  select a.Acct,
             a.[Adm Date],
             a.[PT Obs Start Date],
             b.[PT Obs End Date]
            -- a.OrigLoc
  from a
   Left outer join b
       on a.acct = b.Acct
      order by Acct
0
 

Author Closing Comment

by:Butterfly2
ID: 39864320
I did the code my self, the other poster pointed me in a different direation.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question