A/R Aging query misery.

See more:

SQL

Hopefully someone may be able to set me on the right path. I have the following MS SQL A/R Aging report query. The basic idea is to return customer names, get the sum of their unused credits and then put corresponding invoice due values in aging buckets.

It all works. Except when the customer has never had an invoice but does have a credit. The FROM clause unfortunately creates that dependency. I've tried all manner of Unions and Joins that I can think of but either my knowledge is lacking or I'm just plain doing it wrong.

SELECT contacts.ID, ISNULL(contacts.LName + ', ','') + ISNULL(contacts.FName,'') AS [Name],
(SELECT SUM(c1) FROM
(
    SELECT SUM(total-due) AS c1 FROM invoices WHERE customerID = contacts.ID
    UNION ALL SELECT SUM(-amount) AS c1 FROM transactions WHERE FromID = contacts.ID AND ([Type] = 'Payment' OR [Type] = 'Refund')
    UNION ALL SELECT SUM(amount) AS c1 FROM transactions WHERE ToID = contacts.ID AND [Type] LIKE 'Credit%%'
) AS q1) AS 'Credits',
CONVERT(money,SUM(CASE WHEN DATEDIFF(day,orderDate,GETDATE()) = 0 THEN due ELSE 0 END)) AS 'Current',
CONVERT(money,SUM(CASE WHEN DATEDIFF(day,orderDate,GETDATE()) BETWEEN 1 AND 30 THEN due ELSE 0 END)) AS '1-30',
CONVERT(money,SUM(CASE WHEN DATEDIFF(day,orderDate,GETDATE()) BETWEEN 31 AND 60 THEN due ELSE 0 END)) AS '31-60',
CONVERT(money,SUM(CASE WHEN DATEDIFF(day,orderDate,GETDATE()) BETWEEN 61 AND 90 THEN due ELSE 0 END))  AS '61-90',
CONVERT(money,SUM(CASE WHEN DATEDIFF(day,orderDate,GETDATE()) > 90 THEN due ELSE 0 END)) AS '> 90', '' AS Total
FROM invoices, contacts WHERE Total <> 0 AND orderDate < '2014-07-26 00:00:00' AND contacts.ID = customerID
GROUP BY contacts.ID, contacts.LName, contacts.FName ORDER BY contacts.LName, contacts.FName

Another problem I have that is less significant but has me stumped is how to efficiently add the "bucket" contents to get a row total. Right now I have some C++ code to iterate the records and calculate that way, but it means performing operations on each report row which seems less than optimal. I was thinking there may be some way to perform that operation inside the query as well.

Any help is greatly appreciated.