Solved

How to get the date of a status change

Posted on 2014-02-11
9
194 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

728 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