Overlapping Dates

  • Thread starter Thread starter js
  • Start date Start date
J

js

I have a table (call it tableA) structured like the
following:

ID DrugName Date Days
1 DrugA 01/01/2003 30
2 DrugB 03/05/2003 30
3 DrugA 02/28/2003 90
etc...

What I need to accomplish (and have had a very hard time)
is to find any 2 distinct drugs (drugA or B in this case)
that overlap in their usage.
So, ID=1 is DrugA filled on 01/01/2003 for a 30 day
supply. So the time period is 01/01/2003 + 30= 01/31/2003.

This does not overlap any other drugs, but ID=3 does
overlap with DrugB (ID=2) for the given time period.

How can I structure a query to help me with this? Right
now i have done it via a 12,000 step process that is still
not completely accurate..

thanks for ANY help anyone cand lend.
JS
 
Dear JS:

I've heard of 12 step programs, but 12,000 steps seems like a lot of
steps! How about this:

SELECT A.DrugName AS Drug1, A.[Date] AS DateBegun1,
DateAdd("d", A.[Date], A.Days) AS DateEnd1,
B.DrugName AS Drug2, B.[Date] AS DateBegun2,
DateAdd("d", B.[Date], B.Days) AS DateEnd2
FROM tableA A, tableA B
WHERE A.ID < B.ID
AND (A.[Date] Between B.[Date] AND DateAdd("d", B.[Date], B.Days)
OR (DateAdd("d", A.[Date], A.Days) Between B.[Date]
AND DateAdd("d", B.[Date], B.Days)

Now, this isn't going to be extremely fast if there's a lot of data,
but then there are a huge number of possible combinations to try.

There is a note needed here. When row X overlaps row Y, then Y also
overlaps X. I'm assuming you don't want to see both. So, I've shown
the overlap only where the ID of Drug1 < the ID of Drug 2. As the ID
would seem to be the only unique key for doing this, that's an easy
way to limit the results. If you change the line to:

WHERE A.ID <> B.ID

Then you'll see everything twice. I considered having it be:

WHERE A.ID <> B.ID AND A.[Date] < B.[Date]

but this would have trouble when two rows start at the exact same
time.

Please let me know if this helped, and if I can be of further
assistance on this issue.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I think the following should work. What I have done is added you
table to the query twice, giving it an alias each time to make it
easier to code. Don't add a join between the two tables. This gives
us a Cartesian join. Then, I've eliminated all of the cases where the
IDs are equal, and where the Drug start date for the second table is <
the drug start date for the first drug. Finally, I've excluded all
those records where the DrugDate from the second table > outside the
expiration date of the first record. I've changed your field names to
DrugDate ([Date] is an Access reserved word and should not be used as
a field name) and DrugDays for my example.

SELECT T1.ID
, T1.DrugName
, T1.DrugDate
, DateAdd("d", T1.DrugDays, T1.DrugDate) as Expires
, T2.ID
, T2.DrugName
, T2.DrugDate as OverlapStarts
FROM tblDrugs T1, tblDrugs T2
WHERE T2.ID <> T1.ID
AND T2.DrugDate >= T1.DrugDate
AND T2.DrugDate <= DateAdd("d", T1.DrugDays, T1.DrugDate)

But I'm assuming that there are more fields involved here, like
CustomerID. In that case, you just need to add another criteria to
the WHERE clause such as below, or create a join between the two
tables on CustomerID.

AND T1.CustomerID = T2.CustomerID

--
HTH

Dale Fye


I have a table (call it tableA) structured like the
following:

ID DrugName Date Days
1 DrugA 01/01/2003 30
2 DrugB 03/05/2003 30
3 DrugA 02/28/2003 90
etc...

What I need to accomplish (and have had a very hard time)
is to find any 2 distinct drugs (drugA or B in this case)
that overlap in their usage.
So, ID=1 is DrugA filled on 01/01/2003 for a 30 day
supply. So the time period is 01/01/2003 + 30= 01/31/2003.

This does not overlap any other drugs, but ID=3 does
overlap with DrugB (ID=2) for the given time period.

How can I structure a query to help me with this? Right
now i have done it via a 12,000 step process that is still
not completely accurate..

thanks for ANY help anyone cand lend.
JS
 
Thanks Tom...I am getting an error that a ),], is missing
in the Where..AND statement but cannot find it...

-----Original Message-----
Dear JS:

I've heard of 12 step programs, but 12,000 steps seems like a lot of
steps! How about this:

SELECT A.DrugName AS Drug1, A.[Date] AS DateBegun1,
DateAdd("d", A.[Date], A.Days) AS DateEnd1,
B.DrugName AS Drug2, B.[Date] AS DateBegun2,
DateAdd("d", B.[Date], B.Days) AS DateEnd2
FROM tableA A, tableA B
WHERE A.ID < B.ID
AND (A.[Date] Between B.[Date] AND DateAdd("d", B. [Date], B.Days)
OR (DateAdd("d", A.[Date], A.Days) Between B.[Date]
AND DateAdd("d", B.[Date], B.Days)

Now, this isn't going to be extremely fast if there's a lot of data,
but then there are a huge number of possible combinations to try.

There is a note needed here. When row X overlaps row Y, then Y also
overlaps X. I'm assuming you don't want to see both. So, I've shown
the overlap only where the ID of Drug1 < the ID of Drug 2. As the ID
would seem to be the only unique key for doing this, that's an easy
way to limit the results. If you change the line to:

WHERE A.ID <> B.ID

Then you'll see everything twice. I considered having it be:

WHERE A.ID <> B.ID AND A.[Date] < B.[Date]

but this would have trouble when two rows start at the exact same
time.

Please let me know if this helped, and if I can be of further
assistance on this issue.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I have a table (call it tableA) structured like the
following:

ID DrugName Date Days
1 DrugA 01/01/2003 30
2 DrugB 03/05/2003 30
3 DrugA 02/28/2003 90
etc...

What I need to accomplish (and have had a very hard time)
is to find any 2 distinct drugs (drugA or B in this case)
that overlap in their usage.
So, ID=1 is DrugA filled on 01/01/2003 for a 30 day
supply. So the time period is 01/01/2003 + 30= 01/31/2003.

This does not overlap any other drugs, but ID=3 does
overlap with DrugB (ID=2) for the given time period.

How can I structure a query to help me with this? Right
now i have done it via a 12,000 step process that is still
not completely accurate..

thanks for ANY help anyone cand lend.
JS
Click to expand...

.
Click to expand...
 
I think it would be at the very end of the where clause. It would be
the matching ) to the ( that follows the AND.

--
HTH

Dale Fye


Thanks Tom...I am getting an error that a ),], is missing
in the Where..AND statement but cannot find it...

-----Original Message-----
Dear JS:

I've heard of 12 step programs, but 12,000 steps seems like a lot of
steps! How about this:

SELECT A.DrugName AS Drug1, A.[Date] AS DateBegun1,
DateAdd("d", A.[Date], A.Days) AS DateEnd1,
B.DrugName AS Drug2, B.[Date] AS DateBegun2,
DateAdd("d", B.[Date], B.Days) AS DateEnd2
FROM tableA A, tableA B
WHERE A.ID < B.ID
AND (A.[Date] Between B.[Date] AND DateAdd("d", B. [Date], B.Days)
OR (DateAdd("d", A.[Date], A.Days) Between B.[Date]
AND DateAdd("d", B.[Date], B.Days)

Now, this isn't going to be extremely fast if there's a lot of data,
but then there are a huge number of possible combinations to try.

There is a note needed here. When row X overlaps row Y, then Y also
overlaps X. I'm assuming you don't want to see both. So, I've shown
the overlap only where the ID of Drug1 < the ID of Drug 2. As the ID
would seem to be the only unique key for doing this, that's an easy
way to limit the results. If you change the line to:

WHERE A.ID <> B.ID

Then you'll see everything twice. I considered having it be:

WHERE A.ID <> B.ID AND A.[Date] < B.[Date]

but this would have trouble when two rows start at the exact same
time.

Please let me know if this helped, and if I can be of further
assistance on this issue.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I have a table (call it tableA) structured like the
following:

ID DrugName Date Days
1 DrugA 01/01/2003 30
2 DrugB 03/05/2003 30
3 DrugA 02/28/2003 90
etc...

What I need to accomplish (and have had a very hard time)
is to find any 2 distinct drugs (drugA or B in this case)
that overlap in their usage.
So, ID=1 is DrugA filled on 01/01/2003 for a 30 day
supply. So the time period is 01/01/2003 + 30= 01/31/2003.

This does not overlap any other drugs, but ID=3 does
overlap with DrugB (ID=2) for the given time period.

How can I structure a query to help me with this? Right
now i have done it via a 12,000 step process that is still
not completely accurate..

thanks for ANY help anyone cand lend.
JS
Click to expand...

.
Click to expand...
 
YUP!!!

I think it would be at the very end of the where clause. It would be
the matching ) to the ( that follows the AND.
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top