Avatar of 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.
Oracle Database

Avatar of undefined
Last Comment

8/22/2022 - Mon
Sean Stuber

I'm afraid I don't see the pattern yet.

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?

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.

I will assume some of the table structure in this suggested solution...

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

Open in new window

If I have guessed wrong, we will need more details about the table structures and sample data.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

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

Thank you.  I understand your response but have 1 question.
Do I actually type
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!

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question