Query Sales Dashboard: Gross Profit

--GetCurrentGrossProfitAmountByMonth
WITH tt_cogs_price AS (
    SELECT A.ou_id, B.salesman_id, SUBSTRING(A.ref_doc_date, 1, 6) AS year_month,
        A.ref_item_id,
        SUM(
            CASE WHEN A.curr_code <> 'IDR' THEN
                CASE WHEN A.ref_doc_type_id IN (311, 312) AND A.do_receipt_item_id = -99 THEN
                    A.qty_dlv_so * D.price * f_commercial_rate(10 /*(tenantId)*/, A.ref_doc_date, D.curr_code, 'IDR')
                ELSE
                    -1 * A.qty_dlv_so * D.price * f_commercial_rate(10 /*(tenantId)*/, A.ref_doc_date, D.curr_code, 'IDR')
                END
            ELSE
                CASE WHEN A.ref_doc_type_id IN (311, 312) AND A.do_receipt_item_id = -99 THEN
                    A.qty_dlv_so * D.price
                ELSE
                    -1 * A.qty_dlv_so * D.price
                END
            END
        ) AS cogs_amount
    FROM sl_so_balance_invoice A
    INNER JOIN sl_so B ON A.so_id = B.so_id
    INNER JOIN sl_do_item C ON A.ref_item_id = C.do_item_id
    INNER JOIN sl_so_item_purchasing D ON C.ref_id = D.so_item_id
    WHERE A.ou_id = 10 --ouId
        AND B.salesman_id = 2616 --salesmanId
        AND SUBSTRING(A.ref_doc_date, 1, 6) = '201609' --yearMonth
    GROUP BY A.ou_id, B.salesman_id, SUBSTRING(A.ref_doc_date, 1, 6), A.ref_item_id
), tt_current_sales AS (
    SELECT A.ou_id, B.salesman_id, SUBSTRING(A.ref_doc_date, 1, 6) AS year_month,
        A.ref_item_id,
        SUM(
            CASE WHEN A.curr_code <> 'IDR' THEN
                CASE WHEN A.ref_doc_type_id IN (311, 312) THEN
                    A.item_amount * f_commercial_rate(10 /*(tenantId)*/, A.ref_doc_date, A.curr_code, 'IDR')
                ELSE
                    -1 * A.item_amount * f_commercial_rate(10 /*(tenantId)*/, A.ref_doc_date, A.curr_code, 'IDR')
                END
            ELSE
                CASE WHEN A.ref_doc_type_id IN (311, 312) THEN
                    A.item_amount
                ELSE
                    -1 * A.item_amount
                END
            END
        ) AS sales_amount
    FROM sl_so_balance_invoice A
    INNER JOIN sl_so B ON A.so_id = B.so_id
    INNER JOIN sl_do C ON A.ref_id = C.do_id
    INNER JOIN sl_so_item_purchasing D ON C.ref_id = D.so_item_id
    WHERE A.ou_id = 10 --ouId
        AND B.salesman_id = 2616 --salesmanId
        AND SUBSTRING(A.ref_doc_date, 1, 6) = '201609' --yearMonth
    GROUP BY A.ou_id, B.salesman_id, SUBSTRING(A.ref_doc_date, 1, 6), A.ref_item_id
)
SELECT A.ou_id, A.salesman_id, A.year_month, SUM(A.sales_amount - B.cogs_amount) AS gross_profit_amount
FROM tt_current_sales A
LEFT OUTER JOIN tt_cogs_price B ON A.ou_id = B.ou_id
    AND A.salesman_id = B.salesman_id
    AND A.year_month = B.year_month
    AND A.ref_item_id = B.ref_item_id
GROUP BY A.ou_id, A.salesman_id, A.year_month

--GetCurrentGrossProfitPercentageByMonth
WITH tt_cogs_price AS (
    SELECT A.ou_id, B.salesman_id, SUBSTRING(A.ref_doc_date, 1, 6) AS year_month,
        A.ref_item_id,
        SUM(
            CASE WHEN A.curr_code <> 'IDR' THEN
                CASE WHEN A.ref_doc_type_id IN (311, 312) AND A.do_receipt_item_id = -99 THEN
                    A.qty_dlv_so * D.price * f_commercial_rate(10 /*(tenantId)*/, A.ref_doc_date, D.curr_code, 'IDR')
                ELSE
                    -1 * A.qty_dlv_so * D.price * f_commercial_rate(10 /*(tenantId)*/, A.ref_doc_date, D.curr_code, 'IDR')
                END
            ELSE
                CASE WHEN A.ref_doc_type_id IN (311, 312) AND A.do_receipt_item_id = -99 THEN
                    A.qty_dlv_so * D.price
                ELSE
                    -1 * A.qty_dlv_so * D.price
                END
            END
        ) AS cogs_amount
    FROM sl_so_balance_invoice A
    INNER JOIN sl_so B ON A.so_id = B.so_id
    INNER JOIN sl_do_item C ON A.ref_item_id = C.do_item_id
    INNER JOIN sl_so_item_purchasing D ON C.ref_id = D.so_item_id
    WHERE A.ou_id = 10 --ouId
        AND B.salesman_id = 2616 --salesmanId
        AND SUBSTRING(A.ref_doc_date, 1, 6) = '201609' --yearMonth
    GROUP BY A.ou_id, B.salesman_id, SUBSTRING(A.ref_doc_date, 1, 6), A.ref_item_id
), tt_current_sales AS (
    SELECT A.ou_id, B.salesman_id, SUBSTRING(A.ref_doc_date, 1, 6) AS year_month,
        A.ref_item_id,
        SUM(
            CASE WHEN A.curr_code <> 'IDR' THEN
                CASE WHEN A.ref_doc_type_id IN (311, 312) THEN
                    A.item_amount * f_commercial_rate(10 /*(tenantId)*/, A.ref_doc_date, A.curr_code, 'IDR')
                ELSE
                    -1 * A.item_amount * f_commercial_rate(10 /*(tenantId)*/, A.ref_doc_date, A.curr_code, 'IDR')
                END
            ELSE
                CASE WHEN A.ref_doc_type_id IN (311, 312) THEN
                    A.item_amount
                ELSE
                    -1 * A.item_amount
                END
            END
        ) AS sales_amount
    FROM sl_so_balance_invoice A
    INNER JOIN sl_so B ON A.so_id = B.so_id
    INNER JOIN sl_do C ON A.ref_id = C.do_id
    INNER JOIN sl_so_item_purchasing D ON C.ref_id = D.so_item_id
    WHERE A.ou_id = 10 --ouId
        AND B.salesman_id = 2616 --salesmanId
        AND SUBSTRING(A.ref_doc_date, 1, 6) = '201609' --yearMonth
    GROUP BY A.ou_id, B.salesman_id, SUBSTRING(A.ref_doc_date, 1, 6), A.ref_item_id
)
SELECT A.ou_id, A.salesman_id, A.year_month,
    CASE WHEN SUM(A.sales_amount) <> 0 THEN
        (SUM(A.sales_amount) - SUM(B.cogs_amount)) / SUM(A.sales_amount)
    ELSE
        1
    END * 100 AS gross_profit_percentage
FROM tt_current_sales A
LEFT OUTER JOIN tt_cogs_price B ON A.ou_id = B.ou_id
    AND A.salesman_id = B.salesman_id
    AND A.year_month = B.year_month
    AND A.ref_item_id = B.ref_item_id
GROUP BY A.ou_id, A.salesman_id, A.year_month