Calculate 30,60,90+ aging - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Calculate 30,60,90+ aging

Author

Topic

hbadministrator
Posting Yak Master

120 Posts

Posted - 2012-10-25 : 07:49:34

I am trying to create a report that calculates current balance, 30 days past due, 60 days and 90+. I will provide you the current code and then some example data. I would like to create extra fields with to show if it is current, 30, 60, or 90+.

ok here is the sql code

Select [div-code], [cust-no], [Reference], [Trans-date], [balance-amt], Fields I would like created [Age-num], [30-days], [60-days], [90-over]

To calculate you need to take [Trans-date]-[current-date] to give you [Age-num] then if below 30 days put [Balance-amt] in [Current] if 30 days put [Balance-amt] in [30-days], if [60-days] then put [Balance-amt] in [60-days] so on and so on.

Example data
Div code, Cust-No, Reference, Trans-Date, Balance-amt,
23, 05175M, 1004610, 2012-10-16, 179.52
23, 05175M, 1004631, 2012-10-16, 89.76
00, 05175M, 1004325, 2012-06-01, 1965.92
00, 05175M, 1034253, 2012-03-01, 3555.52

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-25 : 08:07:18

with cte as
(
select [div-code], [cust-no], [Reference], [Trans-date], [balance-amt]
, age_num = datediff(dd,[Trans-date], getdate())
from ...
)
select *, Current = case when age_num < 30 then [balance-amt] end
[30-days] = case when age_num between 30 and 59 then [balance-amt] end
...
from cte

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hbadministrator
Posting Yak Master

120 Posts

Posted - 2012-10-25 : 08:28:30

just got it lol here is my code.

SELECT [div-code]
,[Cust-no]
,[Reference]
,[Ar-account]
,[Trans-date]
,[Due-date]
,[Original-amt]
,[Balance-amt]
,[Disc-date]
,[Disc-amt]
,[From-cust]
,[Tax-code]
,[Slspn-code]
,[source-document]
,[status-flag]
,[Cust-po]
,[curr-factor]
,[HistoryDate]
,[invoiceamt], DATEDIFF(day, [Trans-date], Getdate()) as Numberofdays, 'Current' = CASE WHEN DATEDIFF(day, [Trans-date], Getdate()) < 30 Then [Balance-amt] END, '30_days' = CASE WHEN DATEDIFF(day, [Trans-date], Getdate()) Between 30 AND 60 Then [Balance-amt] END, '60_days' = CASE WHEN DATEDIFF(day, [Trans-date], Getdate()) Between 60 AND 90 Then [Balance-amt] END, '90_plus' = CASE WHEN DATEDIFF(day, [Trans-date], Getdate()) > 90 Then [Balance-amt] END
FROM [Service].[dbo].[ar-open-item]

hbadministrator
Posting Yak Master

120 Posts

Posted - 2012-10-25 : 08:46:04

Thanks for the fast response though!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources