Skip to main content

Incorporating Targets into ZM

Below are the steps that the team follows to incorporate targets in the ZM database.

1. Data Provision​

  • The Program team provides center-wise targets for all centers.
  • The file must contain the ZM vaccination center ID, as it is a mandatory field.

2. Data Mapping​

  • Annual Live Birth @ 3.1% of Population field value is mapped into annual_live_birth (locationattributetype = 29).
  • Annual Surviving Infants @ 94.0% of Live Birth field value is mapped into annual_surviving_infants.

3. Inserting Records into locationattribute Table​

3.1 For Annual Live Births​

INSERT INTO unfepi.locationattribute 
(locationAttributeTypeId, locationId, value, typeName, typeValue1, createdDate, createdByUserId) 
VALUES (29, LOCATION_ID, ROUND(Annual_Live_Birth_@_3.1%_of_Population, 2), 'ANNUALLY', YEAR, NOW(), 43);

3.2 For Annual Surviving Infants​

INSERT INTO unfepi.locationattribute 
(locationAttributeTypeId, locationId, value, typeName, typeValue1, createdDate, createdByUserId) 
VALUES (31, LOCATION_ID, ROUND(Annual_Surviving_Infants_@_94%_of_Live_Birth, 2), 'ANNUALLY', YEAR, NOW(), 43);

4. Rolling Up Targets into Upper Hierarchy​

After inserting center-wise targets, we need to roll up targets into the upper hierarchy levels (UC, Town, District, Division, and Province). The following query calculates the sum of center targets:

4.1 For Annual Live Births​

SELECT lh.relative, SUM(la.value), relativeName, name
FROM locationattribute la 
INNER JOIN location_hierarchy_ancester lh ON la.locationId = lh.locationId
AND lh.locationType = 12 AND lh.relativeLocationType = @required_location_type
WHERE typeValue1 = YEAR AND locationAttributeTypeId = 29
GROUP BY relative;

4.2 For Annual Surviving Infants​

SELECT lh.relative, SUM(la.value), relativeName, name
FROM locationattribute la 
INNER JOIN location_hierarchy_ancester lh ON la.locationId = lh.locationId
AND lh.locationType = 12 AND lh.relativeLocationType = @required_location_type
WHERE typeValue1 = YEAR AND locationAttributeTypeId = 31
GROUP BY relative;

5. Exporting and Inserting Aggregated Data​

  • Using the above queries, we obtain the targets for @required_location_type.
  • Export the data into a CSV file.
  • Create an INSERT query (as explained in Step #3) for the aggregated data.
  • Insert the data into the database.
  • Repeat this process for all upper hierarchy levels.

This process ensures that all vaccination center targets are correctly incorporated and rolled up within the ZM system.