Monday, 10 November 2014

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

6 comments:

  1. Nice post, I bookmark your blog because I found very good information on your blog, Thanks for sharing more information
    Three Channel ECG Machine

    ReplyDelete
  2. I think I am going to use a similar blog template to yours. I find it very attractive to the reader.
    ALOKA UST-

    9130

    ReplyDelete
  3. Thanks for your nice blog! Recently i’m promoting incontinence, wound care and bandages products named wound care. We’re selling Dignity, Molicare, Hartmann and Attends products.

    ReplyDelete
  4. Thanks for your nice blog! Recently i’m promoting incontinence, wound care and bandages products named wound care . We’re selling Dignity, Molicare, Hartmann and Attends products.

    ReplyDelete
  5. Thank you so much for this query!! It was just what I was looking for an invoicing software project.

    ReplyDelete