2022-07-13

SELECT
        a.region_id regionId,
        a.charge_item_id itemId,
        ( SELECT region_name FROM cs_region WHERE id = a.region_id ) regionName,
        ( SELECT NAME FROM pms_charge_item WHERE id = a.charge_item_id ) AS itemName,
        sum( a.actual_amount ) oweAmount,
        sum( a.charge_amount - a.actual_amount ) oweAmounta
        FROM
        (
        SELECT
        pclr.bill_id,
        pclr.region_id,
        pclr.project_id,
        pclr.charge_item_id,
        pclr.charge_amount,
        pclr.room_id,
        ifnull( sum( IF ( pclr.receipt_date   between between '2022-01-01' and '2022-07-13', pclr.actual_amount, 0 ) ), 0 ) AS actual_amount
        FROM
        pms_charge_ledger_report pclr
        WHERE
        pclr.delete_status = 0
        
        AND pclr.charge_item_id IN ( '1','2','3','4','5' )
        and pclr.bill_date  between  BETWEEN '1900-01-01'  AND '2022-01-31' 
        GROUP BY
        pclr.bill_id
        ) a
        GROUP BY
        a.project_id