UPdated Undefined dis, thana School SUmmary

-- creating a temporary table of school less rows

CREATE TABLE pesp_test_db_naimul.ds_raw_data_unmapped AS SELECT ds.id,
    ds.amount,
    ds.fill_up_date,
    ds.has_cross_matching_done,
    ds.insert_date,
    ds.is_amount_absent,
    ds.is_amount_invalid,
    ds.is_beneficiary_name_absent,
    ds.is_kyc_data_exist,
    ds.is_mobile_absent,
    ds.is_mobile_char_count_invalid,
    ds.is_mobile_duplicate_different_school,
    ds.is_mobile_duplicate_same_school,
    ds.is_mobile_operator_invalid,
    ds.is_name_matched,
    ds.is_nid_matched,
    ds.is_registered_by_ds,
    ds.is_schoolcode_absent,
    ds.is_student_count_absent,
    ds.is_student_count_matched,
    ds.is_valid,
    ds.kyc_id,
    ds.mobile_no,
    ds.name,
    ds.nid,
    ds.no_of_student,
    ds.school_code,
    ds.status,
    ds.ds_id_of_icr,
    ds.row_no_of_icr,
    ds.is_student_count_invalid FROM
    bugs.`disbursement_sheet_analysed_raw_data` ds
        LEFT JOIN
    pesp_db_p23_live.school sc ON ds.school_code = sc.school_code
WHERE
    sc.id IS NULL;





-- running some improvements on amount field
update pesp_test_db_naimul.ds_raw_data_unmapped
set amount =
case
when amount = 6150 or amount = 8150 then 150
when amount = 2001 or amount = 2000 or amount = 6200 then 200
when amount = 6300 or amount = 8300 or amount = 7300 or amount = 3001 or amount = 3060 or amount = 3006 or amount = 3000 or amount = 3010 or amount = 3002 or amount = 9300 or amount = 3023 or amount = 3007 then 300
when amount = 6450 then 450
when amount = 6001 or amount = 6000 or amount = 6600 or amount = 8600 then 600
when amount = 6750 then 750
else amount
end
;


-- impossible amounts made zero
update pesp_test_db_naimul.ds_raw_data_unmapped
set amount = 0 where amount < 0 or amount > 1350;

update pesp_test_db_naimul.ds_raw_data_unmapped
set no_of_student = 1 where no_of_student > 4;



-- insert Undetermined row in thana_summary
insert into pesp_test_db_naimul.thana_summary (District_name,
                                         District_id,
                                         Thana_name,
                                         TCode,
                                         Total_school,
                                         Total_Amount,
                                         Total_Amount_Mobile,
                                         Total_parents,
                                         Beneficiary,
                                         Disbursement_through_mobile_banking,
                                         NoOfStd,
                                         Beneficiary_NoOnStd,
                                         Mobile_Beneficiary_Std)
                            SELECT
                                'Undetermined' AS 'District_name',
                                0 as 'District_id',
                                'Undetermined' AS 'Thana name',
                                0 AS TCode,
                                COUNT(DISTINCT (d.`school_code`)) AS 'Total_school',
                                SUM(CASE
                                    WHEN d.`amount` <= 1350 AND d.`amount` > 0 THEN d.`amount`
                                END) AS 'Total_Amount',
                                SUM(d.`amount`) AS 'Total_Amount_Mobile',
                                COUNT(d.`id`) AS 'Total_parents',
                                COUNT(CASE
                                    WHEN d.`amount` <= 1350 AND d.`amount` > 0 THEN d.`id`
                                END) AS 'Beneficiary',
                                CAST(COUNT(CASE
                                    WHEN d.`amount` <= 1350 AND d.`amount` > 0 THEN d.`id`
                                END)*90/100 AS UNSIGNED) AS 'Disbursement_through_mobile_banking',
                                SUM(d.`no_of_student`) AS NoOfStd,
                                SUM(CASE
                                    WHEN d.`amount` <= 1350 AND d.`amount` > 0 THEN d.`no_of_student`
                                END) AS Beneficiary_NoOnStd,
                                SUM(d.`no_of_student`) AS 'Mobile_Beneficiary_Std'
                            FROM
                                pesp_test_db_naimul.ds_raw_data_unmapped d
                                where 1
                            GROUP BY 'Thana name';