Calculating Days/Months

bufbec

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

Dec 2 '05 #1

Rhino

"bufbec" <ka********@accesstoledo.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

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

There are a variety of date functions in DB2, all of which are described in
the Information Center for your particular version/platform of DB2. There
are also techniques for doing adding and subtracting increments from dates.
If you search the manuals on "date arithmetic" or similar terms, you may
very well find some examples that show you the right approach. However, if
you fail to find what you want or fail to understand it, post back and
someone will probably be able to help you.

When and if you do come back, it would be very helpful to us to know the
answers to these questions; in fact it is very difficult to attempt an
answer WITHOUT knowing these things:

1. What platform are you on: DB2 for z/OS? DB2 for Linus/Unix/Windows?
other?

2. What version of DB2 are you using?

3. How is FIRST_DOS defined, i.e. what is the DB2 datatype of the column in
which it is stored? You refer to dates as 7-1-05 which is NOT the normal
format for dates in DB2 and that has me wondering if you're using something
other than DATE or TIMESTAMP to store FIRST_DOS. That may be a big problem
but it affects any answer that we give.

Rhino

Dec 2 '05 #2

Tonkuma

WHERE A.FIRST_DOS BETWEEN
B.FIRST_DOS - ALLOW_PER_MONTHS MONTHS + 5 DAYS AND
B.FIRST_DOS + ALLOW_PER_MONTHS MONTHS - 5 DAYS

Dec 3 '05 #3