Overlapping Time Intervals

[sj]

I've been trying to figure this out for a while now.
I am trying to identify "overlapping" drug usage. See
tbl.example

TN Drug Person Date DaysSupply
1 DrugX 112 1/1/2003 30
2 DrugY 112 2/15/2003 15
3 DrugX 112 2/3/2003 5
4 DrugX 113 1/1/2003 15
5 DrugY 113 1/10/2003 30
6 DrugZ 113 1/15/2003 30

I want to identify when a person is using 2 different
drugs for a certain amount of overlapping time, let's say
10 days in this case.

Person 112 does not have any that meet criteria.
Person 113 has overlap of Drug x and Drug Y, but only for
5 days. Drug Y & Drug Z does seem to satisfy criteria with
overlap of more than 10 days.

Now I did this by just eying it of course.
How can I programmatically accomplish this for a large
data set?

Any help is MUCH appreciated.
Thank You

 

[John Viescas]

This will display all overlaps:

SELECT T1.TN, T1.Drug, T1.Person, T1.[Date], T1.DaysSupply, T2.TN,
T2.[Date], T2.DaysSupply
FROM MyTable As T1 INNER JOIN MyTable As T2
ON T1.Person = T2.Person
WHERE (T1.TN <> T2.TN) And (T2.[Date] >= T1.[Date])
AND ((T1.[Date] +T1.DaysSupply -1) >= T2.[Date])

Calculating 10 or more days overlap is a bit trickier. Try this:

SELECT T1.TN, T1.Drug, T1.Person, T1.[Date], T1.DaysSupply, T2.TN,
T2.[Date], T2.DaysSupply
FROM MyTable As T1 INNER JOIN MyTable As T2
ON T1.Person = T2.Person
WHERE (T1.TN <> T2.TN) And (T2.[Date] >= T1.[Date])
AND ((T1.[Date] +T1.DaysSupply -1) >= T2.[Date])
AND (((T1.[Date] + T1.DaysSupply) <= (T2.[Date] + T2.DaysSupply))
AND (T2.DaysSupply >=10))
OR (((T2.[Date] + T2.DaysSupply) > (T1.[Date] + T1.DaysSupply))
AND ((T1.[Date] + T1.DaysSupply) - T2.[Date]) >= 10)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[sj]

John-
I can't thank you enough!!! This works great!!!

-----Original Message-----
This will display all overlaps:

SELECT T1.TN, T1.Drug, T1.Person, T1.[Date], T1.DaysSupply, T2.TN,
T2.[Date], T2.DaysSupply
FROM MyTable As T1 INNER JOIN MyTable As T2
ON T1.Person = T2.Person
WHERE (T1.TN <> T2.TN) And (T2.[Date] >= T1.[Date])
AND ((T1.[Date] +T1.DaysSupply -1) >= T2.[Date])

Calculating 10 or more days overlap is a bit trickier. Try this:

SELECT T1.TN, T1.Drug, T1.Person, T1.[Date], T1.DaysSupply, T2.TN,
T2.[Date], T2.DaysSupply
FROM MyTable As T1 INNER JOIN MyTable As T2
ON T1.Person = T2.Person
WHERE (T1.TN <> T2.TN) And (T2.[Date] >= T1.[Date])
AND ((T1.[Date] +T1.DaysSupply -1) >= T2.[Date])
AND (((T1.[Date] + T1.DaysSupply) <= (T2.[Date] + T2.DaysSupply))
AND (T2.DaysSupply >=10))
OR (((T2.[Date] + T2.DaysSupply) > (T1.[Date] + T1.DaysSupply))
AND ((T1.[Date] + T1.DaysSupply) - T2.[Date]) >= 10)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

I've been trying to figure this out for a while now.
I am trying to identify "overlapping" drug usage. See
tbl.example

TN Drug Person Date DaysSupply
1 DrugX 112 1/1/2003 30
2 DrugY 112 2/15/2003 15
3 DrugX 112 2/3/2003 5
4 DrugX 113 1/1/2003 15
5 DrugY 113 1/10/2003 30
6 DrugZ 113 1/15/2003 30

I want to identify when a person is using 2 different
drugs for a certain amount of overlapping time, let's say
10 days in this case.

Person 112 does not have any that meet criteria.
Person 113 has overlap of Drug x and Drug Y, but only for
5 days. Drug Y & Drug Z does seem to satisfy criteria with
overlap of more than 10 days.

Now I did this by just eying it of course.
How can I programmatically accomplish this for a large
data set?

Any help is MUCH appreciated.
Thank You

.