I need advice for the best way to find if a date falls within a certain quarter of any year past, present or future then find it's volume for that period of time.
The possible table record(s) values look like this example:
Part number Start Date End Date Volume
1)12345 99/99/99 99/99/99 100
2)54321 <null> 99/99/99 150
3)11111 99/99/99 <null> 175
4)22222 <null> <null> 200 (default)
The default value is ALWAYS included because it has no start or end date and is considered in every quarter.
The real trick here is that I need to prorate the volume
by the days within the quarter. So if, say the end date is
03/10/01 and there is no start date (<null>) I want the volume from 1/02/01 thru 3/10/01 only. I need similar logic for all forumlas except the default.
Parameters:
Plant Name (text) code name value
Confidence # (number) from 1 to 10, but not a range
Year (number) like 1999,2000,2001,2002 ect.
The report would look something like this:
Plant Name (group) Part# Volume more fields...
1st Quarter of 2002
12345 100
2nd..... ............
3rd..... ............
4th..... ............
I'm an experienced mainframe AS/400 programmer and for me to do this in RPG would be a piece of cake...however I'm brand new to CR and the data resides in an MS/SQL 7.0 DB so I need some help.
Any suggestions ? Thanks in advance !
The possible table record(s) values look like this example:
Part number Start Date End Date Volume
1)12345 99/99/99 99/99/99 100
2)54321 <null> 99/99/99 150
3)11111 99/99/99 <null> 175
4)22222 <null> <null> 200 (default)
The default value is ALWAYS included because it has no start or end date and is considered in every quarter.
The real trick here is that I need to prorate the volume
by the days within the quarter. So if, say the end date is
03/10/01 and there is no start date (<null>) I want the volume from 1/02/01 thru 3/10/01 only. I need similar logic for all forumlas except the default.
Parameters:
Plant Name (text) code name value
Confidence # (number) from 1 to 10, but not a range
Year (number) like 1999,2000,2001,2002 ect.
The report would look something like this:
Plant Name (group) Part# Volume more fields...
1st Quarter of 2002
12345 100
2nd..... ............
3rd..... ............
4th..... ............
I'm an experienced mainframe AS/400 programmer and for me to do this in RPG would be a piece of cake...however I'm brand new to CR and the data resides in an MS/SQL 7.0 DB so I need some help.
Any suggestions ? Thanks in advance !