Monday, March 6, 2017
MYSQL Query for Aging Report
MYSQL Query for Aging Report
Assume the following table scheme
Table Name : billing
billNo varchar(20);
insuranceCode varchar(20);
balanceAmount decimal(12,2);
billedDate date;
Here is the query for Aging Report
SELECT insuranceCode,
SUM(IF(DATEDIFF(CURDATE(), billedDate ) BETWEEN 1 AND 30, balanceAmount, 0)) AS age130,
SUM(IF(DATEDIFF(CURDATE(), billedDate ) BETWEEN 31 AND 60, balanceAmount, 0)) AS age3160,
SUM(IF(DATEDIFF(CURDATE(), billedDate ) BETWEEN 61 AND 90, balanceAmount, 0)) AS age6190,
SUM(IF(DATEDIFF(CURDATE(), billedDate ) > 90, balanceAmount, 0)) AS agegt90,
SUM(balanceAmount) AS totalBalance
FROM billing bill
WHERE
bill.balanceAmount > 0
GROUP BY insuranceCode
ORDER BY totalBalance DESC
Available link for download