Rewrite the sql script for Monthly Status Summary

[adamsanadi6]

Due to the current logic written in the sql script, not able to add the row level security on the dataset.

• [x] Rewrite the script so row level security can be applied on the dataset for org_name, State and the City.

[adamsanadi6]

Backup: Old Chart

Old Dataset:

{% set states = filter_values('State') %}
{% set cities = filter_values('City') %}
{% set partners = filter_values('org_name') %}
WITH total_target AS (
    SELECT 
        SUM(t."Total number of safai sathis covered per month") AS target_count
    FROM 
        utthaan.target t 
    left join utthaan.address a on
        a.id = t.address_id
    left join organisation o on
        o.id = t.organisation_id
    WHERE 
        t.is_voided = false
        AND (
          {% if states %}
            a."State" IN (
              {% for state in states %}
                '{{ state }}'{% if not loop.last %},{% endif %}
            {% endfor %}
          )  AND
          {% endif %}
          {% if cities %}
            a."City" IN (
              {% for city in cities %}
                '{{ city }}'{% if not loop.last %},{% endif %}
            {% endfor %}
          )  AND
          {% endif %}
          {% if partners %}
            o.name IN (
              {% for partner in partners %}
                '{{ partner }}'{% if not loop.last %},{% endif %}
            {% endfor %}
          )  AND
          {% endif %}
          True
        )
),
total_achieved AS (
    SELECT 
        COUNT(DISTINCT i.id) AS achieved_count
    FROM
        utthaan.individual i
    left join utthaan.address a on
        a.id = i.address_id
    left join organisation o on
        o.id = i.organisation_id
    WHERE 
        i.is_voided = false
        AND (
          {% if states %}
            a."State" IN (
              {% for state in states %}
                '{{ state }}'{% if not loop.last %},{% endif %}
            {% endfor %}
          )  AND
          {% endif %}
          {% if cities %}
            a."City" IN (
              {% for city in cities %}
                '{{ city }}'{% if not loop.last %},{% endif %}
            {% endfor %}
          )  AND
          {% endif %}
          {% if partners %}
            o.name IN (
              {% for partner in partners %}
                '{{ partner }}'{% if not loop.last %},{% endif %}
            {% endfor %}
          )  AND
          {% endif %}
          True
        )
),
this_month_target AS (
    SELECT 
        SUM(t."Total number of safai sathis covered per month") AS target_count
    FROM 
        utthaan.target t 
    left join utthaan.address a on
        a.id = t.address_id
    left join organisation o on
        o.id = t.organisation_id
    WHERE 
        (
          {% if states %}
            a."State" IN (
              {% for state in states %}
                '{{ state }}'{% if not loop.last %},{% endif %}
            {% endfor %}
          )  AND
          {% endif %}
          {% if cities %}
            a."City" IN (
              {% for city in cities %}
                '{{ city }}'{% if not loop.last %},{% endif %}
            {% endfor %}
          )  AND
          {% endif %}
          {% if partners %}
            o.name IN (
              {% for partner in partners %}
                '{{ partner }}'{% if not loop.last %},{% endif %}
            {% endfor %}
          )  AND
          {% endif %}
          {% if from_dttm is not none and to_dttm is not none %}
            t.registration_date >= '{{ from_dttm }}' AND t.registration_date <= '{{ to_dttm }}' AND
          {% else %}
            EXTRACT(MONTH FROM CURRENT_TIMESTAMP) = EXTRACT(MONTH FROM t.registration_date) 
            AND EXTRACT(YEAR FROM CURRENT_TIMESTAMP) = EXTRACT(YEAR FROM t.registration_date) AND
          {% endif %}
          True
        )
),
this_month_achieved AS (
    SELECT 
        COUNT(DISTINCT i.id) AS achieved_count
    FROM
        utthaan.individual i
    left join utthaan.address a on
        a.id = i.address_id
    left join organisation o on
        o.id = i.organisation_id
    WHERE 
        TRUE
        AND (
          {% if states %}
            a."State" IN (
              {% for state in states %}
                '{{ state }}'{% if not loop.last %},{% endif %}
            {% endfor %}
          )  AND
          {% endif %}
          {% if cities %}
            a."City" IN (
              {% for city in cities %}
                '{{ city }}'{% if not loop.last %},{% endif %}
            {% endfor %}
          )  AND
          {% endif %}
          {% if partners %}
            o.name IN (
              {% for partner in partners %}
                '{{ partner }}'{% if not loop.last %},{% endif %}
            {% endfor %}
          )  AND
          {% endif %}
          {% if from_dttm is not none and to_dttm is not none %}
            i.registration_date >= '{{ from_dttm }}' AND i.registration_date <= '{{ to_dttm }}' AND
          {% else %}
            EXTRACT(MONTH FROM CURRENT_TIMESTAMP) = EXTRACT(MONTH FROM i.registration_date) 
              AND EXTRACT(YEAR FROM CURRENT_TIMESTAMP) = EXTRACT(YEAR FROM i.registration_date) AND
          {% endif %}
          True
        )
)
SELECT 
    t.target_count AS "Target for this month",
    a.achieved_count AS "Achieved this month",
    (a.achieved_count / t.target_count * 100) AS "% Achieved",
    tt.target_count AS "Total target",
    ta.achieved_count AS "Total Achievement",
    (ta.achieved_count / tt.target_count * 100) AS "% Total Achieved"
FROM 
    this_month_achieved a, 
    this_month_target t,
    total_target tt,
    total_achieved ta

[adamsanadi6]

New Datasets: 1. Monthly Status Summary:

WITH this_month_target AS (
    SELECT
        SUM(t."Total number of safai sathis covered per month") AS target_count,
        o.name AS org_name,
        a."State",
        a."City"
    FROM
        utthaan.target t
    JOIN utthaan.address a ON
        a.id = t.address_id
        AND a.is_voided = FALSE
    JOIN organisation o ON
        o.id = t.organisation_id
    WHERE
        t.is_voided = FALSE 
        AND (
            {% if from_dttm is not none and to_dttm is not none %}
                t.registration_date >= '{{ from_dttm }}' AND t.registration_date <= '{{ to_dttm }}' AND
            {% else %}
                EXTRACT(MONTH FROM CURRENT_TIMESTAMP) = EXTRACT(MONTH FROM t.registration_date) 
                AND EXTRACT(YEAR FROM CURRENT_TIMESTAMP) = EXTRACT(YEAR FROM t.registration_date) AND
            {% endif %}
            True
        )
    GROUP BY 
        2, 3, 4
),
this_month_achieved AS (
    SELECT
        COUNT(DISTINCT i.id) AS achieved_count,
        o.name AS org_name,
        a."State",
        a."City"
    FROM
        utthaan.individual i
    JOIN utthaan.address a ON
        a.id = i.address_id
        AND a.is_voided = FALSE
    JOIN organisation o ON
        o.id = i.organisation_id
    WHERE
        i.is_voided = FALSE
        AND (
            {% if from_dttm is not none and to_dttm is not none %}
                i.registration_date >= '{{ from_dttm }}' AND i.registration_date <= '{{ to_dttm }}' AND
            {% else %}
                EXTRACT(MONTH FROM CURRENT_TIMESTAMP) = EXTRACT(MONTH FROM i.registration_date) 
                AND EXTRACT(YEAR FROM CURRENT_TIMESTAMP) = EXTRACT(YEAR FROM i.registration_date) AND
            {% endif %}
            True
        )
    GROUP BY 
        2, 3, 4
)
SELECT
    tma.org_name,
    tma."State",
    tma."City",
    COALESCE(SUM(tmt.target_count), 0) AS "Target for this month",
    COALESCE(SUM(tma.achieved_count), 0) AS "Achieved this month"
FROM
    this_month_achieved tma
LEFT JOIN this_month_target tmt ON
    tmt.org_name = tma.org_name
    AND tmt."State" = tma."State"
    AND tmt."City" = tma."City"
GROUP BY 1, 2, 3

2. All-Time Status Summary:

WITH total_target AS (
    SELECT
        SUM(t."Total number of safai sathis covered per month") AS target_count,
        o.name AS org_name,
        a."State",
        a."City"
    FROM
        utthaan.target t
    JOIN utthaan.address a ON
        a.id = t.address_id
        AND a.is_voided = FALSE
    JOIN organisation o ON
        o.id = t.organisation_id
    WHERE
        t.is_voided = FALSE
    GROUP BY 
        2, 3, 4
),
total_achieved AS (
    SELECT
        COUNT(DISTINCT i.id) AS achieved_count,
        o.name AS org_name,
        a."State",
        a."City"
    FROM
        utthaan.individual i
    JOIN utthaan.address a ON
        a.id = i.address_id
        AND a.is_voided = FALSE
    JOIN organisation o ON
        o.id = i.organisation_id
    WHERE
        i.is_voided = FALSE
    GROUP BY 
        2, 3, 4
)
SELECT
    ta.org_name,
    ta."State",
    ta."City",
    COALESCE(SUM(tt.target_count), 0) AS "Total target",
    COALESCE(SUM(ta.achieved_count), 0) AS "Total Achievement"
FROM
    total_achieved ta
LEFT JOIN total_target tt ON
    tt.org_name = ta.org_name
    AND tt."State" = ta."State"
    AND tt."City" = ta."City"
GROUP BY 
    1, 2, 3;

[adamsanadi6]

Dashboard link: Utthaan Summary Dashboard

[adamsanadi6]

@sachsk Please review the report.