Having trouble implementing complicated business rules.

The company I work for is a managed healthcare organization. One
particular metric we use for determining compensation for a medical
practice in our network is tracking the number of times a particular
practice sees a particular patient (member of the healthcare network).
Practices are awarded one "point" for each such occurrance within a
given timeframe, and compensated according to the number of total
points they accumulate for all network members.
As such, each month, I must take the medical claims filed for that
month and traverse through them in chronological order for each
member/practice combination, a "point" for each instance, but only one
per combination in, for example, a three month period. Therefore, if
practice A sees member 1 on 2006-01-01, then practice A would be
awarded one point on 2006-01-01, and would not be eligible for another
point for seeing member 1 until 2006-04-01.
Traversing the ordered data set is the easy part. I utilize a cursor,
note the last point date for each member/practice combination involved,
and then loop through new claims, awarding points for any claim that is
at least three months from the date the last point was awarded. The
problem I am having is correctly building that data set (cursor). This
is due to certain specific business rules I will list below. Here is a
simplified representation of the data involved:
-- Medical Practices:
CREATE TABLE Practices
(
PracticeID VARCHAR(6),
SpecialtyID INT
)
INSERT Practices VALUES ('A', 10)
INSERT Practices VALUES ('B', 10)
INSERT Practices VALUES ('C', 10)
INSERT Practices VALUES ('D', 21)
INSERT Practices VALUES ('E', 21)
INSERT Practices VALUES ('F', 21)
INSERT Practices VALUES ('G', 45)
-- Practices Splits (occasionally, one practice
-- will split up into multiple newer practices,
-- or multiple practices will merge into one newer
-- practice):
CREATE TABLE PracticeSplits
(
PracticeID_OLD VARCHAR(6),
PRacticeID_NEW VARCHAR(6)
)
INSERT PracticeSplits VALUES ('A', 'B')
INSERT PracticeSplits VALUES ('A', 'C')
INSERT PracticeSplits VALUES ('D', 'F')
INSERT PracticeSplits VALUES ('E', 'F')
-- SpecialtyExceptions (will explain below):
CREATE TABLE SpecialtyExceptions
(
SpecialtyID INT
)
INSERT SpecialtyExceptions VALUES (10)
-- Medical Claims:
CREATE TABLE Claims
(
ClaimID VARCHAR(16),
MemberID INT,
PracticeID VARCHAR(6),
VisitDate DATETIME
)
INSERT Claims VALUES ('200600001', 1, 'A', '2006-01-01')
INSERT Claims VALUES ('200600002', 1, 'A', '2006-02-11')
INSERT Claims VALUES ('200600003', 1, 'A', '2006-03-01')
INSERT Claims VALUES ('200600004', 1, 'A', '2006-03-30')
INSERT Claims VALUES ('200600005', 2, 'A', '2006-02-01')
INSERT Claims VALUES ('200600006', 2, 'B', '2006-01-01')
INSERT Claims VALUES ('200600007', 2, 'B', '2006-01-01')
INSERT Claims VALUES ('200600008', 2, 'C', '2006-03-01')
INSERT Claims VALUES ('200600009', 3, 'A', '2006-02-01')
INSERT Claims VALUES ('2006000010', 3, 'c', '2006-02-21')
INSERT Claims VALUES ('2006000011', 4, 'D', '2006-02-24')
INSERT Claims VALUES ('2006000012', 4, 'E', '2006-03-01')
INSERT Claims VALUES ('2006000013', 4, 'E', '2006-04-26')
INSERT Claims VALUES ('2006000014', 5, 'F', '2006-01-11')
INSERT Claims VALUES ('2006000015', 5, 'A', '2006-05-11')
INSERT Claims VALUES ('2006000016', 5, 'G', '2006-05-14')
Here are the rules:
1) In determining the date of the last point awarded, practice
splits must be considered. In this scenario, if practice A was
awarded a point on '2006-01-01', then neither practice A nor
practices B nor C may receive a point for the next three months.
2) Certain specialties are handled as exceptions, in that only
one point shall be awarded to *any* practice of the same
specialty where that specialty is represented in the
SpecialtyExceptions table. In the above scenario, if practice A
is awarded a point for seeing member 1 on 2006-01-01, then no
other practice with a specialty ID of 10 may receive a point for
seeing member 1 until 2006-04-01.
PROBLEM: Produce a data set consisting of all claims for each
network member, for each practice that would reflect the following
data, thereby facilitating the awarding of visit points in
accordance with the aforementioned business rules.
ClaimID, MemberID, PracticeID, VisitDate, PriorMaxVisitDate,
SpecialtyID
If I've been unclear on anything, please let me know. The schema was
developed by a contractor some time ago. While I would be open to
suggestions on any possible improvements in that regard, I'm not
exactly itching to re-write any more of it that might be absolutely
necessary.A couple of questions...
First, regarding this "one point within a time frame" rule, can you
elaborate on that? Is it one point per quarter per member, or must there be
a 3 month window? If Member 1 has an appointment on 2006-02-17 and another
apointment on 2006-04-01, will you count the later appointment for the month
of April, or exclude it because there was already an appointment in the last
3 months? If you are expluding it, do you do so based on 3 calendar months,
or 90 days?
In your sample data, specialties and practice splits are equivilant.
Meaning all practices in specialty 10 (A,B,C) are split, and all practices
in Specialty 21 (D,E,F) are also split. Is this just coincidence in your
test data, or are practices always split for a given specialty? If so, both
the PracticeSplits and the SpecialtyExceptions are redundant. Also, in the
case of specialty 10 being an exception, because these practices are already
split, this is redundant. Again, if this is merely a coincidence then it is
fine.
If you really have redundant rules built into different tables, then you
will want to change this to minimize the extraneous data, and simplify the
rules. In the mean time, you should be able to get all this information in
a single select without looping through an ordered set.
"Richard Carpenter" <rumbledor@.hotmail.com> wrote in message
news:1148409474.980326.150370@.j33g2000cwa.googlegroups.com...
> The company I work for is a managed healthcare organization. One
> particular metric we use for determining compensation for a medical
> practice in our network is tracking the number of times a particular
> practice sees a particular patient (member of the healthcare network).
> Practices are awarded one "point" for each such occurrance within a
> given timeframe, and compensated according to the number of total
> points they accumulate for all network members.
> As such, each month, I must take the medical claims filed for that
> month and traverse through them in chronological order for each
> member/practice combination, a "point" for each instance, but only one
> per combination in, for example, a three month period. Therefore, if
> practice A sees member 1 on 2006-01-01, then practice A would be
> awarded one point on 2006-01-01, and would not be eligible for another
> point for seeing member 1 until 2006-04-01.
> Traversing the ordered data set is the easy part. I utilize a cursor,
> note the last point date for each member/practice combination involved,
> and then loop through new claims, awarding points for any claim that is
> at least three months from the date the last point was awarded. The
> problem I am having is correctly building that data set (cursor). This
> is due to certain specific business rules I will list below. Here is a
> simplified representation of the data involved:
> -- Medical Practices:
> CREATE TABLE Practices
> (
> PracticeID VARCHAR(6),
> SpecialtyID INT
> )
> INSERT Practices VALUES ('A', 10)
> INSERT Practices VALUES ('B', 10)
> INSERT Practices VALUES ('C', 10)
> INSERT Practices VALUES ('D', 21)
> INSERT Practices VALUES ('E', 21)
> INSERT Practices VALUES ('F', 21)
> INSERT Practices VALUES ('G', 45)
> -- Practices Splits (occasionally, one practice
> -- will split up into multiple newer practices,
> -- or multiple practices will merge into one newer
> -- practice):
> CREATE TABLE PracticeSplits
> (
> PracticeID_OLD VARCHAR(6),
> PRacticeID_NEW VARCHAR(6)
> )
> INSERT PracticeSplits VALUES ('A', 'B')
> INSERT PracticeSplits VALUES ('A', 'C')
> INSERT PracticeSplits VALUES ('D', 'F')
> INSERT PracticeSplits VALUES ('E', 'F')
> -- SpecialtyExceptions (will explain below):
> CREATE TABLE SpecialtyExceptions
> (
> SpecialtyID INT
> )
> INSERT SpecialtyExceptions VALUES (10)
> -- Medical Claims:
> CREATE TABLE Claims
> (
> ClaimID VARCHAR(16),
> MemberID INT,
> PracticeID VARCHAR(6),
> VisitDate DATETIME
> )
> INSERT Claims VALUES ('200600001', 1, 'A', '2006-01-01')
> INSERT Claims VALUES ('200600002', 1, 'A', '2006-02-11')
> INSERT Claims VALUES ('200600003', 1, 'A', '2006-03-01')
> INSERT Claims VALUES ('200600004', 1, 'A', '2006-03-30')
> INSERT Claims VALUES ('200600005', 2, 'A', '2006-02-01')
> INSERT Claims VALUES ('200600006', 2, 'B', '2006-01-01')
> INSERT Claims VALUES ('200600007', 2, 'B', '2006-01-01')
> INSERT Claims VALUES ('200600008', 2, 'C', '2006-03-01')
> INSERT Claims VALUES ('200600009', 3, 'A', '2006-02-01')
> INSERT Claims VALUES ('2006000010', 3, 'c', '2006-02-21')
> INSERT Claims VALUES ('2006000011', 4, 'D', '2006-02-24')
> INSERT Claims VALUES ('2006000012', 4, 'E', '2006-03-01')
> INSERT Claims VALUES ('2006000013', 4, 'E', '2006-04-26')
> INSERT Claims VALUES ('2006000014', 5, 'F', '2006-01-11')
> INSERT Claims VALUES ('2006000015', 5, 'A', '2006-05-11')
> INSERT Claims VALUES ('2006000016', 5, 'G', '2006-05-14')
> Here are the rules:
> 1) In determining the date of the last point awarded, practice
> splits must be considered. In this scenario, if practice A was
> awarded a point on '2006-01-01', then neither practice A nor
> practices B nor C may receive a point for the next three months.
> 2) Certain specialties are handled as exceptions, in that only
> one point shall be awarded to *any* practice of the same
> specialty where that specialty is represented in the
> SpecialtyExceptions table. In the above scenario, if practice A
> is awarded a point for seeing member 1 on 2006-01-01, then no
> other practice with a specialty ID of 10 may receive a point for
> seeing member 1 until 2006-04-01.
> PROBLEM: Produce a data set consisting of all claims for each
> network member, for each practice that would reflect the following
> data, thereby facilitating the awarding of visit points in
> accordance with the aforementioned business rules.
> ClaimID, MemberID, PracticeID, VisitDate, PriorMaxVisitDate,
> SpecialtyID
> If I've been unclear on anything, please let me know. The schema was
> developed by a contractor some time ago. While I would be open to
> suggestions on any possible improvements in that regard, I'm not
> exactly itching to re-write any more of it that might be absolutely
> necessary.
>|||Jim Underwood wrote:
> A couple of questions...
> First, regarding this "one point within a time frame" rule, can you
> elaborate on that? Is it one point per quarter per member, or must there
be
> a 3 month window? If Member 1 has an appointment on 2006-02-17 and anothe
r
> apointment on 2006-04-01, will you count the later appointment for the mon
th
> of April, or exclude it because there was already an appointment in the la
st
> 3 months? If you are expluding it, do you do so based on 3 calendar month
s,
> or 90 days?
It is based on a three month window and pertains to a particular member
visit with a particular practice. For example, of the claims reviewed
for the current month, a particular practice could be awarded ten visit
points provided they saw ten different members, none of whom had
visited that practice (or any split-related practice, or any specialty
excepted practice) within the past three months.

> In your sample data, specialties and practice splits are equivilant.
> Meaning all practices in specialty 10 (A,B,C) are split, and all practices
> in Specialty 21 (D,E,F) are also split. Is this just coincidence in your
> test data, or are practices always split for a given specialty? If so, bo
th
> the PracticeSplits and the SpecialtyExceptions are redundant. Also, in th
e
> case of specialty 10 being an exception, because these practices are alrea
dy
> split, this is redundant. Again, if this is merely a coincidence then it
is
> fine.
It is, in fact, merely coincidental of my hastily compiled test data.
There is no relationship between practice splits and specialties.

> If you really have redundant rules built into different tables, then you
> will want to change this to minimize the extraneous data, and simplify the
> rules. In the mean time, you should be able to get all this information i
n
> a single select without looping through an ordered set.
Well, there are additional requirements that prevent handling this
through query. For starters, it is possible for a claim to be reversed
(voided), thereby rendering all visit points from the visit date on
that claim forward for that practice/member invalid, requiring the
process to reconsider and reestablish visit points for all subsequent
claim data.
I *would* be interested to know how I might be able to establish these
visit points on a one-per-three-month basis through standard query
while adhering to these business rules, though. Maybe I *could* make
that work after all.|||OK, this solution may be more complicated than it needs to be, but I think I
was able to get the results as a distinct list of "point worthy" encounters.
If the list is accurate, you can just change the select clause to
PracticeID, Count(*)
and group by practiceID
I created a cross referencing view on Practice Splits so that D and E would
be mutually exclusive, along with B and C. I assumed this is how you wanted
it to work, just remove the view and access the table directly if this is
not the case.
I also assumed that you wanted results by quarter, or rather wanted to
exclude points based on an earlier encounter with the same patient in the
particular quarter. I used 4 datetime variables, 2 to control the time
frame for which you are counting points, and 2 to control the timeframe for
which you are checking to see if points were already awarded.
You may be better off looping through this data in procedural code, if that
procedural code is much easier to follow. The multiple not exists and
subqueries make this a little ugly.
/*
Create a view to cross reference practice splits using the transitive
property
if A splits with B and A splits with C then B will split with C
This also duplicates the date with columns reversed for ease of joining
later on
*/
create view PracticeSplit_vw as
select b.PracticeID_NEW as PracticeID_OLD, c.PracticeID_NEW
from PracticeSplits a
inner join PracticeSplits b
on a.PracticeID_OLD = b.PracticeID_OLD
inner join PracticeSplits c
on b.PracticeID_OLD = c.PracticeID_OLD
and b.PracticeID_new <> c.practiceID_new
union
select b.PracticeID_OLD as PracticeID_OLD, c.PracticeID_OLD
from PracticeSplits a
inner join PracticeSplits b
on a.PracticeID_NEW = b.PracticeID_NEW
inner join PracticeSplits c
on b.PracticeID_NEW = c.PracticeID_NEW
and b.PracticeID_OLD <> c.PracticeID_OLD
union
select A.PracticeID_OLD, a.PracticeID_NEW
from PracticeSplits a
union
select A.PracticeID_NEW, a.PracticeID_OLD
from PracticeSplits a;
go
Declare @.PeriodStart as datetime
Declare @.PeriodEnd as datetime
Declare @.QuarterStart as datetime
Declare @.QuarterEnd as datetime
set @.PeriodStart = '2006-01-01'
set @.PeriodEnd = '2006-04-01'
set @.QuarterStart = '2006-01-01'
set @.QuarterEnd = '2006-04-01'
Select *
from practices as Prac
inner join claims as Claim
on Prac.PracticeID = Claim.PracticeID
where Claim.VisitDate >= @.PeriodStart
and Claim.VisitDate < @.PeriodEnd
/*
exclude patients already seen this period by this provider
*/
and not exists
(
select 1 from claims as Claim1
where
(
Claim1.VisitDate < Claim.VisitDate
or
(
Claim1.VisitDate = Claim.VisitDate
and claim1.claimID < claim.claimID
)
)
and claim1.memberID = claim.memberID
and claim1.PracticeID = claim.PracticeID
and Claim1.VisitDate >= @.QuarterStart
and Claim1.VisitDate < @.QuarterEnd
)
and not exists
/*
exclude patients for Practice Splits
*/
(
select 1 from claims as Claim1
where
(
Claim1.VisitDate < Claim.VisitDate
or
(
Claim1.VisitDate = Claim.VisitDate
and claim1.claimID < claim.claimID
)
)
and claim1.memberID = claim.memberID
and claim1.PracticeID in
(
select split.PracticeID_NEW
from PracticeSplit_vw as split
where split.PracticeID_OLD = claim.PracticeID
)
and Claim1.VisitDate >= @.QuarterStart
and Claim1.VisitDate < @.QuarterEnd
)
and not exists
/*
exclude patients for specialty exceptions
*/
(
select 1 from claims as Claim1
where
(
Claim1.VisitDate < Claim.VisitDate
or
(
Claim1.VisitDate = Claim.VisitDate
and claim1.claimID < claim.claimID
)
)
and claim1.memberID = claim.memberID
and Prac.SpecialtyID in
(
select spec.SpecialtyID
from SpecialtyExceptions as spec
where spec.SpecialtyID = Prac.SpecialtyID
)
and Claim1.VisitDate >= @.QuarterStart
and Claim1.VisitDate < @.QuarterEnd
)|||Take a look at what I posted and see if it is manageable (and if it works at
all on real data). If it works as it is, we may be able to add another
check to it to account for voided claims. Actually, we can definately do
it, it just may be more trouble than it is worth.
"Richard Carpenter" <rumbledor@.hotmail.com> wrote in message
news:1148415117.269330.20150@.j73g2000cwa.googlegroups.com...
> Jim Underwood wrote:
there be
another
month
last
months,
> It is based on a three month window and pertains to a particular member
> visit with a particular practice. For example, of the claims reviewed
> for the current month, a particular practice could be awarded ten visit
> points provided they saw ten different members, none of whom had
> visited that practice (or any split-related practice, or any specialty
> excepted practice) within the past three months.
>
practices
your
both
the
already
it is
> It is, in fact, merely coincidental of my hastily compiled test data.
> There is no relationship between practice splits and specialties.
>
the
in
> Well, there are additional requirements that prevent handling this
> through query. For starters, it is possible for a claim to be reversed
> (voided), thereby rendering all visit points from the visit date on
> that claim forward for that practice/member invalid, requiring the
> process to reconsider and reestablish visit points for all subsequent
> claim data.
> I *would* be interested to know how I might be able to establish these
> visit points on a one-per-three-month basis through standard query
> while adhering to these business rules, though. Maybe I *could* make
> that work after all.
>|||This is similar to the approach I was taking, though I was using the
code to produce a cursor to scroll through and determine the visit
points. Also, as far as the practice splits go, if practice A splits
into practices B and C, there is no relationship between B and C with
regard to visit points.
One point of note, however, is that, although these points would be
tabulated every month, the "new" data may consist of claims that are
months apart, due to the nature of health care claims processing. We
may receive claims that were erroneously filed with the wrong payor(s)
and bounced around for some time before finally making it to us. As
such, there must be a means of handling the possibility of a particular
practice/patient combination being represented more than once in a
given month (new period), yet only receiving one point unless those
visits were actually more than, say, the pre-established period lenght
of three months apart. That was where I found the need for scrolling
through the cursor to compare all new claims chronologically. In
reviewing your code, it appears that visit points would be awarded to
*every* new practice/patient combination for which a previous instance
had not been represented within that given timeframe.
For the sake of clarity, I haven't provided the entire scope for this
problem, though the missing pieces aren't part of the core process. For
example, the time period in question is actually dependent upon the
specialty of the practice - some are three months, but most are six.
This is referenced through a one-to-one relationship with a Specialties
table.
You have, however, brought to light a couple of areas where I could
possibly have been more efficient. I will try and incorporate those
approaches into the current process. I thank you so much for your time
to this point. You have been extremely helpful.|||Your rules do sound quite complex. While I am certain they could be
resolved with straight SQL, procedural code will probably work better,
simply because it will be easier to follow. The effort that would go into
making it work with straight SQL probably would not be worth it, and no one
would be able to maintain the resulting SQL should problems arise.
Thank you for clarifying how practice splits work. It makes that particular
function a little simpler, although your other requirements certainly
complicate things. Once you get all your logic working, you might post the
final code here and see if anyone can consolidate it, just for kicks. I am
rather interested in seeing your final results.
"Richard Carpenter" <rumbledor@.hotmail.com> wrote in message
news:1148564860.790963.269430@.38g2000cwa.googlegroups.com...
> This is similar to the approach I was taking, though I was using the
> code to produce a cursor to scroll through and determine the visit
> points. Also, as far as the practice splits go, if practice A splits
> into practices B and C, there is no relationship between B and C with
> regard to visit points.
> One point of note, however, is that, although these points would be
> tabulated every month, the "new" data may consist of claims that are
> months apart, due to the nature of health care claims processing. We
> may receive claims that were erroneously filed with the wrong payor(s)
> and bounced around for some time before finally making it to us. As
> such, there must be a means of handling the possibility of a particular
> practice/patient combination being represented more than once in a
> given month (new period), yet only receiving one point unless those
> visits were actually more than, say, the pre-established period lenght
> of three months apart. That was where I found the need for scrolling
> through the cursor to compare all new claims chronologically. In
> reviewing your code, it appears that visit points would be awarded to
> *every* new practice/patient combination for which a previous instance
> had not been represented within that given timeframe.
> For the sake of clarity, I haven't provided the entire scope for this
> problem, though the missing pieces aren't part of the core process. For
> example, the time period in question is actually dependent upon the
> specialty of the practice - some are three months, but most are six.
> This is referenced through a one-to-one relationship with a Specialties
> table.
> You have, however, brought to light a couple of areas where I could
> possibly have been more efficient. I will try and incorporate those
> approaches into the current process. I thank you so much for your time
> to this point. You have been extremely helpful.
>