Solved

How to get the date of a status change

Posted on 2014-02-11
9
189 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
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.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

860 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