query

-- Current Sales Revenue Achievement per Salesman
SELECT SUM(A.sales_amount) AS sales_amount, ((SUM(A.sales_amount) / B.target_amount) * 100) AS sales_percentage
FROM dwh_sales A
INNER JOIN m_target_salesman B ON A.salesman_id = B.salesman_id AND B.year_month = '201701'
WHERE A.year_month = '201701'
    AND A.salesman_id = 3596
    AND A.doc_type_id IN (311, 312)
GROUP BY B.target_amount;

-- Current Sales Revenue Achievement per Salesman
WITH tt_gorss_profit AS (
    SELECT COALESCE(SUM(gross_profit_amount), 0) AS gross_profit_amount, COALESCE(SUM(A.sales_amount),0 ) AS sales_amount
    FROM dwh_sales A
    WHERE A.year_month = '201701'
        AND A.salesman_id = 3596
        AND A.doc_type_id IN (311, 312))
SELECT sales_amount, gross_profit_amount, ((sales_amount - gross_profit_amount) / sales_amount) AS gross_profit_percentage
FROM tt_gorss_profit;

-- History Sales Revenue Achievement per Salesman
1. SELECT SUM(sales_amount) AS sales_amount
FROM dwh_sales A
WHERE A.salesman_id = 3596
    AND A.year_month IN (
        SELECT Z.year_month
        FROM m_target_salesman Z
        WHERE Z.salesman_id = A.salesman_id
            AND SUBSTRING(Z.year_month, 0, 5) = '2017')
GROUP BY A.year_month

2. SELECT SUM(A.sales_amount) AS sales_amount
FROM dwh_sales A
WHERE A.salesman_id = 3596
    AND SUBSTRING(A.year_month, 0, 5) = '2017'
GROUP BY A.year_month
ORDER BY A.year_month


-- History Gross Revenue Achievement per Salesman
1. SELECT SUM(A.gross_profit_amount) AS gross_profit_amount
FROM dwh_sales A
WHERE A.salesman_id = 3596
    AND A.year_month IN (
        SELECT Z.year_month
        FROM m_target_salesman Z
        WHERE Z.salesman_id = A.salesman_id
            AND SUBSTRING(Z.year_month, 0, 5) = '2017')
GROUP BY A.year_month

2. SELECT SUM(A.gross_profit_amount) AS gross_profit_amount
FROM dwh_sales A
WHERE A.salesman_id = 3596
    AND SUBSTRING(A.year_month, 0, 5) = '2017'
GROUP BY A.year_month
ORDER BY A.year_month