Defenseload: aging

Showing posts with label aging. Show all posts

Monday, March 6, 2017

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