-- File: Basic - 20230505.xlsx
-- On Sheets: Omset3
-- Task:
    -- Keluarkan Toko di Wilayah:
        -- 1 - DKI
        -- 41 - BALI
        -- Omset Bulan Okt2019

/*
TABLE STRUCTURE:

---------------------------------------------------------
Divisi Produk | DEPO/CABANG/WILAYAH | LT | QTY SDA | Rp |
---------------------------------------------------------
*/

WITH cte_cust AS (
    SELECT
        customer_key,
        inKdCabang,
        chKetCabang,
        inKdWilayah,
        chKetWilayah,
        inKdDepo,
        chTipeDepo,
        inKdDistinct
    FROM
        dba.lp_mcustomer
    WHERE
        inKdWilayah = 1
    OR  inKdWilayah = 41
),
cte_product AS (
    SELECT
        product_key,
        inKdKelpDivisiNew,
        chKetKelpDivisiNew,
        inKdKonvSda
    FROM
        dba.lp_mproduct
),
cte_product_and_cust AS (
    SELECT
    -- CUSTOMER
        c.customer_key,
        c.inKdCabang,
        c.chKetCabang,
        c.inKdWilayah,
        c.chKetWilayah,
        c.inKdDepo,
        c.chTipeDepo,
        c.inKdDistinct, -- LT
    -- PRODUCT
        p.product_key,
        p.inKdKelpDivisiNew,
        p.chKetKelpDivisiNew,
        p.inKdKonvSda, -- QTY SDA

        t.deQtyNetto,
        t.deRpNetto
    FROM
        dba.dm_tjual_mon t
    LEFT JOIN
        cte_cust c ON t.customer_key = c.customer_key
    LEFT JOIN
        cte_product p ON t.product_key = p.product_key
    WHERE
        t.inBulan = 10
    AND
        t.inTahun = 2019
),
cte_depo AS (
    SELECT
        d.subq_kelp_divisi_depo AS 'kelp_divisi_depo',
        d.subq_ket_depo AS 'ket_depo',
        SUM(d.subq_lt_depo) AS 'lt_depo',
        SUM(d.subq_qty_sda_depo) AS 'qty_sda_depo',
        SUM(d.subq_rp_depo) AS 'rp_depo',
        d.subq_helper_col_depo AS 'helper_col_depo'
    FROM (
        SELECT
            CAST(pc.inKdKelpDivisiNew AS VARCHAR) + '-' + pc.chKetKelpDivisiNew AS 'subq_kelp_divisi_depo',
            RIGHT('0' + CAST(pc.inKdCabang AS VARCHAR), 2) + RIGHT('0' + CAST(pc.inKdDepo AS VARCHAR), 2) + '-' + pc.chTipeDepo AS 'subq_ket_depo',
            COUNT(DISTINCT pc.inKdDistinct) AS 'subq_lt_depo',
            SUM(pc.deQtyNetto/pc.inKdKonvSda) AS 'subq_qty_sda_depo',
            pc.deRpNetto AS 'subq_rp_depo',
            CAST(pc.inKdKelpDivisiNew AS VARCHAR) + -- kelp divisi (1 char)
            COALESCE(RIGHT('0' + CAST(pc.inKdWilayah AS VARCHAR), 2), 'zz') + -- wilayah (2 char)
            COALESCE(RIGHT('0' + CAST(pc.inKdCabang AS VARCHAR), 2), 'zz') + -- cabang (2 char)
            COALESCE(RIGHT('0' + CAST(pc.inKdCabang AS VARCHAR), 2), 'zz') + COALESCE(RIGHT('0' + CAST(pc.inKdDepo AS VARCHAR), 2), 'zz') -- kode depo (4 char: 2 char cabang + 2 char depo)
            AS 'subq_helper_col_depo'
        FROM
            cte_product_and_cust pc
        GROUP BY
            pc.inKdWilayah,
            pc.inKdCabang,
            pc.inKdDepo,
            pc.chTipeDepo,
            pc.deRpNetto,
            pc.inKdKelpDivisiNew,
            pc.chKetKelpDivisiNew
    ) d
    GROUP BY
        ket_depo,
        kelp_divisi_depo,
        helper_col_depo
),
cte_area AS (
    SELECT
        a.subq_kelp_divisi_area AS 'kelp_divisi_area',
        a.subq_ket_area AS 'ket_area',
        SUM(a.subq_lt_area) AS 'lt_area',
        SUM(a.subq_qty_sda_area) AS 'qty_sda_area',
        SUM(a.subq_rp_area) AS 'rp_area',
        a.subq_helper_col_area AS 'helper_col_area'
    FROM (
        SELECT
            CAST(pc.inKdKelpDivisiNew AS VARCHAR) + '-' + pc.chKetKelpDivisiNew AS 'subq_kelp_divisi_area',
            RIGHT('0' + CAST(pc.inKdCabang AS VARCHAR), 2) + '-AREA ' + pc.chKetCabang AS 'subq_ket_area',
            COUNT(DISTINCT pc.inKdDistinct) AS 'subq_lt_area',
            SUM(pc.deQtyNetto/pc.inKdKonvSda) AS 'subq_qty_sda_area',
            pc.deRpNetto AS 'subq_rp_area',
            CAST(pc.inKdKelpDivisiNew AS VARCHAR) + -- kelp divisi (1 char)
            COALESCE(RIGHT('0' + CAST(pc.inKdWilayah AS VARCHAR), 2), 'zz') + -- wilayah (2 char)
            COALESCE(RIGHT('0' + CAST(pc.inKdCabang AS VARCHAR), 2), 'zz') + -- cabang (2 char)
            'zzzz' -- kode depo (4 char: 2 char cabang + 2 char depo)
            AS 'subq_helper_col_area'
        FROM
            cte_product_and_cust pc
        GROUP BY
            pc.inKdWilayah,
            pc.inKdCabang,
            pc.chKetCabang,
            pc.deRpNetto,
            pc.inKdKelpDivisiNew,
            pc.chKetKelpDivisinew
    ) a
    GROUP BY
        ket_area,
        kelp_divisi_area,
        helper_col_area
),
cte_wilayah AS (
    SELECT
        w.subq_kelp_divisi_wilayah AS 'kelp_divisi_wilayah',
        w.subq_ket_wilayah AS 'ket_wilayah',
        SUM(w.subq_lt_wilayah) AS 'lt_wilayah',
        SUM(w.subq_qty_sda_wilayah) AS 'qty_sda_wilayah',
        SUM(w.subq_rp_wilayah) AS 'rp_wilayah',
        w.subq_helper_col_wilayah AS 'helper_col_wilayah'
    FROM (
        SELECT
            CAST(pc.inKdKelpDivisiNew AS VARCHAR) + '-' + pc.chKetKelpDivisiNew AS 'subq_kelp_divisi_wilayah',
            RIGHT('0' + CAST(pc.inKdWilayah AS VARCHAR), 2) + '-' + pc.chKetWilayah AS 'subq_ket_wilayah',
            COUNT(DISTINCT pc.inKdDistinct) AS 'subq_lt_wilayah',
            SUM(pc.deQtyNetto/pc.inKdKonvSda) AS 'subq_qty_sda_wilayah',
            pc.deRpNetto AS 'subq_rp_wilayah',
            CAST(pc.inKdKelpDivisiNew AS VARCHAR) + -- kelp divisi (1 char)
            COALESCE(RIGHT('0' + CAST(pc.inKdWilayah AS VARCHAR), 2), 'zz') + -- wilayah (2 char)
            'zz' + -- cabang (2 char)
            'zzzz' -- kode depo (4 char: 2 char cabang + 2 char depo)
            AS 'subq_helper_col_wilayah'
        FROM
            cte_product_and_cust pc
        GROUP BY
            pc.inKdKelpDivisiNew,
            pc.inKdWilayah,
            pc.chKetWilayah,
            pc.deRpNetto,
            pc.inKdKelpDivisiNew,
            pc.chKetKelpDivisiNew
    ) w
    GROUP BY
        ket_wilayah,
        kelp_divisi_wilayah,
        helper_col_wilayah
),
cte_total AS (
    SELECT
        tot.subq_kelp_divisi_total AS 'kelp_divisi_total',
        'TOTAL' AS 'total_col',
        SUM(tot.subq_lt_total) AS 'lt_total',
        SUM(tot.subq_qty_sda_total) AS 'qty_sda_total',
        SUM(tot.subq_rp_total) AS 'rp_total',
        tot.subq_helper_col_total AS 'helper_col_total'
    FROM (
        SELECT
            CAST(pc.inKdKelpDivisiNew AS VARCHAR) + '-' + pc.chKetKelpDivisiNew AS 'subq_kelp_divisi_total',
            COUNT(DISTINCT pc.inKdDistinct) AS 'subq_lt_total',
            SUM(pc.deQtyNetto/pc.inKdKonvSda) AS 'subq_qty_sda_total',
            pc.deRpNetto AS 'subq_rp_total',
            CAST(pc.inKdKelpDivisiNew AS VARCHAR) + -- kelp divisi (1 char)
            'zz' + -- wilayah (2 char)
            'zz' + -- cabang (2 char)
            'zzzz' -- kode depo (4 char: 2 char cabang + 2 char depo)
            AS 'subq_helper_col_total'
        FROM
            cte_product_and_cust pc
        GROUP BY
            pc.inKdKelpDivisiNew,
            pc.chKetKelpDivisiNew,
            pc.deRpNetto
    ) tot
    GROUP BY
        kelp_divisi_total,
        helper_col_total
),
cte_grand_total AS (
    SELECT
        '' AS '',
        'GRAND TOTAL' AS 'grand_total',
        SUM(tot.lt_total) AS 'grand_total_lt',
        SUM(tot.qty_sda_total) AS 'grand_total_qty_sda',
        SUM(tot.rp_total) AS 'grand_total_rp',
        'z' + -- kelp divisi (1 char)
        'zz' + -- wilayah (2 char)
        'zz' + -- cabang (2 char)
        'zzzz' -- kode depo (4 char: 2 char cabang + 2 char depo)
        AS 'subq_helper_col_total'
    FROM
        cte_total tot
)
SELECT
    kelp_divisi_depo AS 'Divisi Produk',
    ket_depo AS 'DEPO/CABANG/WILAYAH',
    COALESCE(lt_depo, 0) AS 'LT',
    qty_sda_depo AS 'QTY SDA',
    rp_depo AS 'Rp',
    helper_col_depo AS 'helper_col'
FROM
    cte_depo
UNION ALL
SELECT
    kelp_divisi_area,
    ket_area,
    lt_area,
    qty_sda_area,
    rp_area,
    helper_col_area
FROM
    cte_area
UNION ALL
SELECT
    kelp_divisi_wilayah,
    ket_wilayah,
    lt_wilayah,
    qty_sda_wilayah,
    rp_wilayah,
    helper_col_wilayah
FROM
    cte_wilayah
UNION ALL
SELECT
    *
FROM
    cte_total
UNION ALL
SELECT
    *
FROM
    cte_grand_total
ORDER BY
    6