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