I have worked on this for hours and can't come up with a solution. Hope
someone can help me.
I have a table called TMBS_HMAUDIT_PARMS. this table contains data to
tell me how often a person is allowed to receive services for various
programs we offer. columns in this table include PROGRAM_NAME,
PROGRAM_ID, ALLOWED_PER_MONTHS. In the ALLOWED_PER_MONTHS column, a
numerical value of '9' in the 'ALLOWED PER MONTHS' column means a
person is allowed to receive 1 service every 9 months. A numerical
value of '3' means a person can receive a service every 3 months.
I am trying to join this up with the SERVICES table to audit a person's
usage of services under whatever program. I only want results where a
person has used too many services. This table contains actual dates the
services have been received.
my original query
WHERE A.FIRST_DOS BETWEEN
(B.FIRST_DOS)-(ALLOW_PER_MONTHS) MONTHS AND
(B.FIRST_DOS)+(ALLOW_PER_MONTHS)MONTHS
(note: b.FIRST_DOS is the new service. All of the current services are
in the 'A' table)
works almost, except I am getting results where services are exactly 1
month apart, such as, one service being 7-1-05 and the next being
8-1-05. I want to drop this scenario. I have decided that I want to
give a 5 day allowance, so a person who received services on 7-1-05 is
eligible again on:
7-1-05 plus (1 month) minus (5 days).
the '1' month value is what is contained in the TMBS_HMAUDIT_PARMS
table, ALLOWED_PER_MONTHS column.
Best thing I can come up with is to try and explain what I need is
WHERE A.FIRST_DOS BETWEEN
(B.FIRST_DOS)-((ALLOW_PER_MONTHS)+5 days)MONTHS AND
(B.FIRST_DOS)+((ALLOW_PER_MONTHS)-5 days)MONTHS
but of course this does not work. I get an error message:
An arithmetic expression with a datetime value is invalid.
I have also tried
AND
((B.FIRST_DOS <A.FIRST_DOS - ALLOW_PER_MONTHS + 5 DAYS)
OR (B.FIRST_DOS >A.FIRST_DOS + ALLOW_PER_MONTHS - 5 DAYS))
I know this is kind of confusing, but I hope someone can help. Thanks