473,569 Members | 2,664 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_PA RMS. 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_MON THS. In the ALLOWED_PER_MON THS 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_MONT HS) MONTHS AND
(B.FIRST_DOS)+( ALLOW_PER_MONTH S)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_PA RMS
table, ALLOWED_PER_MON THS 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_MON THS)+5 days)MONTHS AND
(B.FIRST_DOS)+( (ALLOW_PER_MONT HS)-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_MONTH S + 5 DAYS)
OR (B.FIRST_DOS >A.FIRST_DOS + ALLOW_PER_MONTH S - 5 DAYS))

I know this is kind of confusing, but I hope someone can help. Thanks

Dec 2 '05 #1
2 2944

"bufbec" <ka********@acc esstoledo.com> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.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_PA RMS. 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_MON THS. In the ALLOWED_PER_MON THS 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_MONT HS) MONTHS AND
(B.FIRST_DOS)+( ALLOW_PER_MONTH S)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_PA RMS
table, ALLOWED_PER_MON THS 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_MON THS)+5 days)MONTHS AND
(B.FIRST_DOS)+( (ALLOW_PER_MONT HS)-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_MONTH S + 5 DAYS)
OR (B.FIRST_DOS >A.FIRST_DOS + ALLOW_PER_MONTH S - 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
WHERE A.FIRST_DOS BETWEEN
B.FIRST_DOS - ALLOW_PER_MONTH S MONTHS + 5 DAYS AND
B.FIRST_DOS + ALLOW_PER_MONTH S MONTHS - 5 DAYS

Dec 3 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
9039
by: Ralph Freshour | last post by:
What's a good way to calculate the number of days between two dates in the following format: 2003-07-15 2003-08-02 I've looked at the PHP date functions but I'm still a bit lost...
4
4444
by: Hans Gruber | last post by:
Hi all, I have been struggling with a problem all day, I have been unable to come up with a working solution. I want to write a function which takes 2 unix timestamps and calculates the difference. I want it to return the difference in years, months, days, hours, minutes and seconds (a complete summary). Keeping into account of course...
5
4090
by: Omey Samaroo | last post by:
I would like to calculate the number of days that a person is hired since date of hire to today's date. Any suggestions on how this can be accomplished ? Thanks Omey
7
2148
by: JLM | last post by:
I have a table that has fieldA, fieldB, fieldC. I want fieldC=fieldA-fieldB. simple enough. the next record I want to be able to do the same on the new value of fieldC. I can do this with SAP ABAP/4, but have never done this using Access. I'm sure this can be done, but not sure how to go about it. thanks in advance, jlm
6
27952
by: carl.barrett | last post by:
Hi, I have a continuous form based on a query ( I will also be creating a report based on the same query). There are 2 fields: Date Obtained and Date Of Expiry I want a further 3 columns to the right of these 2 fields to show the
2
3146
by: celsius | last post by:
Hi folks, Al Bowers wrote this program on comp.lang.c Date: 2001-07-09 13:41:58 PST #include <stdio.h> int isleap (unsigned yr); static unsigned months_to_days (unsigned month); static long years_to_days (unsigned yr); long ymd_to_scalar (unsigned yr, unsigned mo, unsigned day);
3
12930
by: Ron Vecchi | last post by:
I need to calculate the age of a person based on a DateTime BirthDate I was thinking TimeSpan ts = DateTime.Now - BirthDate; //I can get the days but not years. // I could check each year from their year of birth, count the days in the year and compare with the days returned from timespan
1
2102
by: artist | last post by:
Hi, I want to calculate a persons age in years, months and days where combobox1 is months,combobox2 is days and combobox3 is years. When the user clicks the button on the form,a message box pops up displaying accurate age in months,days and years.I am new to c# programming and would really appreciate if someone could suggest the code for it...
2
4305
by: RZ15 | last post by:
Hi guys, I'm really drawing a blank here for how to deal with fiscal months in my monthly sales/receipts reports. My issue is that calculating the months is not as simple as saying 'if the invoice date is january then sum sales' because the first period may include the end of december or even the beginning of february and not all of january. ...
0
7612
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8122
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7673
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6284
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5513
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5219
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3653
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2113
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1213
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.