id wise total 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

WHERE DP.DONORID = 6


Comments

Post a Comment

Popular posts from this blog

date wise search codeigniter

-
  if ( ! empty ( $startdate ))         {             $this -> db -> where ( 'DATE(pickupman_received_date) >=' , $startdate );             }         if ( ! empty ( $enddate ))         {                   $this -> db -> where ( 'DATE(pickupman_received_date) <=' , $enddate );               }
Read more

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 >
Read more