SQL Server 2005. I need help building AR aging report . What is wrong with the script?

Question

SELECT 
   invno,invdte,
   CASE     
        WHEN (invdte >= (getdate() - 30) and itotal !=0 THEN 'itotal') AS '0-30'
        WHEN (invdte BETWEEN getdate(), - 31)  AND (getdate(), - 60) AND itotal !=0 THEN 'itotal') AS '31-60'
        WHEN (invdte < getdate(),- 61) and itotal !=0 THEN 'itotal') AS '61>'
            Else '0'
   END AS [Aging AR]
   SUM(itotal) AS 'Outstanding Total'
FROM   
   [01].[ARINVOI]
GROUP BY 
   invno, invdte
ORDER BY 
   Outstanding Total DESC

It looks like your GROUP BY doesn't include your Case Statement. — Matt, Aug 08 '14 at 19:42
And your Case Statement has many deficiencies. http://msdn.microsoft.com/en-us/library/ms181765.aspx — Matt, Aug 08 '14 at 19:44

score 0 · Answer 1 · answered Aug 11 '14 at 07:19

0

SELECT 
   invno, invdte,
   SUM(CASE WHEN invdte >= (getdate() - 30) and itotal !=0 THEN itotal END) AS [0-30]
   SUM(CASE WHEN invdte BETWEEN (getdate() - 31)  AND (getdate() - 60) AND itotal !=0 THEN itotal END) AS [31-60]
   SUM(itotal) AS 'Outstanding Total'
FROM   
   [01].[ARINVOI]
GROUP BY 
   invno, invdte
ORDER BY 
   Outstanding Total DESC

answered Aug 11 '14 at 07:19

Squirrel

23,507
4
34
32

Note : if your invdte contains date & time, you might get undesire result as getdate() - 30 will give you the date 30 days ago with current time – Squirrel Aug 11 '14 at 07:21

SQL Server 2005. I need help building AR aging report . What is wrong with the script?

1 Answers1