jvera524
asked on
Aging Formula in update query
Hello there is a date and agingcategory column in one table and an aging lookup in another table. Every month the aging category needs to be updated using last day of the month. So if I am updating the information on June 1, 2012 it should use May 31, 2012. If May 1 it should use April 30, 2012. I need it to automatically calculate the difference between the month end date and the date in the particular field of the date column. After calculating the number of days get the appropriate aging category for the difference. There are 13 aging categories.
An example below.
Date used to calculate aging: May 31, 2012 June 30, 2012
date column Aging column(on June 1) Aging column(July 1)
May 15, 2012 0-30 Days 31-60 Days
Please let me know if any more information is needed.
Thank you in advance.
An example below.
Date used to calculate aging: May 31, 2012 June 30, 2012
date column Aging column(on June 1) Aging column(July 1)
May 15, 2012 0-30 Days 31-60 Days
Please let me know if any more information is needed.
Thank you in advance.
ASKER
Another example below.
Date used to calculate aging: May 31, 2012 June 30, 2012
date column Aging column(on June 1) Aging column(July 1)
Mar 15, 2012 61-90 Days 91-120 Days
May 15, 2012 0-30 Days 31-60 Days
It would calculate 5/31/2012-3/15/2012 @ 76 Days 6/30/2012-3/15/2012 @ 106 days
Then it would up look up 76 and 106 and obtain the appropriate aging category from the lookup table. I want to update the one column in the same table as the date column. Is there a function which would the month end date correctly.
If calculated updated on June 1 2012 or June 2 2012 It would use 5/31/2012 to calculate # of days to lookup
If calculated updated on July 1 2012 or July 4 2012 It would use 6/3o/2012 to calculate # of days to lookup.
Date used to calculate aging: May 31, 2012 June 30, 2012
date column Aging column(on June 1) Aging column(July 1)
Mar 15, 2012 61-90 Days 91-120 Days
May 15, 2012 0-30 Days 31-60 Days
It would calculate 5/31/2012-3/15/2012 @ 76 Days 6/30/2012-3/15/2012 @ 106 days
Then it would up look up 76 and 106 and obtain the appropriate aging category from the lookup table. I want to update the one column in the same table as the date column. Is there a function which would the month end date correctly.
If calculated updated on June 1 2012 or June 2 2012 It would use 5/31/2012 to calculate # of days to lookup
If calculated updated on July 1 2012 or July 4 2012 It would use 6/3o/2012 to calculate # of days to lookup.
I will assume some of the table structure in this suggested solution...
If I have guessed wrong, we will need more details about the table structures and sample data.
update table1
set agingcategory = (select agingcategory
from agingcategory_table
where (trunc(sysdate, 'mm') - 1) - table1.date
between agingcategory_table.from_days
and agingcategory_table.to_days
)If I have guessed wrong, we will need more details about the table structures and sample data.
I was too slow to do my post.
(trunc(sysdate, 'mm') - 1) equates to the last day of last month
If you have to update 2 columns, then use my subselect for each of the columns
(trunc(sysdate, 'mm') - 1) equates to the last day of last month
If you have to update 2 columns, then use my subselect for each of the columns
ASKER
Thank you. I understand your response but have 1 question.
Do I actually type
.from_days
.to_days
after agingcategory_table? Will that look up the difference in the NumDays field and return the corresponding category?
Just in case some more information below
The agingcategory_table has two columns the lookup table
NumDays Category
1 0-30 Days
2 0-30 Days
...
...
...
45 31-60 Days
The other table table1 has 2 columns(returns the value from the lookup table)
Date AgeCategory
4/21/2012 0-30 Days
1/12/2012 31-60 Days
Thank you again for your help!
Do I actually type
.from_days
.to_days
after agingcategory_table? Will that look up the difference in the NumDays field and return the corresponding category?
Just in case some more information below
The agingcategory_table has two columns the lookup table
NumDays Category
1 0-30 Days
2 0-30 Days
...
...
...
45 31-60 Days
The other table table1 has 2 columns(returns the value from the lookup table)
Date AgeCategory
4/21/2012 0-30 Days
1/12/2012 31-60 Days
Thank you again for your help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please provide more sample data and expected results.
Also, are you looking for a select statement that gives the final results or are you looking for an update statement(s) that modifies one or more of the tables?