# Aging report GP Dynamics

,

We have millions of records in our Open AR table in Micorsoft Dynamics GP. It was taking 10-12 hours to run an aging. This simple script using Pivot and coalesce take 30 seconds,. We are on GP 10.4

```select
custnmbr
,custname
,coalesce([1],0.00) as 'Current'
,coalesce([2],0.00) as '31-60'
,coalesce([3],0.00) as '61-90'
,coalesce([4],0.00) as 'Over-91'
,[User Date]
from
(select  'Aging Day' =
case when abs(DATEDIFF ( day ,docdate,getdate())) between 0 and 30 then 1
when abs(DATEDIFF (  day ,docdate,getdate()))   between 31 and 60 then 2
when abs(DATEDIFF (  day ,docdate,getdate())) between 61 and 90  then 3
when abs(DATEDIFF ( day ,docdate,getdate())) > 90 then 4
end
,'Total' =
case when RMDTYPAL = 1 then SUM(CURTRXAM)
when RMDTYPAL = 3 then SUM(CURTRXAM)
when RMDTYPAL = 7 then (SUM(CURTRXAM))*-1
when RMDTYPAL = 9 then (SUM(CURTRXAM))*-1
end
,custname
,rm101.custnmbr
,(select [dbo].[uf_AR_EndPrd_Date]()) as 'User Date'
from RM20101 rm201 inner join RM00101 as Rm101
on rm201.custnmbr = rm101.custnmbr
group by rm101.custnmbr,custname,rmdtypal,docdate

) as SourceTable
pivot(
sum(Total)
for [Aging Day] in ([1],[2],[3],[4])
)as PVT
order by custnmbr```

## Rate

5 (3)

You rated this post out of 5. Change rating

## Rate

5 (3)

You rated this post out of 5. Change rating