Solved

Calculating time between dates.

Posted on 2006-06-30
14
530 Views
Last Modified: 2012-08-14
We are trying to write an Access or SQL query that finds patients who were re-admitted to the hospital within 30 days of their last discharge date, and we are struggling BIG TIME!!  The table would be something like shown below.  Somehow, we need to have our query evaluate the patient's discharge date and compare that to their next admit date, then determine if the difference between the two dates is less than 30 days, and if so, return that Visit.  

Any takers?

Visit #      Name             ADmit Date      D/C Date
1      DOE, JOHN             01/01/2006      01/05/2006
2      DOE, JOHN             01/30/2006      02/08/2006
3      DOE, JOHN             05/30/2006      06/09/2006
0
Comment
Question by:PHS_IT
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17020739
select *
from yourtable n
where exists  (select 1 from yourtable p
     where p.[D/C Date] >= DateAdd(day, -30, n.[Admit Date])
                   )
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17025097
select *
  from YourTable as P
 Where Exists (select x.PatientID from YourTable as x
                          where P.PatientID=X.PatientID
                            and p.[D/C Date] >= Dateadd(day,-30,x.[Admit Date])
                     )
order by P.patientid,p.[Visit #]



AngelIII solution just forgot to include the test for the patient being the same...

you should have a patient identifier within your table...
you could use the name if thats all that you have but that is a very bad design if you don't actually
uniquely identify your patients...

hth
0
 
LVL 19

Expert Comment

by:folderol
ID: 17028549
Sorry I wanted to test something, so I changed the column names, but I did confirm you need to keep track of admissions.  So when you are looking for the existance of a discharge you can't accept every discharge, it has to be a discharge from a prior admission.    I hope this code makes sense...


select *
from yourtable as readmitted
where exists  
(
  select 1 from yourtable as prior_admission
  where prior_admisssion.discharged_on >= DateAdd(day, -30, readmitted.admitted_on)
  and prior_admission.admitted_on < readmitted.admitted_on
  and prior_admission.Name = readmitted.Name
)


Tom
0
 

Author Comment

by:PHS_IT
ID: 17093913
Yes, we do have a unique identifier, med_rec_no.  I have almost got this from your posting Lowfatspread, but I am also getting records for patients with only one visit for some reason.  I should never get a return where there are not at least two visit records.  Any ideas?
0
 

Author Comment

by:PHS_IT
ID: 17093949
My entire code is posted below, if that helps any.  For some reason, in the WHERE statement after EXISTS, when I try to code where p.med_rec_no = x.med_rec_no and (p.dschrg_ts > = DATEADD(day, -30, x.adm_ts....SQL reporting services removes both of the x's in the statement.????

SELECT     p.med_rec_no, p.vst_ext_id, RTRIM(TSM040_PERSON_HDR.lst_nm) + RTRIM(RTRIM(', ' + TSM040_PERSON_HDR.fst_nm)) AS Patient, p.adm_ts,
                      p.dschrg_ts
FROM         TPM300_PAT_VISIT AS p INNER JOIN
                      TSM180_MST_COD_DTL ON p.pat_ty = TSM180_MST_COD_DTL.cod_dtl_int_id INNER JOIN
                      TSM040_PERSON_HDR ON p.psn_int_id = TSM040_PERSON_HDR.psn_int_id
WHERE     (p.adm_ts > CONVERT(DATETIME, '2006-01-01 00:00:00', 102)) AND (TSM180_MST_COD_DTL.cod_dtl_ds = 'INPATIENT') AND EXISTS
                          (SELECT     med_rec_no
                            FROM          TPM300_PAT_VISIT AS x
                            WHERE      (p.med_rec_no = med_rec_no) AND (p.dschrg_ts >= DATEADD(day, - 30, adm_ts)))
ORDER BY p.med_rec_no, p.adm_ts
0
 
LVL 19

Expert Comment

by:folderol
ID: 17094359
I still think you need to add to the EXISTS ( ... )

WHERE      (p.med_rec_no = med_rec_no) AND (p.dschrg_ts >= DATEADD(day, - 30, adm_ts)) and
( p.adm_ts < x.adm_ts)

otherwise a discharge date will ALWAYS be greater than its own admission.  Comparing admission dates keeps that from happening.

Tom
0
 

Author Comment

by:PHS_IT
ID: 17094400
Tom,
I understand what you are saying.  I am currently trying to work with your posting.  The code I am using now is below.  However, I am still getting single visits for which there is no subsequent visit.  I am also getting visits that are more than 30 days apart...  This is so frustrating for someone as green as me when it comes to SQL!!!  Your assistance so far has been appreciated!  Thank you!

select med_rec_no, vst_ext_id, adm_ts, dschrg_ts
from TPM300_PAT_VISIT as readmitted
where pat_ty = 27360 and readmitted.adm_ts > CONVERT(DATETIME, '2006-01-01 00:00:00', 102)
and exists  
(select 1 from TPM300_PAT_VISIT as prior_admission
  where prior_admission.dschrg_ts >= DateAdd(day, -30, readmitted.adm_ts)
  and prior_admission.adm_ts < readmitted.adm_ts
  and prior_admission.med_rec_no = readmitted.med_rec_no)
ORDER BY med_rec_no, adm_ts
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:PHS_IT
ID: 17094488
I think I may know what part of my problem is, but I don't know exactly how to write it.  I need to select only inpatient visits and do not need to include a patient's outpatient visits, even if they are within the 30-day range.  Patient type 27360 in my code above is the inpatient patient type, but I think I may need to state that the patient type for both the readmitted table and the prior admission table need to look at that field.  Am I anywhere close, or totally confusing the issue?
0
 
LVL 19

Expert Comment

by:folderol
ID: 17094546
You sound like you are really close now.  Yes, you need to include

and prior_admitted.pat_ty = 27360

into the exists (...) part.

Tom
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17097001
select med_rec_no, vst_ext_id, adm_ts, dschrg_ts
  from TPM300_PAT_VISIT as readmitted
 where pat_ty = 27360
   and readmitted.adm_ts > CONVERT(DATETIME, '2006-01-01 00:00:00', 102)
   and exists  
       (select 1
          from TPM300_PAT_VISIT as prior_admission
         where prior_admission.dschrg_ts >= DateAdd(day, -30, readmitted.adm_ts)
           and prior_admission.adm_ts < readmitted.adm_ts
           and prior_admission.med_rec_no = readmitted.med_rec_no
           and prior_admission.pat_ty = 27360
       )
ORDER BY med_rec_no, adm_ts
0
 

Author Comment

by:PHS_IT
ID: 17099734
OK, Tom, your code is working, however, it appears that it is only returning the readmitted visit, rather than the two visits that are within the 30-day time frame.  For example, in my original post, I would expect to have the following returned:

1     DOE, JOHN            01/01/2006     01/05/2006
2     DOE, JOHN            01/30/2006     02/08/2006

As it works now, though, only the following is returned:

2     DOE, JOHN            01/30/2006     02/08/2006

Does that have something to do with the select 1???  I don't know what that code means.
0
 
LVL 19

Accepted Solution

by:
folderol earned 500 total points
ID: 17101782
That is the nature of the EXISTS( ... ) code.  It only finds a true or false result for one row.  Since the re-admission row has a result for the EXISTS of FALSE, it is not included into the result set.

Whatever I did to test your code before, I apparantly erased. So, I have two untested options for you, I'm curious if this first one works.
---------------------------------------------------
select med_rec_no, vst_ext_id, adm_ts, dschrg_ts
from TPM300_PAT_VISIT as readmitted
join  TPM300_PAT_VISIT as prior_admission on
           and prior_admission.med_rec_no = readmitted.med_rec_no
           and prior_admission.pat_ty = 27360
where
pat_ty = 27360 and
readmitted.adm_ts > CONVERT(DATETIME, '2006-01-01 00:00:00', 102)
and
((
prior_admission.dschrg_ts >= DateAdd(day, -30, readmitted.adm_ts) and
prior_admission.adm_ts < readmitted.adm_ts
)
or
(
readmitted.dschrg_ts >= DateAdd(day, -30, prior_admission.adm_ts) and
prior_admission.adm_ts > readmitted.adm_ts
))

ORDER BY med_rec_no, adm_ts
---------------------------------------
This 2nd option should be no problem, even if it lacks elegance.

select med_rec_no, vst_ext_id, adm_ts, dschrg_ts
  from TPM300_PAT_VISIT as readmitted
 where pat_ty = 27360
   and readmitted.adm_ts > CONVERT(DATETIME, '2006-01-01 00:00:00', 102)
   and exists  
       (select 1
          from TPM300_PAT_VISIT as prior_admission
         where prior_admission.dschrg_ts >= DateAdd(day, -30, readmitted.adm_ts)
           and prior_admission.adm_ts < readmitted.adm_ts
           and prior_admission.med_rec_no = readmitted.med_rec_no
           and prior_admission.pat_ty = 27360
       )

UNION

select med_rec_no, vst_ext_id, adm_ts, dschrg_ts
  from TPM300_PAT_VISIT as readmitted
 where pat_ty = 27360
   and readmitted.adm_ts > CONVERT(DATETIME, '2006-01-01 00:00:00', 102)
   and exists  
       (select 1
          from TPM300_PAT_VISIT as prior_admission
         where readmitted.dschrg_ts >= DateAdd(day, -30, prior_admission.adm_ts)
           and readmitted.adm_ts < prior_admission.adm_ts
           and prior_admission.med_rec_no = readmitted.med_rec_no
           and prior_admission.pat_ty = 27360
       )

ORDER BY med_rec_no, adm_ts


It's might not be a good idea in the 2nd option's second select statement appearing right after the UNION keyword that I reuse the table names READMITTED and PRIOR_ADMISSION since that is logically not correct and would be confusing for documentation, but I wanted to show that I am just reversing the comparison so that both the admissions appear in your final result set.  Besides I don't pay much attention to documentation for ad hoc reports or small projects. :)

Tom

0
 

Author Comment

by:PHS_IT
ID: 17102786
Tom,
The first code gave me a syntax error on the first "and" after the join.  The second code worked wonderfully, but opened up another issue for me in adding the person's name from another table.  I am going to award you the points, however, because you've been a great help as I struggle through this and I don't want to absorb any more of your time!  Your code does work for what I've asked for!  You don't consult, do you ha, ha!!
Thanks again for your patience and wonderful assistance!

Tracy
0
 
LVL 19

Expert Comment

by:folderol
ID: 17103194
Thanks, I enjoyed it.  The 1st option just needs to remove extra word,  "on and " should be "on ".

(Assuming med_rec_no is the PatientID...)
The 1st option should allow you to join the person's name table simply by adding just before the where clause,

join person_name_table on readmitted.med_rec_no = person_name_table.med_rec_no
Where .......
-------------

the 2nd, I would suggest a little more work.

--------------
Select med_rec_no, vst_ext_id, adm_ts, dschrg_ts,

patient_name_table.last_name

from
(
select med_rec_no, vst_ext_id, adm_ts, dschrg_ts
  from TPM300_PAT_VISIT as readmitted
 where pat_ty = 27360
   and readmitted.adm_ts > CONVERT(DATETIME, '2006-01-01 00:00:00', 102)
   and exists  
       (select 1
          from TPM300_PAT_VISIT as prior_admission
         where prior_admission.dschrg_ts >= DateAdd(day, -30, readmitted.adm_ts)
           and prior_admission.adm_ts < readmitted.adm_ts
           and prior_admission.med_rec_no = readmitted.med_rec_no
           and prior_admission.pat_ty = 27360
       )

UNION

select med_rec_no, vst_ext_id, adm_ts, dschrg_ts
  from TPM300_PAT_VISIT as readmitted
 where pat_ty = 27360
   and readmitted.adm_ts > CONVERT(DATETIME, '2006-01-01 00:00:00', 102)
   and exists  
       (select 1
          from TPM300_PAT_VISIT as prior_admission
         where readmitted.dschrg_ts >= DateAdd(day, -30, prior_admission.adm_ts)
           and readmitted.adm_ts < prior_admission.adm_ts
           and prior_admission.med_rec_no = readmitted.med_rec_no
           and prior_admission.pat_ty = 27360
       )
)
as admission_list
join patient_name_table on admission_list.med_rec_no = patient_name_table.med_rec_no
Order by ORDER BY med_rec_no, adm_ts
--------------

You just put ( ) around the starting query to make it an object, named as, (in this case, admission_list),
then you can join more tables to it in steps, making the whole more complex in chunks so you don't have to face big debug issues.

It's like taking the beginning query and making a view out of it, then increasing the complexity in steps.  The chief difference is queries in ( ) can't be referenced by other connections, whereas a view can be re-used since it is saved on the server.

I don't consult, except for my sister's outfit :).  She gets to jump the queue.

Tom
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

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…
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

747 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

9 Experts available now in Live!

Get 1:1 Help Now