Aging Formula in update query

jvera524
jvera524 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
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?

Author

Commented:
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.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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

Author

Commented:
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!
Based on the given table structure, change the query to something like this...

update table1
set agingcategory = (select Category
                                from agingcategory_table
                                where (trunc(sysdate, 'mm') - 1) - table1.date = NumDays
                                )
                                            

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial