all customer due sql query

 

SELECT DONORID,DONORNAME,INROLLEDDATE,addresss AS DONORADDRESS, donor_mobile AS DONORMOBILE,

INROLLEDMONTH,PAYABLEAMOUNT,paid_amount AS TOTALPAID,

(CASE WHEN (PAYABLEAMOUNT-paid_amount)>0 THEN (PAYABLEAMOUNT-paid_amount) ELSE 0 END) AS TOTALDUEAMOUNT,

(CASE WHEN (PAYABLEAMOUNT-paid_amount)<0 THEN ABS((PAYABLEAMOUNT-paid_amount)) ELSE 0 END) AS TOTALADVANCE

FROM 

(

SELECT c.id AS DONORID,

CONVERT(YEAR(CONVERT(c.insert_date, DATE)),BINARY) AS INROLLEDYEAR,

MONTH(CONVERT(c.insert_date, DATE)) AS MonthNo,

MONTHNAME(CONVERT(c.insert_date, DATE)) AS MONTHNAME,

CONVERT(c.insert_date, DATE) AS INROLLEDDATE,

CURDATE(),

FLOOR(DATEDIFF(CURDATE(),CONVERT(c.insert_date, DATE))/30) AS INROLLEDMONTH,

FLOOR(DATEDIFF(CURDATE(),CONVERT(c.insert_date, DATE))/30)*c.donor_amount AS PAYABLEAMOUNT,

c.donor_name AS DONORNAME, c.addresss,c.donor_district,c.donor_thana,c.donor_mobile,c.donor_type,

    c.donor_amount,c.donor_rosid, (CASE WHEN dp.totalPaid >0 then dp.totalPaid ELSE 0 end) AS paid_amount 

FROM donor_list c LEFT JOIN (SELECT d.donor_id , SUM(d.donor_paid_amount) AS totalPaid FROM donor_payment d 

  GROUP BY d.donor_id) dp ON c.id = dp.donor_id

)DP