check

WITH tt_avg_sales AS(
    SELECT A.partner_id, f_get_partner_name(A.partner_id) AS partner_name, COALESCE(AVG(gross_profit_percentage), 0) AS avg_gross_profit_percentage
    FROM vw_salesman_ou A
    LEFT OUTER JOIN dwh_sales E ON A.partner_id = E.salesman_id
        AND A.ou_id = E.ou_id
        AND NOT EXISTS (SELECT 1 FROM fi_po_internal_so_igs_tagging Z WHERE Z.so_id = E.so_id)
        AND SUBSTRING(E.year_month, 0 , 5) = '2017'
    WHERE A.tenant_id = 10
    AND A.ou_id IN (
        SELECT Z.ou_id
        FROM m_ou_dashboard_mapping Z
        WHERE Z.ou_dashboard_id = 10
    )
    AND A.partner_id IN (3186,3244,3297,5574,4704,1805,4968,5424,5268,2616,3596,3602,5597,4707)
    GROUP BY A.partner_id
),
tt_avg_outstanding AS (
    SELECT A.partner_id, f_get_partner_name(A.partner_id) AS partner_name, COALESCE(AVG(gross_profit_percentage), 0) AS avg_gross_profit_percentage
    FROM vw_salesman_ou A
    LEFT OUTER JOIN vw_summary_outstanding_per_salesman B ON A.partner_id = B.salesman_id
        AND A.ou_id = B.ou_id
        AND SUBSTRING(B.year_month, 0 , 5) = '2017'
    WHERE A.tenant_id = 10
        AND A.ou_id IN (
        SELECT Z.ou_id
        FROM m_ou_dashboard_mapping Z
        WHERE Z.ou_dashboard_id = 10
        )
        AND A.partner_id IN (3186,3244,3297,5574,4704,1805,4968,5424,5268,2616,3596,3602,5597,4707)
    GROUP BY A.partner_id
)
SELECT A.partner_name, (A.avg_gross_profit_percentage+B.avg_gross_profit_percentage)/ 2 AS avg_gross_profit_percentage
FROM tt_avg_outstanding A, tt_avg_sales B
WHERE A.partner_id = B.partner_id
ORDER BY A.partner_name;