SQL - Aging Report - 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 2005 Forums

Transact-SQL (2005)

SQL - Aging Report

Author

Topic

johngeh
Starting Member

3 Posts

Posted - 2009-04-02 : 00:16:08

I need to do an aging report for my accounts payable. I found a similar thread but it showed the aging for each invoice. I need to sum the amounts due by account. My SQL runs but the results are all 0. What am I doing wrong?

SELECT AP.VendorNum, V.Name,
SUM(CASE
WHEN DATEDIFF(dd, AP.duedate, GETDATE()) > 0 AND DATEDIFF(dd, AP.duedate, GETDATE()) <= 0
THEN AP.invoiceamt
ELSE 0
END) AS [0 - 7 Days],
SUM(CASE
WHEN DATEDIFF(dd, AP.duedate, GETDATE()) > 7 AND DATEDIFF(dd, AP.duedate, GETDATE()) <= 14
THEN AP.invoiceamt
ELSE 0
END) AS [8 - 14 Days],
SUM(CASE
WHEN DATEDIFF(dd, AP.duedate, GETDATE()) > 14 AND DATEDIFF(dd, AP.duedate, GETDATE()) <= 21
THEN AP.invoiceamt
ELSE 0
END) AS [15 - 21 Days],
SUM(CASE
WHEN DATEDIFF(dd, AP.duedate, GETDATE()) > 21 AND DATEDIFF(dd, AP.duedate, GETDATE()) <= 28
THEN AP.invoiceamt
ELSE 0
END) AS [21 - 28 Days],
SUM(CASE
WHEN DATEDIFF(dd, AP.duedate, GETDATE()) > 28
THEN AP.invoiceamt
ELSE 0
END) AS [Over 28 Days]
FROM apinvhed AP, Vendor V
WHERE V.VendorNum = AP.VendorNum
AND AP.DueDate > GETDATE()
GROUP BY AP.VendorNum, V.Name
ORDER BY V.Name

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-04-02 : 02:39:23

put your conditions within braces and try. like this..

SUM(CASE WHEN (DATEDIFF(dd, AP.duedate, GETDATE()) > 0 AND DATEDIFF(dd, AP.duedate, GETDATE()) <= 0 )
THEN AP.invoiceamt
ELSE 0
END) AS [0 - 7 Days],

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-02 : 03:19:54

Greater than 0 and also less-or-equal-to zero? What?

My opinion is that less-or-equal-to should be 7.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-02 : 03:22:27

Or this easier to read suggestion

SELECT		AP.VendorNum,
		V.Name,
		SUM(CASE WHEN DATEDIFF(dd, AP.duedate, GETDATE()) BETWEEN 0 AND 7 THEN AP.invoiceamt ELSE 0 END) AS [0 - 7 Days],
		SUM(CASE WHEN DATEDIFF(dd, AP.duedate, GETDATE()) BETWEEN 8 AND 14 THEN AP.invoiceamt ELSE 0 END) AS [8 - 14 Days],
		SUM(CASE WHEN DATEDIFF(dd, AP.duedate, GETDATE()) BETWEEN 15 AND 21 THEN AP.invoiceamt ELSE 0 END) AS [15 - 21 Days],
		SUM(CASE WHEN DATEDIFF(dd, AP.duedate, GETDATE()) BETWEEN 22 AND 28 THEN AP.invoiceamt ELSE 0 END) AS [21 - 28 Days],
		SUM(CASE WHEN DATEDIFF(dd, AP.duedate, GETDATE()) >= 29 THEN AP.invoiceamt ELSE 0 END) AS [Over 28 Days]
FROM		apinvhed AS AP
INNER JOIN	Vendor AS V ON V.VendorNum = AP.VendorNum
WHERE		AP.DueDate > DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
GROUP BY	AP.VendorNum,
		V.Name
ORDER BY	V.Name

E 12°55'05.63"
N 56°04'39.26"

johngeh
Starting Member

3 Posts

Posted - 2009-04-02 : 10:56:09

I tried both of the above solutions and neither of them solves the problem. I still get all 0's in the result set.

Any other ideas?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-04-02 : 11:07:59

I doubt that this is the condition you want in your query if you are looking for overdue invoices.

AND AP.DueDate > GETDATE()

CODO ERGO SUM

johngeh
Starting Member

3 Posts

Posted - 2009-04-02 : 11:45:20

These are payables so they are future liabilities but it did give me an idea that helped me figure it out.

The DATEDIFF function is returning a negative value so I just changed the ranges and it worked.

SELECT AP.VendorNum, V.Name,
SUM(CASE WHEN DATEDIFF(dd, AP.duedate, '4/6/2009') BETWEEN -7 AND 0 THEN AP.invoiceamt ELSE 0 END) AS [0 - 7 Days],
SUM(CASE WHEN DATEDIFF(dd, AP.duedate, '4/6/2009') BETWEEN -14 AND -8 THEN AP.invoiceamt ELSE 0 END) AS [8 - 14 Days],
SUM(CASE WHEN DATEDIFF(dd, AP.duedate, '4/6/2009') BETWEEN -21 AND -15 THEN AP.invoiceamt ELSE 0 END) AS [15 - 21 Days],
SUM(CASE WHEN DATEDIFF(dd, AP.duedate, '4/6/2009') BETWEEN -28 AND -22 THEN AP.invoiceamt ELSE 0 END) AS [21 - 28 Days],
SUM(CASE WHEN DATEDIFF(dd, AP.duedate, '4/6/2009') < -28 THEN AP.invoiceamt ELSE 0 END) AS [Over 28 Days]
FROM APInvHed AP, Vendor V
WHERE V.VendorNum = AP.VendorNum
AND AP.DueDate > '4/6/2009'
GROUP BY AP.VendorNum, V.Name
ORDER BY V.Name

Thanks to everyone who helped.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources