Hi again,
Here's what I use to produce my summary report.
Kind regards,
Leslie:
select
CASE WHEN
DATEADD ( d , -30, GETDATE() ) > T1.DOCDATE AND
DATEADD ( d , -45, GETDATE() ) <= T1.DOCDATE
THEN
CASE T1.RMDTYPAL
WHEN 7 THEN -1*T1.CURTRXAM
WHEN 8 THEN -1*T1.CURTRXAM
WHEN 9 THEN -1*T1.CURTRXAM
ELSE T1.CURTRXAM
END
ELSE 0 END
as [31 - 45],
CASE WHEN
DATEADD ( d , -60, GETDATE() ) > T1.DOCDATE AND
DATEADD ( d , -75, GETDATE() ) <= T1.DOCDATE
THEN
CASE T1.RMDTYPAL
WHEN 7 THEN -1*T1.CURTRXAM
WHEN 8 THEN -1*T1.CURTRXAM
WHEN 9 THEN -1*T1.CURTRXAM
ELSE T1.CURTRXAM
END
ELSE 0 END
as [61 - 75],
CASE WHEN
DATEADD ( d , -90, GETDATE() ) > T1.DOCDATE
THEN
CASE T1.RMDTYPAL
WHEN 7 THEN -1*T1.CURTRXAM
WHEN 8 THEN -1*T1.CURTRXAM
WHEN 9 THEN -1*T1.CURTRXAM
ELSE T1.CURTRXAM
END
ELSE 0 END
as [Over 90],
CASE WHEN
DATEADD ( d , -30, GETDATE() ) <= T1.DOCDATE
THEN
CASE T1.RMDTYPAL
WHEN 7 THEN -1*T1.CURTRXAM
WHEN 8 THEN -1*T1.CURTRXAM
WHEN 9 THEN -1*T1.CURTRXAM
ELSE T1.CURTRXAM
END
ELSE 0 END
as [0 - 30],
CASE WHEN
DATEADD ( d , -45, GETDATE() ) > T1.DOCDATE AND
DATEADD ( d , -60, GETDATE() ) <= T1.DOCDATE
THEN
CASE T1.RMDTYPAL
WHEN 7 THEN -1*T1.CURTRXAM
WHEN 8 THEN -1*T1.CURTRXAM
WHEN 9 THEN -1*T1.CURTRXAM
ELSE T1.CURTRXAM
END
ELSE 0 END
as [46 - 60],
CASE WHEN
DATEADD ( d , -75, GETDATE() ) > T1.DOCDATE AND
DATEADD ( d , -90, GETDATE() ) <= T1.DOCDATE
THEN
CASE T1.RMDTYPAL
WHEN 7 THEN -1*T1.CURTRXAM
WHEN 8 THEN -1*T1.CURTRXAM
WHEN 9 THEN -1*T1.CURTRXAM
ELSE T1.CURTRXAM
END
ELSE 0 END
as [76 - 90],
CASE T1.RMDTYPAL
WHEN 7 THEN -1*T1.CURTRXAM
WHEN 8 THEN -1*T1.CURTRXAM
WHEN 9 THEN -1*T1.CURTRXAM
ELSE T1.CURTRXAM
END as [Current_Bal],
CASE T1.RMDTYPAL
WHEN 7 THEN -1*T1.CURTRXAM
WHEN 8 THEN -1*T1.CURTRXAM
WHEN 9 THEN -1*T1.CURTRXAM
ELSE T1.CURTRXAM
END as [Original Amt],
T1.[CUSTNMBR],
T2.[CUSTNAME]
from [TWO]..RM20101 T1 with (nolock)
INNER JOIN [TWO]..RM00101 T2 with (nolock)
on
T2.[CUSTNMBR] = T1.[CUSTNMBR]
where
T1.[CURTRXAM] <> '0'