Solved

How to get the date of a status change

Posted on 2014-02-11
9
170 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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:ScottPletcher
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem with SqlConnection 5 121
SQL server 2008 SP4 29 34
SQL Server memory Issue 7 74
Mssql SQL query 14 28
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now