467,175 Members | 1,316 Online

# Calculating Days/Months

 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)) I know this is kind of confusing, but I hope someone can help. Thanks Dec 2 '05 #1
• viewed: 2582
Share:
2 Replies