Solved

How to get the date of a status change

Posted on 2014-02-11
9
182 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

776 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