USE [CA]
GO
/****** Object: StoredProcedure [dbo].[usp_aging] Script Date: 06/03/2013 14:57:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[usp_aging]
--@ARS char(3)
AS
select custnmbr
,custname
,coalesce([30],0.00) as 'Current'
,coalesce([31],0.00) as '31-60'
,coalesce([61],0.00) as '61-90'
,coalesce([91],0.00) as 'Over-90'
from
(select 'AgeDays' =
case when abs(DATEDIFF(DAY,GETDATE(),docdate)) between 0 and 30 then 30
when abs(DATEDIFF(DAY,GETDATE(),docdate)) between 31 and 60 then 31
when abs(DATEDIFF(DAY,GETDATE(),docdate)) between 61 and 90 then 61
when abs(DATEDIFF(DAY,GETDATE(),docdate)) >= 91 then 91
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
from RM20101 rm201 inner join RM00101 rm101
on rm201.custnmbr = rm101.custnmbr
group by rm101.custnmbr,custname,rmdtypal,docdate
) as Sourcetable
pivot(sum(total)
for [AgeDays]in ([30],[31],[61],[91]))as Pvt
order by custnmbr