Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Calculating time between dates.

Posted on 2006-06-30
Medium Priority
594 Views
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
Question by:PHS_IT
[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
• 6
• 5
• 2
• +1

LVL 143

Expert Comment

ID: 17020739
select *
from yourtable n
where exists  (select 1 from yourtable p
)
0

LVL 50

Expert Comment

ID: 17025097
select *
from YourTable as P
Where Exists (select x.PatientID from YourTable as x
where P.PatientID=X.PatientID
)
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

hth
0

LVL 19

Expert Comment

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 *
where exists
(
select 1 from yourtable as prior_admission
)

Tom
0

Author Comment

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

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
0

LVL 19

Expert Comment

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

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

Tom
0

Author Comment

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!

and exists
(select 1 from TPM300_PAT_VISIT as prior_admission
0

Author Comment

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

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

into the exists (...) part.

Tom
0

LVL 50

Expert Comment

ID: 17097001
where pat_ty = 27360
and exists
(select 1
)
0

Author Comment

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

folderol earned 2000 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.
---------------------------------------------------
where
pat_ty = 27360 and
and
((
)
or
(
))

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

where pat_ty = 27360
and exists
(select 1
)

UNION

where pat_ty = 27360
and exists
(select 1
)

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

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

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.

--------------

patient_name_table.last_name

from
(
where pat_ty = 27360
and exists
(select 1
)

UNION

where pat_ty = 27360
and exists
(select 1
)
)
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

Question has a verified solution.

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

Why is this different from all of the other step by step guides? Â Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff lâ€¦
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE sâ€¦
###### Suggested Courses
Course of the Month5 days, 19 hours left to enroll