If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: DateDiff

Hello-
I have a table with multiple rows for Members.
I need to pull from this table the rows for Members cases where the difference between the start date for one instance is <31 days from the the end of another case for that member. They may have 2,3,4,5 cases show up on the initial table. For example, this member as 4 rows, but I only want the last three on the report:

SELECT *
FROM MembersCases AS this
WHERE EXISTS (SELECT 1
FROM MembersCases AS prev
WHERE prev.MemberID = this.memberID
AND prev.CaseID = this.CaseID
AND prev.StartDt < this.StartDt
AND this.StartDt - 31 Days < prev.EndDt)

-PatP

In theory, theory and practice are identical. In practice, theory and practice are unrelated.

I can tell that the answer is "yes", but I don't understand your query enough to answer the "how" part!

Without knowing the structure of your table or tables, how you are joining them and what problem you're having accomplishing that goal, I'm not sure how to coach you. I'm 99% sure that you can just write a SELECT statement as you see fit then tack on my EXISTS clause and toddle merrily on your way!

-PatP

In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Here is the basic gist of my query. The result of this code contains the 4 rows I'd provided as an example where I have multiple rows back for one member, some of which I need to keep as the date in CaseBeginDt falls within 30 days of another case's DischargeDt for that same member. The example I'd provided had 1 row that was in January, the other three fell within 30 days of each other. I need to exclude the case in January (outside of 30 days). How do I apply your suggested "where exists" code? Thank you

Code:

SELECT
a.CaseID,
a.CaseBeginDt,
b.DischargeDt,
a.MemberID
FROM DB2PROD.TMDTRPTMR a,
DB2PROD.TMDTRPPRVSRV b
WHERE a.I_TRIMED_SYS_KEY=b.PRV.I_TRIMED_SYS_KEY
AND a.C_TMR_TYPE = 'PC'
and b.D_SERV_BEG Between '2014-01-01' and '2014-04-30'
and a.C_SBU <> '35'
and a.C_REPORT_CATG Not In ('FEP','MEDS','NATL')
and b.C_REMARK_CATG <> 'L'
and b.Q_UNITS_APPR <> '0'

If a gap more than 30 days was in middle of a member(like memberID = 89506 in the following sample),
which row(s) should take and which row(s) should drop?

You make a good point, which I considered while constructing my query. That's why I kept the date logic on one line so that no matter which choice was needed the change would be trivial.

i took my best guess and assumed that the "base" row and not the row with the additional discharge date should be returned. This query has evolved a bit since the original post, and it could spin a number of different ways.

All I can do is answer questions as posted, and sometimes ask questions to help the user realized what they might be missing. Without access to detailed problem specifications, anything else is just a guess.

-PatP

In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Hi-
Thank you for your responses. I'll give your code a whirl -looks like it should give me what I need which is a list of members who were hospitalized, discharged, then readmitted within 30 days of the previous hospitalization.
I'll be back with questions if need be.
Thanks again!
Laura

I had to adjust the code because the member ID is only on one of the tables. I decided I could pull in the information on the second table after getting the right admits on the base result set. That being said, this is how I tweaked the code, which isn't quite right because I'm still getting 4 cases for the one member (the one that I don't want to see the January case).

Without having your tables/data to work with I can't experiment to figure out what I might have done wrong. It is almost certainly in the last line (with the comment: Interesting date). Please think through (and maybe add the c.D_TMR_END and b.D_TMR_BEGIN rows as diagnostics to your result set).

I probably just fumbled something in the comparison, but without seeing your data/results I'm not willing or able to spend more time on this.

-PatP

In theory, theory and practice are identical. In practice, theory and practice are unrelated.