Reference to next record within a group

[Simon L]

I am trying to set up a summary query to identify how many total days a
patient has gone without any medication. The following are the data that I
start with.

Member # Fill Date Days Supply Next Med Date
940183753-02 1/11/2007 30 2/10/2007
940183753-02 2/7/2007 30 3/9/2007
940183753-02 2/8/2007 28 3/8/2007
940183753-02 2/11/2007 90 5/12/2007
940183753-02 5/2/2007 90 7/31/2007
940183753-02 5/2/2007 90 7/31/2007
940183753-02 7/12/2007 30 8/11/2007
940183753-02 9/19/2007 30 10/19/2007
940183753-02 9/19/2007 30 10/19/2007

This particular mbr was on multiple meds for a specific disease and filled
those meds at various dates within a time period. Based on the days supply of
the each refill, I can calculate when he needs a refill before med runs out
(Next Med Date). What I need to find out is: once he started on a treatment
between 1/11/07 and 10/19/07, how many days he was without medication of any
kind. I need to have this info for thousand of mbrs being treated for
specific disease. Each mbr has differenct start and end date of tx period.
The type of meds does not matter, we just need to know if a mbr is on at
least one med. Any help will be appreciated. Thanks

 

[Michel Walsh]

Start with a query like:

-----------------------------------------
SELECT memberNumber, FillDate AS theDate, 1 AS direction FROM myTable
UNION ALL
SELECT memberNumber, NextMedDate, -1 FROM myTable
-----------------------------------------

Say it is saved under the name qu. Next, we need a running sum of direction
over theDate:


SELECT a.memberNumber, a.theDate, SUM(b.direction) AS running
FROM qu AS a INNER JOIN qu AS b
ON a.memberNumber = b.memberNumber
AND a.theDate >= b.theDate
GROUP BY a.memberNumber, a.theDate

Indeed, +1 is added as a new medicamentation starts, and -1 is added when a
medicamentation ends. So, you can now see that a running of 0 means the
memberNumber is without medication starting at the said date. So, we need
only to keep running of 0 and 1 ( since 1 == start of a new period WITH some
medicamentation):


----------------------------------------
SELECT a.memberNumber, a.theDate, SUM(b.direction) AS running
FROM qu AS a INNER JOIN qu AS b
ON a.memberNumber = b.memberNumber
AND a.theDate >= b.theDate
GROUP BY a.memberNumber, a.theDate
HAVING SUM(b.direction) IN (0, 1)
----------------------------------------

Saved it as qRun.

Assuming all interval are closed, ie: the number of record, per
memberNumber, is even, then, to get the number of days WITH medicamentation,
we can compute it with:


SELECT memberNumber, SUM( iif(running =0, theDate, -theDate))
FROM qRun
GROUP BY memberNumber

Indeed, if you have the data:

member theDate running
xxxxxxx a 1
xxxxxxx b 0
xxxxxxx c 1
xxxxxxx d 0


then, the number of days under medicamentation is (b-a) + (d-c).

and the number of day WITHOUT medicamentation is then (d-a) - days under
medicamentation, or:

----------------------------------------
SELECT memberNumber,
MAX(theDate) - MIN(theDate) - SUM( iif(running =0, theDate, -theDate))
AS daysWithoutMed
FROM qRun
GROUP BY memberNumber
HAVING 0 = COUNT(*) MOD 2
----------------------------------------


where I added the extra condition as extra measure to be sure that the
intervals are all closed (else, the result would be erroneous).





Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Note that if I start a med on day a, and ends it at day b, then, I assumed
the number of day on med is

b-a

but it can be

1+b-a

dependant if the last day has to be included, or excluded, as example.
If so, change

SUM( iif(running =0, theDate, -theDate))


to

SUM( iif(running =0, 1+ theDate, -theDate))



Also, then, check if the total number of days is

MAX( ... ) - MIN( ... )

or

1+ MAX( ... ) - MIN( ... )




Vanderghast, Access MVP

 

[Simon L]

Thank you so much for your help, Michel! I am going out of town for a few
days and I will definitely try your suggestions as soon as I return to office
and let you know if it works!! Thanks again.

Simon

 

[Simon L]

Michel,
I am back from vacation and have looked at your suggestion. With my
extremely limited knowledge in SQL language, I have trouble understanding
your logic and what the queries were supposed to show me. May I present my
challenge once again?

The goal is to calculate a MPR (Medication Possession Ratio) for each mbr.
The data field that I have to work with are Mbr #, Fill Date, DS(Days
Supply). Based on the DS, I can calculate the Next Fill date (date the mbr
will be out of that medication)

The following was done on the Excel for one mbr. Keep in mind, this mbr
filled a number of medications in different dates throughout the time period.
The time period varies for each member. Each row represents one specific med
filled on that date.

The Days WO (without) med is calculated by comparing the Fill Date to the
maximum of previous Next Fill Date. For example, on 8/24/07, this mbr got a
rx for 90DS. Since 8/2/07 (not 6/16/07) was the first date this mbr would run
out of any med, therefore 22 days was calculated as Days WO med.

We total the Days WO med for each mbr (in this case 64), and the total days
between treatment (total lapse days between 1/6/07 and 1/26/08) = 385

The MPR for this mbr is calculated as (385-64)/385 or 0.83. What I am
looking for is a way to calculate the MPR for each of the mbrs in my database
whose treatment start and end dates are different. Sorry for the lengthy
description. Thanks

Mbr # Fill Date DS Next Fill date Days WO med
940183763-01 1/6/2007 30 2/5/2007
940183763-01 1/6/2007 30 2/5/2007 0
940183763-01 2/5/2007 30 3/7/2007 0
940183763-01 4/13/2007 30 5/13/2007 37
940183763-01 5/3/2007 25 5/28/2007 0
940183763-01 5/4/2007 90 8/2/2007 0
940183763-01 5/17/2007 30 6/16/2007 0
940183763-01 8/24/2007 90 11/22/2007 22
940183763-01 8/24/2007 30 9/23/2007 0
940183763-01 10/17/200730 11/16/2007 0
940183763-01 11/24/200730 12/24/2007 2
940183763-01 11/24/200730 12/24/2007 0
940183763-01 12/27/200730 1/26/2008 3
940183763-01 12/27/200730 1/26/2008 0
940183763-01 12/27/200730 1/26/2008 0

Simon

 

[Michel Walsh]

That is not what I understood the first time. Furthermore,


FillDate DS RefillDate DaysWithoutMed
8/24/2007 90 11/22/2007 22


does not seem right, since there is indeed 90 days between the two dates,
so, if the patient uses 1 DS a day, the patient would not be without med at
all.


Vanderghast, Access MVP

 

[Simon L]

Michel,
Sorry for the confusion. Please let me try to explain my logic. In the
example on 8/24/07, this mbr has been without any medication for 22 days
since the previous filled rx (filled on 5/4/07) would only lasted thru
8/2/07. Between 8/2/07 and 8/24/07, this mbr has no medication at all.

Simon

 

[Michel Walsh]

I see. So, somehow, it is a little bit like I saw it, initially, but
duplication added some unsuspected complexity. I won't be able to come with
a solution before Wednesday, though.


Vanderghast, Access MVP

 

[Michel Walsh]

Here a (simpler) solution.

From:

Meds
MemberID FillDate DS NextFillDate
940183763-01 2007.01.06 30 2007.02.05
940183763-01 2007.02.05 30 2007.03.07
940183763-01 2007.02.05 30 2007.03.07
940183763-01 2007.04.13 30 2007.05.13
940183763-01 2007.05.03 25 2007.05.28
940183763-01 2007.05.04 90 2007.08.02
940183763-01 2007.05.17 30 2007.06.16
940183763-01 2007.08.24 90 2007.11.22
940183763-01 2007.08.24 30 2007.09.23
940183763-01 2007.10.17 30 2007.11.16
940183763-01 2007.11.24 30 2007.12.24
940183763-01 2007.11.24 30 2007.12.24
940183763-01 2007.12.27 30 2008.01.26
940183763-01 2007.12.27 30 2008.01.26
940183763-01 2007.12.27 30 2008.01.26


First, we make two queries:

SELECT DISTINCT a.memberID, a.FillDate
FROM meds AS a
WHERE 0= (SELECT COUNT(*)
FROM meds AS b
WHERE b.FillDate <a.FillDate) -
(SELECT COUNT(*)
FROM meds AS c
WHERE c.nextFillDate < a.fillDate)


gives

MedsStartingSequence memberID FillDate
940183763-01 2007.01.06
940183763-01 2007.04.13
940183763-01 2007.08.24
940183763-01 2007.11.24
940183763-01 2007.12.27



and

SELECT DISTINCT a.memberID, a.NextFillDate
FROM meds AS a
WHERE 0=(SELECT COUNT(*)
FROM meds AS b
WHERE b.FillDate <= a.NextFillDate)
- (SELECT COUNT(*)
FROM meds AS c
WHERE c.NextFillDate<= a.NextFillDate)


returns

MedsEndingSequence memberID NextFillDate
940183763-01 2007.03.07
940183763-01 2007.08.02
940183763-01 2007.11.22
940183763-01 2007.12.24
940183763-01 2008.01.26



These two queries, together, defines the intervals that are relevant to the problem.

I make the following intermediate query:

SELECT memberID, -CDbl(MIN(FillDate)) AS z FROM MedsStartingSequence GROUP BY memberID
UNION ALL
SELECT memberID, CDbl(MAX(NextFillDate)) FROM MedsEndingSequence GROUP BY memberID
UNION ALL
SELECT memberID, CDbl(FillDate) FROM MedsStartingSequence
UNION ALL
SELECT memberID, -CDbl(NextFillDate) FROM MedsEndingSequence


MedsUnion memberID z
940183763-01 -39088
940183763-01 39473
940183763-01 39088
940183763-01 39185
940183763-01 39318
940183763-01 39410
940183763-01 39443
940183763-01 -39148
940183763-01 -39296
940183763-01 -39408
940183763-01 -39440
940183763-01 -39473



which has simply to be SUM:

SELECT MedsUnion.memberID, Sum(MedsUnion.z) AS SumOfz
FROM MedsUnion
GROUP BY MedsUnion.memberID;



to return the number of days without med:


DaysWithoutMed memberID SumOfz
940183763-01 64





Vanderghast, Access MVP

 

[Simon L]

Dear Michel,
Thank you so much for helping me with this. I tried the first two queries
and all I got in return was one single mbr with one single FillDate from the
starting sequence query and the same for the ending sequence query. I was
expecting multiple mbrs and fill dates.

Here is what was returned from the starting sequence query
memberID FillDate
940387753-01 1/1/2007

Return from the ending sequence query
memberID NextFillDate
940420560-02 4/12/2008

Looks like the first query was looking for the first date in the meds table
and the second query was looking for the last date in the meds table. I have
approx 223 mbrs with 1905 filldate records.

Did I miss some steps?

Simon

 

[Michel Walsh]

Indeed, two queries were not testing for the memberID:

Change MedsStartingSequence

SELECT DISTINCT a.memberID, a.FillDate
FROM meds AS a
WHERE 0= (SELECT COUNT(*)
FROM meds AS b
WHERE b.FillDate <a.FillDate) -
(SELECT COUNT(*)
FROM meds AS c
WHERE c.nextFillDate < a.fillDate)


to (note the WHERE clause of the two sub queries)

SELECT DISTINCT a.memberID, a.FillDate
FROM meds AS a
WHERE 0= (SELECT COUNT(*)
FROM meds AS b
WHERE a.member=b.memberID AND
b.FillDate <a.FillDate) -
(SELECT COUNT(*)
FROM meds AS c
WHERE a.member=c.memberID AND
c.nextFillDate < a.fillDate)


and, for MedsEndingSequence

SELECT DISTINCT a.memberID, a.NextFillDate
FROM meds AS a
WHERE 0=(SELECT COUNT(*)
FROM meds AS b
WHERE b.FillDate <= a.NextFillDate)
- (SELECT COUNT(*)
FROM meds AS c
WHERE c.NextFillDate<= a.NextFillDate)

to

SELECT DISTINCT a.memberID, a.NextFillDate
FROM meds AS a
WHERE 0=(SELECT COUNT(*)
FROM meds AS b
WHERE a.member=b.memberID AND
b.FillDate <= a.NextFillDate)
- (SELECT COUNT(*)
FROM meds AS c
WHERE a.member=c.memberID AND
c.NextFillDate<= a.NextFillDate)




Since I tested with only one memberID in my data, I failed to spot that
problem.



Vanderghast, Access MVP

 

[Simon L]

It worked!!! It worked!!! Thank you so much for your help. As I said I really
don't know anything about SQL to write queries like these. Thanks again,
Michel!

Simon

 

[Simon L]

Michel,
I wonder if I could ask for your help again. The queries that you suggested
worked extremely well with a small # of records. However, when I tried to run
the starting and ending sequences for a large # of records (>110,000), it
took forever to run. I fact, I never got the query done even after a whole
day so I had to abort. Is there anyway to speed up the queries, especially
the starting and ending sequence queries? Thanks again!

 

[Michel Walsh]

There are many things that can be tried, in that case, yes. To make this
query faster:


SELECT DISTINCT a.memberID, a.NextFillDate
FROM meds AS a
WHERE 0=(SELECT COUNT(*)
FROM meds AS b
WHERE a.member=b.memberID AND
b.FillDate <= a.NextFillDate)
- (SELECT COUNT(*)
FROM meds AS c
WHERE a.memberID=c.memberID AND
c.NextFillDate<= a.NextFillDate)




we can try to precompute the counts into temporary tables. As example, if we
make a temp table with:

SELECT a.memberID, a.NextFillDate, COUNT(*) AS sq1Count
FROM meds AS a INNER JOIN meds AS c
ON a.memberID=c.memberID
AND a.NextFillDate >= c.NextFillDate
GROUP BY a.memberID, a.NextFillDate


let us call the table tempCountNextFill

then


SELECT DISTINCT a.memberID, a.NextFillDate
FROM meds AS a
WHERE 0=(SELECT COUNT(*)
FROM meds AS b
WHERE a.member=b.memberID AND
b.FillDate <= a.NextFillDate)
- (SELECT sq1Count
FROM tempCountNextFill AS c
WHERE a.memberID=c.memberID AND
c.NextFillDate= a.NextFillDate)



should be much faster since now we computed the COUNT(*) just once, rather
than computing it on the fly, each time we need it. Sure, we will have to
replace all the similar SELECT COUNT(*) to be really faster. As example, for
the other sub query (I have not tested that new formulation, though)


SELECT a.memberID, a.NextFillDate, COUNT(*) AS sq2Count
FROM meds AS a INNER JOIN meds AS b
ON a.memberID = b.memberID
AND b.fillDate <= a.NextFillDate
GROUP BY a.memberID, a.NextFillDate

saved into a table as tempCountFillDate

then, unless I miss some point, the first query can be re-written as:

SELECT DISTINCT a.memberID, a.NextFillDate
FROM meds AS a
WHERE 0=(SELECT sq2Count
FROM tempCountFillDateAS b
WHERE a.member=b.memberID AND
b.NextFillDate =a.NextFillDate)
- (SELECT sq1Count
FROM tempCountNextFill AS c
WHERE a.memberID=c.memberID AND
c.NextFillDate= a.NextFillDate)



and our two initial COUNT(*) now become simple lookups.


I may be wrong, though. Better to test that new solution (on a small data
set) before continuing for a global change. It will also show if the
modifications do, indeed, improve the execution time.


Vanderghast, Access MVP

 

[Simon L]

Michel,
Thanks again for your help and quick response. I will test what you have
suggested and let you know.

Simon

 

[Simon L]

Michel,
I am somewhat confused. Please let me know if I understand correctly what
you meant.

First I will make two temporary tables:
1st Table TempCountFillDate as follow:

SELECT a.memberID, a.NextFillDate, COUNT(*) AS sq2Count
FROM meds AS a INNER JOIN meds AS b
ON a.memberID = b.memberID
AND b.fillDate <= a.NextFillDate
GROUP BY a.memberID, a.NextFillDate

2nd Table TempCountNextFill as follow:

SELECT a.memberID, a.NextFillDate, COUNT(*) AS sq1Count
FROM meds AS a INNER JOIN meds AS c
ON a.memberID=c.memberID
AND a.NextFillDate >= c.NextFillDate
GROUP BY a.memberID, a.NextFillDate

Then
For the original MedStartingSequence query:

SELECT DISTINCT a.memberID, a.FillDate
FROM meds AS a
WHERE 0= (SELECT COUNT(*)
FROM meds AS b
WHERE a.memberID=b.memberID AND
b.FillDate <a.FillDate) -
(SELECT COUNT(*)
FROM meds AS c
WHERE a.member=c.memberID AND
c.nextFillDate < a.fillDate)

I will change it to

SELECT DISTINCT a.memberID, a.NextFillDate
FROM meds AS a
WHERE 0=(SELECT sq2Count
FROM tempCountFillDateAS b
WHERE a.member=b.memberID AND
b.NextFillDate =a.NextFillDate)
- (SELECT sq1Count
FROM tempCountNextFill AS c
WHERE a.memberID=c.memberID AND
c.NextFillDate= a.NextFillDate)

For the original MedEndingSequence query:

SELECT DISTINCT a.memberID, a.NextFillDate
FROM meds AS a
WHERE 0=(SELECT COUNT(*)
FROM meds AS b
WHERE a.member=b.memberID AND
b.FillDate <= a.NextFillDate)
- (SELECT COUNT(*)
FROM meds AS c
WHERE a.member=c.memberID AND
c.NextFillDate<= a.NextFillDate)

I will change it to:

SELECT DISTINCT a.memberID, a.NextFillDate
FROM meds AS a
WHERE 0=(SELECT COUNT(*)
FROM meds AS b
WHERE a.member=b.memberID AND
b.FillDate <= a.NextFillDate)
- (SELECT sq1Count
FROM tempCountNextFill AS c
WHERE a.memberID=c.memberID AND
c.NextFillDate= a.NextFillDate)

What is confusing to me is the new MedStartingSequence query is referencing
to both temp tables.

Will I be running the new MedStartingSequence query first, then the new
MedEndingSequence query, followed by the MedUnion query?

Thanks

Simon

 

[Michel Walsh]

You make the temporary tables, first, on the small data set. Then, you run
the new MedStartingSequence on the small data set to see if it gives the
same result (as it should).

Next, you repeat on the real data set, but instead of checking the validity
of the result, you check the time it takes to run. Indeed, if it takes as
much time as previously, that is useless to continue in this direction.



Hoping it may help,
Vanderghast, Access MVP

 

[Simon L]

Michel,
Thanks again for the response.
I did test the two temp tables on a small set of data and both tables
returned the results. However when I tried to run the starting sequence using
the following query, it gave me an error message, " At most one record can be
returned by this query." What did I do wrong?

SELECT DISTINCT a.memberID, a.NextFillDate
FROM [Test 1] AS a
WHERE 0=(SELECT sq2Count
FROM tempCountFillDate AS b
WHERE a.memberID=b.memberID AND
b.NextFillDate =a.NextFillDate)
- (SELECT sq1Count
FROM tempCountNextFill AS c
WHERE a.memberID=c.memberID AND
c.NextFillDate= a.NextFillDate)
Simon

 

[Michel Walsh]

That is bad news, we should have only one record for a given memberID and a
given NextFillDate. Hoping for the best, but I suspect the result may not be
right, try:


SELECT DISTINCT a.memberID, a.NextFillDate
FROM [Test 1] AS a
WHERE 0=(SELECT MAX(sq2Count)
FROM tempCountFillDate AS b
WHERE a.memberID=b.memberID AND
b.NextFillDate =a.NextFillDate)
- (SELECT MAX(sq1Count)
FROM tempCountNextFill AS c
WHERE a.memberID=c.memberID AND
c.NextFillDate= a.NextFillDate)



Vanderghast, Access MVP

Michel,
Thanks again for the response.
I did test the two temp tables on a small set of data and both tables
returned the results. However when I tried to run the starting sequence
using
the following query, it gave me an error message, " At most one record can
be
returned by this query." What did I do wrong?

SELECT DISTINCT a.memberID, a.NextFillDate
FROM [Test 1] AS a
WHERE 0=(SELECT sq2Count
FROM tempCountFillDate AS b
WHERE a.memberID=b.memberID AND
b.NextFillDate =a.NextFillDate)
- (SELECT sq1Count
FROM tempCountNextFill AS c
WHERE a.memberID=c.memberID AND
c.NextFillDate= a.NextFillDate)
Simon

You make the temporary tables, first, on the small data set. Then, you
run
the new MedStartingSequence on the small data set to see if it gives the
same result (as it should).

Next, you repeat on the real data set, but instead of checking the
validity
of the result, you check the time it takes to run. Indeed, if it takes as
much time as previously, that is useless to continue in this direction.



Hoping it may help,
Vanderghast, Access MVP

 

[Simon L]

Michel,
The Starting Sequenc query that you just suggested worked and returned with
1546 records. Some sample records here:

memberID NextFillDate
940070653-01 10/21/2006
940070653-01 11/30/2006
940070653-01 12/16/2006
940070653-01 1/1/2007
940070653-01 1/17/2007
940076306-01 3/4/2006
940076306-01 4/8/2006
940076306-01 5/17/2006
940076306-01 6/24/2006
940076306-01 8/1/2006
940076306-01 9/12/2006
940076306-01 10/18/2006
940076306-01 11/21/2006
940076306-01 12/25/2006

I tried the Ending sequence with the following query but I got the same
error message of " At most one record can be returned by this query."

SELECT DISTINCT a.memberID, a.NextFillDate
FROM [Test 1] AS a
WHERE 0=(SELECT COUNT(*)
FROM [Test 1] AS b
WHERE a.memberID=b.memberID AND
b.FillDate <= a.NextFillDate)
- (SELECT sq1Count
FROM tempCountNextFill AS c
WHERE a.memberID=c.memberID AND
c.NextFillDate= a.NextFillDate)

If I changed it to the following, I got zero record return.

SELECT DISTINCT a.memberID, a.NextFillDate
FROM [Test 1] AS a
WHERE 0=(SELECT COUNT(*)
FROM [Test 1] AS b
WHERE a.memberID=b.memberID AND
b.FillDate <= a.NextFillDate)
- (SELECT MAX(sq1Count)
FROM tempCountNextFill AS c
WHERE a.memberID=c.memberID AND
c.NextFillDate= a.NextFillDate)

I feel like we are getting close. Please bear with me as I am trying to
learn this. I truly appreciate your help.

Simon

That is bad news, we should have only one record for a given memberID and a
given NextFillDate. Hoping for the best, but I suspect the result may not be
right, try:


SELECT DISTINCT a.memberID, a.NextFillDate
FROM [Test 1] AS a
WHERE 0=(SELECT MAX(sq2Count)
FROM tempCountFillDate AS b
WHERE a.memberID=b.memberID AND
b.NextFillDate =a.NextFillDate)
- (SELECT MAX(sq1Count)
FROM tempCountNextFill AS c
WHERE a.memberID=c.memberID AND
c.NextFillDate= a.NextFillDate)



Vanderghast, Access MVP

Michel,
Thanks again for the response.
I did test the two temp tables on a small set of data and both tables
returned the results. However when I tried to run the starting sequence
using
the following query, it gave me an error message, " At most one record can
be
returned by this query." What did I do wrong?

SELECT DISTINCT a.memberID, a.NextFillDate
FROM [Test 1] AS a
WHERE 0=(SELECT sq2Count
FROM tempCountFillDate AS b
WHERE a.memberID=b.memberID AND
b.NextFillDate =a.NextFillDate)
- (SELECT sq1Count
FROM tempCountNextFill AS c
WHERE a.memberID=c.memberID AND
c.NextFillDate= a.NextFillDate)
Simon

You make the temporary tables, first, on the small data set. Then, you
run
the new MedStartingSequence on the small data set to see if it gives the
same result (as it should).

Next, you repeat on the real data set, but instead of checking the
validity
of the result, you check the time it takes to run. Indeed, if it takes as
much time as previously, that is useless to continue in this direction.



Hoping it may help,
Vanderghast, Access MVP