Untitled

SELECT 
-- vtor.firstName vaccinator, m.id, lhdistrict.relativeName district, lhtown.relativeName town, lhuc.relativeName uc, id.identifier childId, ch.firstName AS childName, ch.fatherFirstName AS FatherName, ch.gender, cn.number AS primaryContact, ch.birthdate, vv.vaccinationEventType vaccinationEventType, vc.name AS centerName, vi.recipient AS incentivizedNumber, vi.consumptionDate AS incentive, mv.visitDate,  GROUP_CONCAT(DISTINCT va.name) AS vaccineName, 
vc.mappedId,mv.visitDate,
COALESCE(IF(s4.surveyOutcome='SUCCESSFULLY_SCREENED' AND sb4.screeningBalanceId IS NULL AND s4.callRelationshipWithChild IS NULL, null, s4.surveyOutcome),IF(s3.surveyOutcome='SUCCESSFULLY_SCREENED' AND s3.callRelationshipWithChild IS NULL AND sb3.screeningBalanceId IS NULL, null, s3.surveyOutcome),IF(s2.surveyOutcome='SUCCESSFULLY_SCREENED' AND s2.callRelationshipWithChild IS NULL AND sb2.screeningBalanceId IS NULL, null, s2.surveyOutcome),IF(s1.surveyOutcome='SUCCESSFULLY_SCREENED' AND s1.callRelationshipWithChild IS NULL AND sb1.screeningBalanceId IS NULL, null, s1.surveyOutcome)) finalSurveyOutcome, IF(DATE(ch.dateEnrolled)<DATE(mv.visitDate), 'FUP', 'ENR') visitType, u.username operator, -- vc.centerType centerType,  
s1.createdDate AS surveyDate1, s1.surveyOutcome AS surveyOutcome1, s2.createdDate AS surveyDate2, s2.surveyOutcome AS surveyOutcome2, IF(s3.surveyOutcome='SUCCESSFULLY_SCREENED' AND s3.callRelationshipWithChild IS NULL AND sb3.screeningBalanceId IS NULL, null, s3.createdDate) AS surveyDate3,IF(s3.surveyOutcome='SUCCESSFULLY_SCREENED' AND s3.callRelationshipWithChild IS NULL AND sb3.screeningBalanceId IS NULL, null, s3.surveyOutcome) AS surveyOutcome3,  IF(s4.surveyOutcome='SUCCESSFULLY_SCREENED' AND s4.callRelationshipWithChild IS NULL AND sb4.screeningBalanceId IS NULL, null, s4.createdDate) AS surveyDate4, IF(s4.surveyOutcome='SUCCESSFULLY_SCREENED' AND s4.callRelationshipWithChild IS NULL AND sb4.screeningBalanceId IS NULL, null, s4.surveyOutcome) AS surveyOutcome4,  s4.amountPaid amountPaid4, s3.amountPaid amountPaid3, s2.amountPaid amountPaid2, s1.amountPaid amountPaid1, COALESCE(s4.amountPaid, s3.amountPaid, s2.amountPaid, s1.amountPaid) finalAmountPaid,  COALESCE(s4.callRelationshipWithChild, s3.callRelationshipWithChild, s2.callRelationshipWithChild, s1.callRelationshipWithChild) AS OncallPersonRelationWithChild,  COALESCE(s4.phoneRelationshipWithChild, s3.phoneRelationshipWithChild, s2.phoneRelationshipWithChild, s1.phoneRelationshipWithChild) AS phoneRelationshipWithChild,  COALESCE(s4.caregiverExperience, s3.caregiverExperience, s2.caregiverExperience, s1.caregiverExperience) AS caregiverExperience,  COALESCE(s4.childrenUnderTwo, s3.childrenUnderTwo, s2.childrenUnderTwo, s1.childrenUnderTwo) AS childrenUnderTwo, s4.comments comments4, s3.comments comments3, s2.comments comments2, s1.comments comments1 ,  COALESCE(IF(s4.surveyOutcome='OPEN_FOR_BALANCE_INQUIRY' OR s4.isIncentiveRecieved IN ('NO', 'DONT_KNOW'), null, s4.incentiveAmount), IF(s3.surveyOutcome='OPEN_FOR_BALANCE_INQUIRY' OR s3.isIncentiveRecieved IN ('NO', 'DONT_KNOW'), null, s3.incentiveAmount), IF(s2.surveyOutcome='OPEN_FOR_BALANCE_INQUIRY' OR s2.isIncentiveRecieved IN ('NO', 'DONT_KNOW'), null, s2.incentiveAmount), IF(s1.surveyOutcome='OPEN_FOR_BALANCE_INQUIRY' OR s1.isIncentiveRecieved IN ('NO', 'DONT_KNOW'), null, s1.incentiveAmount)) AS incentiveAmount, COALESCE(IF(s4.incentiveDuration='NOT_APPLICABLE' OR s4.surveyOutcome='OPEN_FOR_BALANCE_INQUIRY' OR s4.isIncentiveRecieved IN ('NO', 'DONT_KNOW'), null, s4.incentiveDuration), IF(s3.incentiveDuration='NOT_APPLICABLE' OR  s3.surveyOutcome='OPEN_FOR_BALANCE_INQUIRY' OR s3.isIncentiveRecieved IN ('NO', 'DONT_KNOW'), null, s3.incentiveDuration),IF(s2.incentiveDuration='NOT_APPLICABLE' OR s2.surveyOutcome='OPEN_FOR_BALANCE_INQUIRY' OR s2.isIncentiveRecieved IN ('NO', 'DONT_KNOW'), null, s2.incentiveDuration), IF(s1.incentiveDuration='NOT_APPLICABLE' OR s1.surveyOutcome='OPEN_FOR_BALANCE_INQUIRY' OR s1.isIncentiveRecieved IN ('NO', 'DONT_KNOW'), null, s1.incentiveDuration)) AS incentiveDuration, COALESCE(s4.isDataPackage, s3.isDataPackage, s2.isDataPackage, s1.isDataPackage) AS isDataPackage,  s4.isIncentiveRecieved isIncentiveRecieved4, s3.isIncentiveRecieved isIncentiveRecieved3, s2.isIncentiveRecieved isIncentiveRecieved2, s1.isIncentiveRecieved isIncentiveRecieved1, COALESCE(s4.isIncentiveRecieved, s3.isIncentiveRecieved, s2.isIncentiveRecieved, s1.isIncentiveRecieved) finalIncentiveRecieved, COALESCE(s4.isInformationGiven, s3.isInformationGiven, s2.isInformationGiven, s1.isInformationGiven) AS isInformationGiven, COALESCE(s4.isPostPaidSim, s3.isPostPaidSim, s2.isPostPaidSim, s1.isPostPaidSim) AS isPostPaidSim,  COALESCE(s4.isAccompaniedChild, s3.isAccompaniedChild, s2.isAccompaniedChild, s1.isAccompaniedChild) AS isAccompaniedChild, COALESCE(s4.accompanies, s3.accompanies, s2.accompanies, s1.accompanies) AS accompanies,  COALESCE(s4.whoElseAccompanied, s3.whoElseAccompanied, s2.whoElseAccompanied, s1.whoElseAccompanied) AS whoElseAccompanied, COALESCE(s4.isPriorInformation, s3.isPriorInformation, s2.isPriorInformation, s1.isPriorInformation) AS isPriorInformation,  COALESCE(s4.isVaccinePaid, s3.isVaccinePaid, s2.isVaccinePaid, s1.isVaccinePaid) AS isVaccinePaid, COALESCE(s4.rescheduleDate, s3.rescheduleDate, s2.rescheduleDate, s1.rescheduleDate) AS rescheduleDate,  COALESCE(IF(s4.vaccinationEventType='NOT_APPLICABLE', null, s4.vaccinationEventType),IF(s3.vaccinationEventType='NOT_APPLICABLE', null, s3.vaccinationEventType),  IF(s2.vaccinationEventType='NOT_APPLICABLE', null, s2.vaccinationEventType),IF(s1.vaccinationEventType='NOT_APPLICABLE', null, s1.vaccinationEventType)) AS vaccinationSite, COALESCE(s4.waitingTime, s3.waitingTime, s2.waitingTime, s1.waitingTime) AS waitingTime, COALESCE(s4.immunizationVisit, s3.immunizationVisit, s2.immunizationVisit, s1.immunizationVisit) AS NoOfImmunizationVisits,  sb1.optionName balanceOption1, sb2.optionName balanceOption2, sb3.optionName balanceOption3, sb4.optionName balanceOption4, COALESCE(sb4.optionName, sb3.optionName, sb2.optionName, sb1.optionName) finalBalanceOption, COALESCE(IF(s1.isAccompaniedChild =true, GROUP_CONCAT(si1.sourceName), null), IF(s2.isAccompaniedChild =true, GROUP_CONCAT(si2.sourceName), null), IF(s3.isAccompaniedChild =true, GROUP_CONCAT(si3.sourceName), null), IF(s4.isAccompaniedChild =true, GROUP_CONCAT(si4.sourceName), null)) AS informationSource, GROUP_CONCAT(sv.vaccineName) AS vaccinesAdministered  ,GROUP_CONCAT(DISTINCT sv1.vaccineName) vaccine1, GROUP_CONCAT(DISTINCT sv2.vaccineName) vaccine2, GROUP_CONCAT(DISTINCT sv3.vaccineName) vaccine3,  

-- COALESCE(IF(s4.isVaccinePaid = true, GROUP_CONCAT(spr4.paymentReason), null), IF(s3.isVaccinePaid = true, GROUP_CONCAT(spr3.paymentReason), null), IF(s2.isVaccinePaid = true, GROUP_CONCAT(spr2.paymentReason), null), IF(s1.isVaccinePaid =true, GROUP_CONCAT(spr1.paymentReason), null)) AS paymentReasons,  

-- COALESCE(IF(s4.surveyOutcome = 'INCORRECT_NUMBER', GROUP_CONCAT(sin4.incorrectNumberReason), null), IF(s3.surveyOutcome = 'INCORRECT_NUMBER', GROUP_CONCAT(sin3.incorrectNumberReason), null), IF(s2.surveyOutcome = 'INCORRECT_NUMBER', GROUP_CONCAT(sin2.incorrectNumberReason), null), IF(s1.surveyOutcome = 'INCORRECT_NUMBER', GROUP_CONCAT(sin1.incorrectNumberReason), null)) AS incorrectNumberReasons,  

-- COALESCE(IF(s4.surveyOutcome = 'IMMUNIZATION_RECORD_INCORRECT', GROUP_CONCAT(sir4.incorrectRecordReason), null), IF(s3.surveyOutcome = 'IMMUNIZATION_RECORD_INCORRECT', GROUP_CONCAT(sir3.incorrectRecordReason), null), IF(s2.surveyOutcome = 'IMMUNIZATION_RECORD_INCORRECT', GROUP_CONCAT(sir2.incorrectRecordReason), null), IF(s1.surveyOutcome = 'IMMUNIZATION_RECORD_INCORRECT', GROUP_CONCAT(sir1.incorrectRecordReason), null)) AS immunizationRecordIncorrectReasons,  

s1.mobileNetwork as networkUse1, s2.mobileNetwork AS networkUse2, s3.mobileNetwork AS networkUse3, s4.mobileNetwork AS networkUse4, COALESCE (IF(s4.mobileNetwork = 'NOT_APPLICABLE', null, s4.mobileNetwork), IF(s3.mobileNetwork = 'NOT_APPLICABLE', null, s3.mobileNetwork), IF(s2.mobileNetwork = 'NOT_APPLICABLE', null, s2.mobileNetwork), IF(s1.mobileNetwork = 'NOT_APPLICABLE', null, s1.mobileNetwork)) AS finalNetworkUse, s1.gender AS gender1, s2.gender AS gender2, s3.gender AS gender3, s4.gender AS gender4, COALESCE(IF(s4.gender = 'NOT_APPLICABLE', null, s4.gender), IF(s3.gender = 'NOT_APPLICABLE', null, s3.gender), IF(s2.gender = 'NOT_APPLICABLE', null, s2.gender), IF(s1.gender = 'NOT_APPLICABLE', null, s1.gender)) AS finalGender,  s1.surveyorExperience AS surveyorExperience1, s2.surveyorExperience AS surveyorExperience2, s3.surveyorExperience AS surveyorExperience3, s4.surveyorExperience AS surveyorExperience4, COALESCE(s4.surveyorExperience, s3.surveyorExperience, s2.surveyorExperience, s1.surveyorExperience) AS finalSurveyorExperience, s1.isBrandRecall AS isBrandRecall1, s2.isBrandRecall AS isBrandRecall2, s3.isBrandRecall AS isBrandRecall3, s4.isBrandRecall AS isBrandRecall4, COALESCE(s4.isBrandRecall, s3.isBrandRecall, s2.isBrandRecall, s1.isBrandRecall) AS finalIsBrandRecall,  s1.childReceivedReminder AS isChildReceivedReminder1, s2.childReceivedReminder AS isChildReceivedReminder2, s3.childReceivedReminder AS isChildReceivedReminder3, s4.childReceivedReminder AS isChildReceivedReminder4, COALESCE(IF(s4.childReceivedReminder = 'NOT_APPLICABLE', null, s4.childReceivedReminder), IF(s3.childReceivedReminder = 'NOT_APPLICABLE', null, s3.childReceivedReminder), IF(s2.childReceivedReminder = 'NOT_APPLICABLE', null, s2.childReceivedReminder), IF(s1.childReceivedReminder = 'NOT_APPLICABLE', null, s1.childReceivedReminder)) AS finalIsChildReceivedReminder,  s1.childNameDifferent AS isChildNameDifferent1, s2.childNameDifferent AS isChildNameDifferent2, s3.childNameDifferent AS isChildNameDifferent3, s4.childNameDifferent AS isChildNameDifferent4, COALESCE(IF(s4.childNameDifferent = 'NOT_APPLICABLE', null, s4.childNameDifferent), IF(s3.childNameDifferent = 'NOT_APPLICABLE', null, s3.childNameDifferent), IF(s2.childNameDifferent = 'NOT_APPLICABLE', null, s2.childNameDifferent), IF(s1.childNameDifferent = 'NOT_APPLICABLE', null, s1.childNameDifferent)) AS finalIsChildNameDifferent, s1.childName AS childName1, s2.childName AS childName2, s3.childName AS childName3, s4.childName AS childName4, COALESCE(s4.childName, s3.childName, s2.childName, s1.childName) AS finalChildName,  s1.fatherNameDifferent AS isFatherNameDifferent1, s2.fatherNameDifferent AS isFatherNameDifferent2, s3.fatherNameDifferent AS isFatherNameDifferent3, s4.fatherNameDifferent AS isFatherNameDifferent4, COALESCE(IF(s4.fatherNameDifferent = 'NOT_APPLICABLE', null, s4.fatherNameDifferent), IF(s3.fatherNameDifferent = 'NOT_APPLICABLE', null, s3.fatherNameDifferent), IF(s2.fatherNameDifferent = 'NOT_APPLICABLE', null, s2.fatherNameDifferent), IF(s1.fatherNameDifferent = 'NOT_APPLICABLE', null, s1.fatherNameDifferent)) AS finalIsFatherNameDifferent, s1.fatherName AS fatherName1, s2.fatherName AS fatherName2, s3.fatherName AS fatherName3, s4.fatherName AS fatherName4, COALESCE(s4.fatherName, s3.fatherName, s2.fatherName, s1.fatherName) AS finalFatherName,  s1.DOBDifferent AS isChildDOBDifferent1, s2.DOBDifferent AS isChildDOBDifferent2, s3.DOBDifferent AS isChildDOBDifferent3, s4.DOBDifferent AS isChildDOBDifferent4, COALESCE(IF(s4.DOBDifferent = 'NOT_APPLICABLE', null, s4.DOBDifferent), IF(s3.DOBDifferent = 'NOT_APPLICABLE', null, s3.DOBDifferent), IF(s2.DOBDifferent = 'NOT_APPLICABLE', null, s2.DOBDifferent), IF(s1.DOBDifferent = 'NOT_APPLICABLE', null, s1.DOBDifferent)) AS finalIsChildDOBDifferent, s1.DOB AS childDOB1, s2.DOB AS childDOB2, s3.DOB AS childDOB3, s4.DOB AS childDOB4, COALESCE(s4.DOB, s3.DOB, s2.DOB, s1.DOB) AS finalChildDOB,  s1.vaccineDistrictDifferent AS isVaccineDistrictDifferent1, s2.vaccineDistrictDifferent AS isVaccineDistrictDifferent2, s3.vaccineDistrictDifferent AS isVaccineDistrictDifferent3, s4.vaccineDistrictDifferent AS isVaccineDistrictDifferent4, COALESCE(IF(s4.vaccineDistrictDifferent = 'NOT_APPLICABLE', null, s4.vaccineDistrictDifferent), IF(s3.vaccineDistrictDifferent = 'NOT_APPLICABLE', null, s3.vaccineDistrictDifferent), IF(s2.vaccineDistrictDifferent = 'NOT_APPLICABLE', null, s2.vaccineDistrictDifferent), IF(s1.vaccineDistrictDifferent = 'NOT_APPLICABLE', null, s1.vaccineDistrictDifferent)) AS finalIsVaccineDistrictDifferent, s1.centerDistrict AS vaccineDistrict1, s2.centerDistrict AS vaccineDistrict2, s3.centerDistrict AS vaccineDistrict3, s4.centerDistrict AS vaccineDistrict4, COALESCE(s4.centerDistrict, s3.centerDistrict, s2.centerDistrict, s1.centerDistrict) AS finalVaccineDistrict,  s1.childDistrictSame AS isChildDistrictSame1, s2.childDistrictSame AS isChildDistrictSame2, s3.childDistrictSame AS isChildDistrictSame3, s4.childDistrictSame AS isChildDistrictSame4, COALESCE(IF(s4.childDistrictSame = 'NOT_APPLICABLE', null, s4.childDistrictSame), IF(s3.childDistrictSame = 'NOT_APPLICABLE', null, s3.childDistrictSame), IF(s2.childDistrictSame = 'NOT_APPLICABLE', null, s2.childDistrictSame), IF(s1.childDistrictSame = 'NOT_APPLICABLE', null, s1.childDistrictSame)) AS finalIsChildDistrictSame, s1.childDistrict AS childDistrict1, s2.childDistrict AS childDistrict2, s3.childDistrict AS childDistrict3, s4.childDistrict AS childDistrict4, COALESCE(s4.childDistrict, s3.childDistrict, s2.childDistrict, s1.childDistrict) AS finalChildDistrict,  s1.vaccineAdministeredInMcct AS isVaccineAdministeredInMcct1, s2.vaccineAdministeredInMcct AS isVaccineAdministeredInMcct2, s3.vaccineAdministeredInMcct AS isVaccineAdministeredInMcct3, s4.vaccineAdministeredInMcct AS isVaccineAdministeredInMcct4, COALESCE(IF(s4.vaccineAdministeredInMcct = 'NOT_APPLICABLE', null, s4.vaccineAdministeredInMcct), IF(s3.vaccineAdministeredInMcct = 'NOT_APPLICABLE', null, s3.vaccineAdministeredInMcct), IF(s2.vaccineAdministeredInMcct = 'NOT_APPLICABLE', null, s2.vaccineAdministeredInMcct), IF(s1.vaccineAdministeredInMcct = 'NOT_APPLICABLE', null, s1.vaccineAdministeredInMcct)) AS finalIsVaccineAdministeredInMcct, s1.vaccineDistrict AS vaccineAdministeredDistrict1, s2.vaccineDistrict AS vaccineAdministeredDistrict2, s3.vaccineDistrict AS vaccineAdministeredDistrict3, s4.vaccineDistrict AS vaccineAdministeredDistrict4, COALESCE(s4.vaccineDistrict, s3.vaccineDistrict, s2.vaccineDistrict, s1.vaccineDistrict) AS finalVaccineAdministeredDistrict  

FROM monitoring m LEFT JOIN screening s1 ON m.id = s1.monitoringId AND s1.iteration = 1 AND s1.voided=false LEFT JOIN screening s2 ON m.id = s2.monitoringId AND s2.iteration = 2 AND s2.voided=false  LEFT JOIN screening s3 ON m.id = s3.monitoringId AND s3.iteration = 3  AND s3.voided=false LEFT JOIN screening s4 ON m.id = s4.monitoringId AND s4.iteration = 4  AND s4.voided=false  INNER JOIN user u ON u.mappedId=m.operatorId LEFT JOIN screeningbalance sb1 ON sb1.screeningId=s1.screeningId  LEFT JOIN screeningbalance sb2 ON sb2.screeningId=s2.screeningId LEFT JOIN screeningbalance sb3 ON sb3.screeningId=s3.screeningId LEFT JOIN screeningbalance sb4 ON sb4.screeningId=s4.screeningId  LEFT JOIN screeningvaccine sv ON sv.screeningId = s1.screeningId  INNER JOIN child ch ON ch.mappedId = m.childId INNER JOIN contactnumber cn ON cn.mappedId = m.childId  LEFT JOIN mcctvisit mv ON mv.visitRecordNum = m.visitRecordNum 

LEFT JOIN visitincentive vi ON vi.childVaccinationVisitId = m.visitRecordNum 
LEFT JOIN vaccinationcenter vc ON vc.mappedId = mv.vaccinationCenterId 
INNER JOIN identifier id ON id.mappedId = ch.mappedId 
LEFT JOIN vaccination vv ON vv.childId=ch.mappedId AND DATE(vv.vaccinationDate)=DATE(mv.visitDate) AND vv.vaccinationStatus='VACCINATED' AND vv.vaccineId<7 
LEFT JOIN vaccinator vtor ON vtor.mappedId=vv.vaccinatorId 
LEFT JOIN vaccine va ON va.vaccineId=vv.vaccineId  
LEFT JOIN screeninginfosource si1 ON si1.screeningId=s1.screeningId  
LEFT JOIN screeninginfosource si2 ON si2.screeningId=s2.screeningId 
LEFT JOIN screeninginfosource si3 ON si3.screeningId=s3.screeningId 
LEFT JOIN screeninginfosource si4 ON si4.screeningId=s4.screeningId  
LEFT JOIN screeningvaccine sv1 ON sv1.screeningId=s1.screeningId  
LEFT JOIN screeningvaccine sv2 ON sv2.screeningId=s2.screeningId  LEFT JOIN screeningvaccine sv3 ON sv3.screeningId=s3.screeningId 

LEFT JOIN screeningincorrectnumber sin1 ON sin1.screeningId=s1.screeningId  LEFT JOIN screeningincorrectnumber sin2 ON sin2.screeningId=s2.screeningId  LEFT JOIN screeningincorrectnumber sin3 ON sin3.screeningId=s3.screeningId  LEFT JOIN screeningincorrectnumber sin4 ON sin4.screeningId=s4.screeningId  LEFT JOIN screeningincorrectrecord sir1 ON sir1.screeningId=s1.screeningId  LEFT JOIN screeningincorrectrecord sir2 ON sir2.screeningId=s2.screeningId  LEFT JOIN screeningincorrectrecord sir3 ON sir3.screeningId=s3.screeningId  LEFT JOIN screeningincorrectrecord sir4 ON sir4.screeningId=s4.screeningId  LEFT JOIN screeningpaymentreason spr1 ON spr1.screeningId=s1.screeningId  LEFT JOIN screeningpaymentreason spr2 ON spr2.screeningId=s2.screeningId  LEFT JOIN screeningpaymentreason spr3 ON spr3.screeningId=s3.screeningId  LEFT JOIN screeningpaymentreason spr4 ON spr4.screeningId=s4.screeningId  

 -- INNER JOIN location_hierarchy_ancester lhuc ON lhuc.locationId = 52  -- AND lhuc.relativeLocationType=3 
 --  INNER JOIN location_hierarchy_ancester lhtown ON lhtown.locationId = lhuc.locationId -- AND lhtown.relativeLocationType = 2 
 --  INNER JOIN location_hierarchy_ancester lhdistrict ON lhdistrict.locationId = lhuc.locationId -- AND lhdistrict.relativeLocationType = 1  
  INNER JOIN location_hierarchy_ancester lha ON lha.locationId = vc.mappedId AND lha.relative = 382364 
where  
 mv.visitDate BETWEEN '2020-02-01' and '2024-02-28' 
-- mv.childId = 8287709
GROUP BY m.id ORDER BY id.identifier ASC;