Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 611
  • Last Modified:

Calculating time between dates.

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
PHS_IT
Asked:
PHS_IT
  • 6
  • 5
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
select *
from yourtable n
where exists  (select 1 from yourtable p
     where p.[D/C Date] >= DateAdd(day, -30, n.[Admit Date])
                   )
0
 
LowfatspreadCommented:
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
 
folderolCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PHS_ITAuthor Commented:
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
 
PHS_ITAuthor Commented:
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
 
folderolCommented:
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
 
PHS_ITAuthor Commented:
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
 
PHS_ITAuthor Commented:
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
 
folderolCommented:
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
 
LowfatspreadCommented:
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
 
PHS_ITAuthor Commented:
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
 
folderolCommented:
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
 
PHS_ITAuthor Commented:
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
 
folderolCommented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now