Query Report Op Prod

mail@pastecode.io avatar
unknown
sqlserver
21 days ago
119 kB
1
Indexable
Never
BEGIN
DECLARE @tglStart datetime
DECLARE @tglEnd datetime
SET @tglStart='2024-7-15 00:00:00'
SET @tglEnd='2024-09-30 23:59:59'
SELECT
  a.nomor_spk as Nomor_SPK,
  a.nama_pembeli as Nama_Pembeli,
  Merk,
  Model,
  Tipe,
  [APPID],
  [MID],
  --a.APPID,
  --a.MID,
  a.PreApprove,
  [Dealer Name],
  a.Kanwil,
  a.[KCU],
  (CASE WHEN KCP like '%(KCU)' THEN '' ELSE REPLACE(KCP,'(KCP)','') END) as KCP,
  [Jumlah Unit],
  [OTR Pakai(M)],
  [Unit*OTR],
  a.createddate as [Tanggal Input],
  a.TglApproveSPKDealer as [Tanggal Approve SPK],
  [Dealer Id],
  a.reqIdVM as [IDVM],
  CASE
    WHEN [IDVM] IS NOT NULL THEN 'PARTISIPAN'
    ELSE [Dealer Type]
  END AS [Dealer Type],
  --[Dealer Type],
  CASE 
    WHEN UPPER(c.[Ket Event]) = 'EXPO'
      THEN 
        CASE 
          WHEN a.Kanwil LIKE '%D%'
            THEN
              CASE
                WHEN [Dealer Type] LIKE 'NON PARTISIPAN. PARTISIPAN KHUSUS KANWIL%' THEN
                  CASE
                    WHEN c.Kanwil IN (
                      SELECT 
                        LTRIM(RTRIM(Item))
                      FROM 
                        dbo.fnSplitString(SUBSTRING([Dealer Type], CHARINDEX('KANWIL', [Dealer Type]) + LEN('KANWIL '), LEN([Dealer Type])), ',')
                      WHERE
                        TRY_CAST(LTRIM(RTRIM(Item)) AS INT) IS NOT NULL
                    )
                      THEN
                        CASE
                          WHEN (
                            [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                          ) THEN 'SPKONLINE JK REVISI DARI GATH PRESELL VM'
                          WHEN (
                            [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                          ) THEN 'SPKONLINE'
                          WHEN (
                            [IDVM] IS NULL
                            AND a.createddate BETWEEN c.StartDate AND c.EndDate
                            AND a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate
                          ) THEN 'EXPOVERSARY'
                          WHEN (
                            [IDVM] IS NULL
                            AND (((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate))
                            OR ((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate))
                            OR ((a.createddate BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate)))
                          ) THEN 'SPECIALRATE'
                          ELSE 'GATHERING'
                        END
                      ELSE 
                        CASE 
                          WHEN [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove
                          THEN (
                            CASE 
                              WHEN d1.PAKET_ID IS NOT NULL 
                                THEN d12.NAMA_PAKET
                              ELSE 'GATHERING'
                            END
                          )
                          WHEN [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                          THEN (
                            CASE 
                              WHEN d3.PAKET_ID IS NOT NULL 
                                THEN d32.NAMA_PAKET
                              ELSE 'GATHERING'
                            END
                          )
                          ELSE 'GATHERING'
                        END
                  END
                WHEN [Dealer Type] LIKE 'PARTISIPAN. PARTISIPAN BUNGA SAJA KHUSUS KANWIL%' THEN
                  CASE
                    WHEN c.Kanwil IN (
                      SELECT 
                        LTRIM(RTRIM(Item))
                      FROM 
                        dbo.fnSplitString(SUBSTRING([Dealer Type], CHARINDEX('KANWIL', [Dealer Type]) + LEN('KANWIL '), LEN([Dealer Type])), ',')
                      WHERE
                        TRY_CAST(LTRIM(RTRIM(Item)) AS INT) IS NOT NULL
                    )
                    THEN 
                      CASE       
                        WHEN (
                          [IDVM] IS NULL
                        ) THEN 'SPECIALRATE'       
                        WHEN (
                          [IDVM] IS NOT NULL
                          AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                          AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                        ) THEN 'SPKONLINE JK REVISI DARI GATH PRESELL VM'
                        WHEN (
                          [IDVM] IS NOT NULL
                          AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                          AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                        ) THEN 'SPKONLINE'
                        ELSE 'GATHERING'
                      END
                    ELSE 
                      CASE
                        WHEN (
                          [IDVM] IS NOT NULL
                          AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                          AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                        ) THEN 'SPKONLINE JK REVISI DARI GATH PRESELL VM'
                        WHEN (
                          [IDVM] IS NOT NULL
                          AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                          AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                        ) THEN 'SPKONLINE'
                        WHEN (
                          [IDVM] IS NULL
                          AND a.createddate BETWEEN c.StartDate AND c.EndDate
                          AND a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate
                        ) THEN 'EXPOVERSARY'
                        WHEN (
                          [IDVM] IS NULL
                          AND (((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate))
                          OR ((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate))
                          OR ((a.createddate BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate)))
                        ) THEN 'SPECIALRATE'
                        ELSE 'GATHERING'
                      END
                  END
                WHEN (
                  --[Dealer Type] = 'PARTISIPAN'
                    [IDVM] IS NOT NULL
                    AND (a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput) 
                    AND (a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove)
                  )
                  THEN (
                    CASE 
                      WHEN d1.PAKET_ID IS NOT NULL 
                        THEN d12.NAMA_PAKET
                        --THEN 'p1'
                      ELSE 'GATHERING'
                    END
                  )
                WHEN
                  [Dealer Type] = 'NON PARTISIPAN' 
                  THEN
                    CASE 
                      WHEN [IDVM] IS NOT NULL
                        AND a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput
                        AND a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove
                      THEN (
                        CASE 
                          WHEN d1.PAKET_ID IS NOT NULL 
                            THEN d12.NAMA_PAKET
                          ELSE 'GATHERING'
                        END
                      )
                      WHEN [IDVM] IS NOT NULL
                        AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                        AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                      THEN (
                        CASE 
                          WHEN d3.PAKET_ID IS NOT NULL 
                            THEN d32.NAMA_PAKET
                          ELSE 'GATHERING'
                        END
                      )
                      ELSE 'GATHERING'
                    END
                WHEN (
                  --[Dealer Type] = 'PARTISIPAN' 
                  [IDVM] IS NOT NULL
                  AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                  AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                )
                  THEN (
                    CASE 
                      WHEN d3.PAKET_ID IS NOT NULL 
                        THEN d32.NAMA_PAKET
                        --THEN 'p3'
                      ELSE 'GATHERING'
                    END
                  )
                WHEN (
                  [Dealer Type] = 'PARTISIPAN' 
                  AND [IDVM] IS NULL
                  AND a.createddate BETWEEN c.StartDate AND c.EndDate
                  AND a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate
                )
                  THEN (
                    CASE 
                      WHEN d4.PAKET_ID IS NOT NULL 
                        THEN d42.NAMA_PAKET
                        --THEN 'p4'
                      ELSE 'GATHERING'
                    END
                  )
                WHEN (
                  [Dealer Type] = 'PARTISIPAN'
                  AND [IDVM] IS NULL
                  AND (((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate))
                  OR ((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate))
                  OR ((a.createddate BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate)))
                ) THEN (
                  CASE 
                    WHEN d5.PAKET_ID IS NOT NULL 
                      THEN d52.NAMA_PAKET
                      --THEN 'p5'
                    ELSE 'GATHERING'
                  END
                )
                WHEN (
                  [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
                  AND [IDVM] IS NULL
                )
                THEN (
                  CASE 
                    WHEN d7.PAKET_ID IS NOT NULL 
                      THEN d72.NAMA_PAKET
                    --THEN 'p7'
                    ELSE 'GATHERING'
                  END
                )
                WHEN (
                  [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
                  AND [IDVM] IS NOT NULL
                  AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                  AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                )
                THEN (
                  CASE 
                    WHEN d8.PAKET_ID IS NOT NULL 
                      THEN d82.NAMA_PAKET
                      --THEN 'p8'
                    ELSE 'GATHERING'
                  END
                )
                WHEN (
                  [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
                  AND [IDVM] IS NOT NULL
                  AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                  AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                )
                THEN (
                  CASE 
                    WHEN d9.PAKET_ID IS NOT NULL 
                      THEN d92.NAMA_PAKET
                      --THEN 'p9'
                    ELSE 'GATHERING'
                  END
                )
              ELSE 'GATHERING'
          END

          -- IF EXPO AND KANWIL NOT IN D
          ELSE
            CASE
              WHEN
                [Dealer Type] LIKE 'NON PARTISIPAN. PARTISIPAN KHUSUS KANWIL%' THEN
                  CASE
                    WHEN a.Kanwil IN (
                      SELECT 
                        LTRIM(RTRIM(Item))
                      FROM 
                        dbo.fnSplitString(SUBSTRING([Dealer Type], CHARINDEX('KANWIL', [Dealer Type]) + LEN('KANWIL '), LEN([Dealer Type])), ',')
                      WHERE
                        TRY_CAST(LTRIM(RTRIM(Item)) AS INT) IS NOT NULL
                    )
                    THEN
                      CASE
                        WHEN (
                          [IDVM] IS NOT NULL
                          AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                          AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                        ) THEN 'SPKONLINE JK REVISI DARI GATH PRESELL VM'
                        WHEN (
                          [IDVM] IS NOT NULL
                          AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                          AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                        ) THEN 'SPKONLINE'
                        WHEN (
                          [IDVM] IS NULL
                          AND a.createddate BETWEEN d4.StartDateInput AND d4.EndDateInput
                          AND a.TglApproveSPKDealer BETWEEN d4.StartDateApprove AND d4.EndDateApprove
                        ) THEN 'EXPOVERSARY'
                        WHEN (
                          [IDVM] IS NULL
                          AND (((a.createddate NOT BETWEEN d5.StartDateInput AND d5.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d5.StartDateApprove AND d5.EndDateApprove))
                          OR ((a.createddate NOT BETWEEN d5.StartDateInput AND d5.EndDateInput) AND (a.TglApproveSPKDealer BETWEEN d5.StartDateApprove AND d5.EndDateApprove))
                          OR ((a.createddate BETWEEN d5.StartDateInput AND d5.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d5.StartDateApprove AND d5.EndDateApprove)))
                        ) THEN 'SPECIALRATE'
                        ELSE 'GATHERING'
                      END
                    ELSE 
                      CASE 
                          WHEN [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove
                          THEN (
                            CASE 
                              WHEN d1.PAKET_ID IS NOT NULL 
                                THEN d12.NAMA_PAKET
                              ELSE 'GATHERING'
                            END
                          )
                          WHEN [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                          THEN (
                            CASE 
                              WHEN d3.PAKET_ID IS NOT NULL 
                                THEN d32.NAMA_PAKET
                              ELSE 'GATHERING'
                            END
                          )
                          ELSE 'GATHERING'
                        END
                  END
              WHEN 
                [Dealer Type] LIKE 'PARTISIPAN. PARTISIPAN BUNGA SAJA KHUSUS KANWIL%' THEN
                  CASE
                    WHEN a.Kanwil IN (
                      SELECT 
                        LTRIM(RTRIM(Item))
                      FROM 
                        dbo.fnSplitString(SUBSTRING([Dealer Type], CHARINDEX('KANWIL', [Dealer Type]) + LEN('KANWIL '), LEN([Dealer Type])), ',')
                      WHERE
                        TRY_CAST(LTRIM(RTRIM(Item)) AS INT) IS NOT NULL
                    )
                    THEN 
                      CASE       
                        WHEN (
                          [IDVM] IS NULL
                        ) THEN 'SPECIALRATE'       
                        WHEN (
                          [IDVM] IS NOT NULL
                          AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                          AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                        ) THEN 'SPKONLINE JK REVISI DARI GATH PRESELL VM'
                        WHEN (
                          [IDVM] IS NOT NULL
                          AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                          AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                        ) THEN 'SPKONLINE'
                        ELSE 'GATHERING'
                      END
                    ELSE 
                      CASE
                        WHEN (
                          [IDVM] IS NOT NULL
                          AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                          AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                        ) THEN 'SPKONLINE JK REVISI DARI GATH PRESELL VM'
                        WHEN (
                          [IDVM] IS NOT NULL
                          AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                          AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                        ) THEN 'SPKONLINE'
                        WHEN (
                          [IDVM] IS NULL
                          AND a.createddate BETWEEN d4.StartDateInput AND d4.EndDateInput
                          AND a.TglApproveSPKDealer BETWEEN d4.StartDateApprove AND d4.EndDateApprove
                        ) THEN 'EXPOVERSARY'
                        WHEN (
                          [IDVM] IS NULL
                          AND (((a.createddate NOT BETWEEN d5.StartDateInput AND d5.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d5.StartDateApprove AND d5.EndDateApprove))
                          OR ((a.createddate NOT BETWEEN d5.StartDateInput AND d5.EndDateInput) AND (a.TglApproveSPKDealer BETWEEN d5.StartDateApprove AND d5.EndDateApprove))
                          OR ((a.createddate BETWEEN d5.StartDateInput AND d5.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d5.StartDateApprove AND d5.EndDateApprove)))
                        ) THEN 'SPECIALRATE'
                        ELSE 'GATHERING'
                      END
                  END
                WHEN (
                --[Dealer Type] = 'PARTISIPAN'
                [IDVM] IS NOT NULL
                AND (a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput) 
                AND (a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove)
                )
                THEN (
                  CASE 
                    WHEN d1.PAKET_ID IS NOT NULL 
                      THEN d12.NAMA_PAKET
                      --THEN 'p1'
                    ELSE 'GATHERING'
                  END
                )
                WHEN
                  [Dealer Type] = 'NON PARTISIPAN' 
                  THEN
                    CASE 
                      WHEN [IDVM] IS NOT NULL
                        AND a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput
                        AND a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove
                      THEN (
                        CASE 
                          WHEN d1.PAKET_ID IS NOT NULL 
                            THEN d12.NAMA_PAKET
                          ELSE 'GATHERING'
                        END
                      )
                      WHEN [IDVM] IS NOT NULL
                        AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                        AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                      THEN (
                        CASE 
                          WHEN d3.PAKET_ID IS NOT NULL 
                            THEN d32.NAMA_PAKET
                          ELSE 'GATHERING'
                        END
                      )
                      ELSE 'GATHERING'
                    END
                WHEN (
                  --[Dealer Type] = 'PARTISIPAN' 
                  [IDVM] IS NOT NULL
                  AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                  AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                )
                  THEN (
                    CASE 
                      WHEN d3.PAKET_ID IS NOT NULL 
                        THEN d32.NAMA_PAKET
                        --THEN 'p3'
                      ELSE 'GATHERING'
                    END
                  )
                WHEN (
                  [Dealer Type] = 'PARTISIPAN' 
                  AND [IDVM] IS NULL
                  AND a.createddate BETWEEN d4.StartDateInput AND d4.EndDateInput
                  AND a.TglApproveSPKDealer BETWEEN d4.StartDateApprove AND d4.EndDateApprove
                )
                  THEN (
                    CASE 
                      WHEN d4.PAKET_ID IS NOT NULL 
                        THEN d42.NAMA_PAKET
                        --THEN 'p4'
                      ELSE 'GATHERING'
                    END
                  )
                WHEN (
                  [Dealer Type] = 'PARTISIPAN'
                  AND [IDVM] IS NULL
                  AND (((a.createddate NOT BETWEEN d5.StartDateInput AND d5.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d5.StartDateApprove AND d5.EndDateApprove))
                  OR ((a.createddate NOT BETWEEN d5.StartDateInput AND d5.EndDateInput) AND (a.TglApproveSPKDealer BETWEEN d5.StartDateApprove AND d5.EndDateApprove))
                  OR ((a.createddate BETWEEN d5.StartDateInput AND d5.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d5.StartDateApprove AND d5.EndDateApprove)))
                ) THEN (
                  CASE 
                    WHEN d5.PAKET_ID IS NOT NULL 
                      THEN d52.NAMA_PAKET
                      --THEN 'p5'
                    ELSE 'GATHERING'
                  END
                )
                WHEN (
                  [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
                  AND [IDVM] IS NULL
                  --AND (((a.createddate NOT BETWEEN d7.StartDateInput AND d7.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d7.StartDateApprove AND d7.EndDateApprove))
                  --OR ((a.createddate BETWEEN d7.StartDateInput AND d7.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d7.StartDateApprove AND d7.EndDateApprove)))
                )
                THEN (
                  CASE 
                    WHEN d7.PAKET_ID IS NOT NULL 
                      THEN d72.NAMA_PAKET
                    --THEN 'p7'
                    ELSE 'GATHERING'
                  END
                )
                WHEN (
                  [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
                  AND [IDVM] IS NOT NULL
                  AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                  AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                )
                THEN (
                  CASE 
                    WHEN d8.PAKET_ID IS NOT NULL 
                      THEN d82.NAMA_PAKET
                      --THEN 'p8'
                    ELSE 'GATHERING'
                  END
                )
                WHEN (
                  [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
                  AND [IDVM] IS NOT NULL
                  AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                  AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                )
                THEN (
                  CASE 
                    WHEN d9.PAKET_ID IS NOT NULL 
                      THEN d92.NAMA_PAKET
                      --THEN 'p9'
                    ELSE 'GATHERING'
                  END
                )
                ELSE 'GATHERING'
            END
        END

    WHEN UPPER(c.[Ket Event]) = 'AUTOSHOW'
      THEN
        CASE
          WHEN a.Kanwil LIKE '%D%'
            THEN
              CASE
                WHEN [Dealer Type] LIKE 'NON PARTISIPAN. PARTISIPAN KHUSUS KANWIL%' THEN
                  CASE
                    WHEN c.Kanwil IN (
                      SELECT 
                        LTRIM(RTRIM(Item))
                      FROM 
                        dbo.fnSplitString(SUBSTRING([Dealer Type], CHARINDEX('KANWIL', [Dealer Type]) + LEN('KANWIL '), LEN([Dealer Type])), ',')
                      WHERE
                        TRY_CAST(LTRIM(RTRIM(Item)) AS INT) IS NOT NULL
                    )
                    THEN
                      CASE
                        WHEN (
                          [IDVM] IS NOT NULL
                          AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                          AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                        ) THEN 'SPKONLINE JK REVISI DARI GATH PRESELL VM'
                        WHEN (
                          [IDVM] IS NOT NULL
                          AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                          AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                        ) THEN 'SPKONLINE'
                        WHEN (
                          [IDVM] IS NULL
                          AND a.createddate BETWEEN c.StartDate AND c.EndDate
                          AND a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate
                        ) THEN 'EXPOVERSARY'
                        WHEN (
                          [IDVM] IS NULL
                          AND (((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate))
                          OR ((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate))
                          OR ((a.createddate BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate)))
                        ) THEN 'SPECIALRATE'
                        ELSE 'GATHERING'
                      END
                    ELSE 
                      CASE 
                          WHEN [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove
                          THEN (
                            CASE 
                              WHEN d1.PAKET_ID IS NOT NULL 
                                THEN d12.NAMA_PAKET
                              ELSE 'GATHERING'
                            END
                          )
                          WHEN [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                          THEN (
                            CASE 
                              WHEN d3.PAKET_ID IS NOT NULL 
                                THEN d32.NAMA_PAKET
                              ELSE 'GATHERING'
                            END
                          )
                          ELSE 'GATHERING'
                        END
                  END
                WHEN 
                  [Dealer Type] LIKE 'PARTISIPAN. PARTISIPAN BUNGA SAJA KHUSUS KANWIL%' THEN
                    CASE
                      WHEN c.Kanwil IN (
                        SELECT 
                          LTRIM(RTRIM(Item))
                        FROM 
                          dbo.fnSplitString(SUBSTRING([Dealer Type], CHARINDEX('KANWIL', [Dealer Type]) + LEN('KANWIL '), LEN([Dealer Type])), ',')
                        WHERE
                          TRY_CAST(LTRIM(RTRIM(Item)) AS INT) IS NOT NULL
                      )
                      THEN 
                        CASE       
                          WHEN (
                            [IDVM] IS NULL
                          ) THEN 'SPECIALRATE'       
                          WHEN (
                            [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                          ) THEN 'SPKONLINE JK REVISI DARI GATH PRESELL VM'
                          WHEN (
                            [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                          ) THEN 'SPKONLINE'
                          ELSE 'GATHERING'
                        END
                      ELSE 
                        CASE
                          WHEN (
                            [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                          ) THEN 'SPKONLINE JK REVISI DARI GATH PRESELL VM'
                          WHEN (
                            [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                          ) THEN 'SPKONLINE'
                          WHEN (
                            [IDVM] IS NULL
                            AND a.createddate BETWEEN c.StartDate AND c.EndDate
                            AND a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate
                          ) THEN 'EXPOVERSARY'
                          WHEN (
                            [IDVM] IS NULL
                            AND (((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate))
                            OR ((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate))
                            OR ((a.createddate BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate)))
                          ) THEN 'SPECIALRATE'
                          ELSE 'GATHERING'
                        END
                    END
                WHEN (
                --[Dealer Type] = 'PARTISIPAN'
                [IDVM] IS NOT NULL
                AND (a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput) 
                AND (a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove)
                )
                THEN (
                  CASE 
                    WHEN d1.PAKET_ID IS NOT NULL 
                      THEN d12.NAMA_PAKET
                      --THEN 'p1'
                    ELSE 'GATHERING'
                  END
                )
                WHEN
                  [Dealer Type] = 'NON PARTISIPAN' 
                  THEN
                    CASE 
                      WHEN [IDVM] IS NOT NULL
                        AND a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput
                        AND a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove
                      THEN (
                        CASE 
                          WHEN d1.PAKET_ID IS NOT NULL 
                            THEN d12.NAMA_PAKET
                          ELSE 'GATHERING'
                        END
                      )
                      WHEN [IDVM] IS NOT NULL
                        AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                        AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                      THEN (
                        CASE 
                          WHEN d3.PAKET_ID IS NOT NULL 
                            THEN d32.NAMA_PAKET
                          ELSE 'GATHERING'
                        END
                      )
                      ELSE 'GATHERING'
                    END
                WHEN (
                  --[Dealer Type] = 'PARTISIPAN' 
                  [IDVM] IS NOT NULL
                  AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                  AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                )
                  THEN (
                    CASE 
                      WHEN d3.PAKET_ID IS NOT NULL 
                        THEN d32.NAMA_PAKET
                        --THEN 'p3'
                      ELSE 'GATHERING'
                    END
                  )
                WHEN (
                [Dealer Type] = 'PARTISIPAN'
                    AND [IDVM] IS NULL
                    AND a.createddate BETWEEN c.StartDate AND c.EndDate
                    AND a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate
                  )
                  THEN (
                    CASE 
                      WHEN d10.PAKET_ID IS NOT NULL 
                        THEN d102.NAMA_PAKET  
                      ELSE 'GATHERING'
                    END
                  )
                WHEN (
                  [Dealer Type] = 'PARTISIPAN'
                  AND [IDVM] IS NULL
                  AND (((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate))
                  OR ((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate))
                  OR ((a.createddate BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate)))
                )
                THEN (
                  CASE 
                    WHEN d5.PAKET_ID IS NOT NULL 
                      THEN d52.NAMA_PAKET
                      --THEN 'p5'
                    ELSE 'GATHERING'
                  END
                )
                WHEN (
                  [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
                  AND [IDVM] IS NULL
                )
                THEN (
                  CASE 
                    WHEN d7.PAKET_ID IS NOT NULL 
                      THEN d72.NAMA_PAKET
                    --THEN 'p7'
                    ELSE 'GATHERING'
                  END
                )
                WHEN (
                  [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
                  AND [IDVM] IS NOT NULL
                  AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                  AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                )
                THEN (
                  CASE 
                    WHEN d8.PAKET_ID IS NOT NULL 
                      THEN d82.NAMA_PAKET
                      --THEN 'p8'
                    ELSE 'GATHERING'
                  END
                )
                WHEN (
                  [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
                  AND [IDVM] IS NOT NULL
                  AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                  AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                )
                THEN (
                  CASE 
                    WHEN d9.PAKET_ID IS NOT NULL 
                      THEN d92.NAMA_PAKET
                      --THEN 'p9'
                    ELSE 'GATHERING'
                  END
                )
              ELSE 'GATHERING'
          END

    -- IF AUTOSHOW AND KANWIL NOT IN D
    ELSE
      CASE
        WHEN
            [Dealer Type] LIKE 'NON PARTISIPAN. PARTISIPAN KHUSUS KANWIL%' THEN
              CASE
                WHEN a.Kanwil IN (
                  SELECT 
                    LTRIM(RTRIM(Item))
                  FROM 
                    dbo.fnSplitString(SUBSTRING([Dealer Type], CHARINDEX('KANWIL', [Dealer Type]) + LEN('KANWIL '), LEN([Dealer Type])), ',')
                  WHERE
                    TRY_CAST(LTRIM(RTRIM(Item)) AS INT) IS NOT NULL
                )
                THEN
                  CASE
                    WHEN (
                      [IDVM] IS NOT NULL
                      AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                      AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                    ) THEN 'SPKONLINE JK REVISI DARI GATH PRESELL VM'
                    WHEN (
                      [IDVM] IS NOT NULL
                      AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                      AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                    ) THEN 'SPKONLINE'
                    WHEN (
                      [IDVM] IS NULL
                      AND a.createddate BETWEEN c.StartDate AND c.EndDate
                      AND a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate
                    ) THEN 'EXPOVERSARY'
                    WHEN (
                      [IDVM] IS NULL
                      AND (((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate))
                      OR ((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate))
                      OR ((a.createddate BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate)))
                    ) THEN 'SPECIALRATE'
                    ELSE 'GATHERING'
                  END
                ELSE
                  CASE 
                    WHEN [IDVM] IS NOT NULL
                      AND a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput
                      AND a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove
                    THEN (
                      CASE 
                        WHEN d1.PAKET_ID IS NOT NULL 
                          THEN d12.NAMA_PAKET
                        ELSE 'GATHERING'
                      END
                    )
                    WHEN [IDVM] IS NOT NULL
                      AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                      AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                    THEN (
                      CASE 
                        WHEN d3.PAKET_ID IS NOT NULL 
                          THEN d32.NAMA_PAKET
                        ELSE 'GATHERING'
                      END
                    )
                    ELSE 'GATHERING'
                  END
              END
        WHEN 
          [Dealer Type] LIKE 'PARTISIPAN. PARTISIPAN BUNGA SAJA KHUSUS KANWIL%' THEN
            CASE
              WHEN a.Kanwil IN (
                SELECT 
                  LTRIM(RTRIM(Item))
                FROM 
                  dbo.fnSplitString(SUBSTRING([Dealer Type], CHARINDEX('KANWIL', [Dealer Type]) + LEN('KANWIL '), LEN([Dealer Type])), ',')
                WHERE
                  TRY_CAST(LTRIM(RTRIM(Item)) AS INT) IS NOT NULL
              )
              THEN 
                CASE       
                  WHEN (
                    [IDVM] IS NULL
                  ) THEN 'SPECIALRATE'       
                  WHEN (
                    [IDVM] IS NOT NULL
                    AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                    AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                  ) THEN 'SPKONLINE JK REVISI DARI GATH PRESELL VM'
                  WHEN (
                    [IDVM] IS NOT NULL
                    AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                    AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                  ) THEN 'SPKONLINE'
                  ELSE 'GATHERING'
                END
              ELSE 
                CASE
                  WHEN (
                    [IDVM] IS NOT NULL
                    AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                    AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                  ) THEN 'SPKONLINE JK REVISI DARI GATH PRESELL VM'
                  WHEN (
                    [IDVM] IS NOT NULL
                    AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                    AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                  ) THEN 'SPKONLINE'
                  WHEN (
                    [IDVM] IS NULL
                    AND a.createddate BETWEEN c.StartDate AND c.EndDate
                    AND a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate
                  ) THEN 'EXPOVERSARY'
                  WHEN (
                    [IDVM] IS NULL
                    AND (((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate))
                    OR ((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate))
                    OR ((a.createddate BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate)))
                  ) THEN 'SPECIALRATE'
                  ELSE 'GATHERING'
                END
            END
        WHEN (
        --[Dealer Type] = 'PARTISIPAN'
        [IDVM] IS NOT NULL
        AND (a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput) 
        AND (a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove)
        )
        THEN (
          CASE 
            WHEN d1.PAKET_ID IS NOT NULL 
              THEN d12.NAMA_PAKET
              --THEN 'p1'
            ELSE 'GATHERING'
          END
        )
        WHEN
          [Dealer Type] = 'NON PARTISIPAN' 
          THEN
            CASE 
              WHEN [IDVM] IS NOT NULL
                AND a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput
                AND a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove
              THEN (
                CASE 
                  WHEN d1.PAKET_ID IS NOT NULL 
                    THEN d12.NAMA_PAKET
                  ELSE 'GATHERING'
                END
              )
              WHEN [IDVM] IS NOT NULL
                AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
              THEN (
                CASE 
                  WHEN d3.PAKET_ID IS NOT NULL 
                    THEN d32.NAMA_PAKET
                  ELSE 'GATHERING'
                END
              )
              ELSE 'GATHERING'
            END
        WHEN (
          --[Dealer Type] = 'PARTISIPAN' 
          [IDVM] IS NOT NULL
          AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
          AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
        )
          THEN (
            CASE 
              WHEN d3.PAKET_ID IS NOT NULL 
                THEN d32.NAMA_PAKET
                --THEN 'p3'
              ELSE 'GATHERING'
            END
          )
        WHEN (
        [Dealer Type] = 'PARTISIPAN'
            AND [IDVM] IS NULL
            AND a.createddate BETWEEN c.StartDate AND c.EndDate
            AND a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate
          )
          THEN (
            CASE 
              WHEN d10.PAKET_ID IS NOT NULL 
                THEN d102.NAMA_PAKET  
              ELSE 'GATHERING'
            END
          )
        WHEN (
          [Dealer Type] = 'PARTISIPAN'
          AND [IDVM] IS NULL
          AND (((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate))
          OR ((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate))
          OR ((a.createddate BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate)))
        )
          THEN (
            CASE 
              WHEN d5.PAKET_ID IS NOT NULL 
                THEN d52.NAMA_PAKET
                --THEN 'p5'
              ELSE 'GATHERING'
            END
          )
        WHEN (
          [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
          AND [IDVM] IS NULL
          --AND (((a.createddate NOT BETWEEN d7.StartDateInput AND d7.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d7.StartDateApprove AND d7.EndDateApprove))
          --OR ((a.createddate BETWEEN d7.StartDateInput AND d7.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d7.StartDateApprove AND d7.EndDateApprove)))
        )
        THEN (
          CASE 
            WHEN d7.PAKET_ID IS NOT NULL 
              THEN d72.NAMA_PAKET
            --THEN 'p7'
            ELSE 'GATHERING'
          END
        )
        WHEN (
          [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
          AND [IDVM] IS NOT NULL
          AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
          AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
        )
        THEN (
          CASE 
            WHEN d8.PAKET_ID IS NOT NULL 
              THEN d82.NAMA_PAKET
              --THEN 'p8'
            ELSE 'GATHERING'
          END
        )
        WHEN (
          [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
          AND [IDVM] IS NOT NULL
          AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
          AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
        )
        THEN (
          CASE 
            WHEN d9.PAKET_ID IS NOT NULL 
              THEN d92.NAMA_PAKET
              --THEN 'p9'
            ELSE 'GATHERING'
          END
        )
        ELSE 'GATHERING'
      END
  END
      -- when c.[Ket Event] = not 'EXPO' or AUTOSHOW, or kanwil not participate/not include in master dealer list
    ELSE 
        CASE
          WHEN (
          [Dealer Type] = 'PARTISIPAN'
          AND [IDVM] IS NULL
          ) THEN (
            CASE 
              WHEN d5.PAKET_ID IS NOT NULL 
                THEN d52.NAMA_PAKET
                --THEN 'p5'
              ELSE 'GATHERING'
            END
          )
          WHEN (
            --[Dealer Type] = 'PARTISIPAN'
            [IDVM] IS NOT NULL
            AND (a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput) 
            AND (a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove)
            ) THEN (
                CASE 
                  WHEN d1.PAKET_ID IS NOT NULL 
                    THEN d12.NAMA_PAKET
                  ELSE 'GATHERING'
                END
            )
          WHEN (
            --[Dealer Type] = 'PARTISIPAN' 
            [IDVM] IS NOT NULL
            AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
            AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
          ) THEN (
              CASE 
                WHEN d3.PAKET_ID IS NOT NULL 
                  THEN d32.NAMA_PAKET
                ELSE 'GATHERING'
              END
          )
          WHEN
            [Dealer Type] = 'NON PARTISIPAN' 
            THEN
              CASE 
                WHEN [IDVM] IS NOT NULL
                  AND a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput
                  AND a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove
                THEN (
                  CASE 
                    WHEN d1.PAKET_ID IS NOT NULL 
                      THEN d12.NAMA_PAKET
                    ELSE 'GATHERING'
                  END
                )
                WHEN [IDVM] IS NOT NULL
                  AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                  AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                THEN (
                  CASE 
                    WHEN d3.PAKET_ID IS NOT NULL 
                      THEN d32.NAMA_PAKET
                    ELSE 'GATHERING'
                  END
                )
                ELSE 'GATHERING'
              END
          WHEN (
            [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
            AND [IDVM] IS NULL
            --AND (((a.createddate NOT BETWEEN d7.StartDateInput AND d7.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d7.StartDateApprove AND d7.EndDateApprove))
            --OR ((a.createddate BETWEEN d7.StartDateInput AND d7.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d7.StartDateApprove AND d7.EndDateApprove)))
          )
          THEN (
            CASE 
              WHEN d7.PAKET_ID IS NOT NULL 
                THEN d72.NAMA_PAKET
              --THEN 'p7'
              ELSE 'GATHERING'
            END
          )
          WHEN (
            [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
            AND [IDVM] IS NOT NULL
            AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
            AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
          )
          THEN (
            CASE 
              WHEN d8.PAKET_ID IS NOT NULL 
                THEN d82.NAMA_PAKET
                --THEN 'p8'
              ELSE 'GATHERING'
            END
          )
          WHEN (
            [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
            AND [IDVM] IS NOT NULL
            AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
            AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
          )
          THEN (
            CASE 
              WHEN d9.PAKET_ID IS NOT NULL 
                THEN d92.NAMA_PAKET
                --THEN 'p9'
              ELSE 'GATHERING'
            END
          )
          WHEN
            [Dealer Type] LIKE 'NON PARTISIPAN. PARTISIPAN KHUSUS KANWIL&' THEN
              CASE
                WHEN a.Kanwil IN (
                  SELECT 
                    LTRIM(RTRIM(Item))
                  FROM 
                    dbo.fnSplitString(SUBSTRING([Dealer Type], CHARINDEX('KANWIL', [Dealer Type]) + LEN('KANWIL '), LEN([Dealer Type])), ',')
                  WHERE
                    TRY_CAST(LTRIM(RTRIM(Item)) AS INT) IS NOT NULL
                )
                THEN
                  CASE
                    WHEN (
                      [IDVM] IS NOT NULL
                      AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                      AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                    ) THEN 'SPKONLINE JK REVISI DARI GATH PRESELL VM'
                    WHEN (
                      [IDVM] IS NOT NULL
                      AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                      AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                    ) THEN 'SPKONLINE'
                    WHEN (
                      [IDVM] IS NULL
                    ) THEN 'SPECIALRATE'
                    ELSE 'GATHERING'
                  END
                ELSE
                  CASE 
                    WHEN [IDVM] IS NOT NULL
                      AND a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput
                      AND a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove
                    THEN (
                      CASE 
                        WHEN d1.PAKET_ID IS NOT NULL 
                          THEN d12.NAMA_PAKET
                        ELSE 'GATHERING'
                      END
                    )
                    WHEN [IDVM] IS NOT NULL
                      AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                      AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                    THEN (
                      CASE 
                        WHEN d3.PAKET_ID IS NOT NULL 
                          THEN d32.NAMA_PAKET
                        ELSE 'GATHERING'
                      END
                    )
                    ELSE 'GATHERING'
                  END
              END
          WHEN 
          [Dealer Type] LIKE 'PARTISIPAN. PARTISIPAN BUNGA SAJA KHUSUS KANWIL%' THEN
            CASE
              WHEN a.Kanwil IN (
                SELECT 
                  LTRIM(RTRIM(Item))
                FROM 
                  dbo.fnSplitString(SUBSTRING([Dealer Type], CHARINDEX('KANWIL', [Dealer Type]) + LEN('KANWIL '), LEN([Dealer Type])), ',')
                WHERE
                  TRY_CAST(LTRIM(RTRIM(Item)) AS INT) IS NOT NULL
              )
              THEN 
                CASE       
                  WHEN (
                    [IDVM] IS NULL
                  ) THEN 'SPECIALRATE'       
                  WHEN (
                    [IDVM] IS NOT NULL
                    AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                    AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                  ) THEN 'SPKONLINE JK REVISI DARI GATH PRESELL VM'
                  WHEN (
                    [IDVM] IS NOT NULL
                    AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                    AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                  ) THEN 'SPKONLINE'
                  ELSE 'GATHERING'
                END
              ELSE 
                CASE
                  WHEN (
                    [IDVM] IS NOT NULL
                    AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                    AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                  ) THEN 'SPKONLINE JK REVISI DARI GATH PRESELL VM'
                  WHEN (
                    [IDVM] IS NOT NULL
                    AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                    AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                  ) THEN 'SPKONLINE'
                  WHEN (
                    [IDVM] IS NULL
                  ) THEN 'SPECIALRATE'
                  ELSE 'GATHERING'
                END
            END
        END
  END AS [Nama Paket],
  --END as [Nama Paket],

  -- BENEFIT
  CASE 
    WHEN UPPER(c.[Ket Event]) = 'EXPO'
      THEN 
        CASE
          WHEN a.Kanwil LIKE '%D%'
            THEN
              CASE
                WHEN
                  [Dealer Type] LIKE 'NON PARTISIPAN. PARTISIPAN KHUSUS KANWIL&' THEN
                    CASE
                      WHEN c.Kanwil IN (
                        SELECT 
                          LTRIM(RTRIM(Item))
                        FROM 
                          dbo.fnSplitString(SUBSTRING([Dealer Type], CHARINDEX('KANWIL', [Dealer Type]) + LEN('KANWIL '), LEN([Dealer Type])), ',')
                        WHERE
                          TRY_CAST(LTRIM(RTRIM(Item)) AS INT) IS NOT NULL
                      )
                      THEN
                        CASE
                          WHEN (
                            [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                          ) THEN 'BUNGA + ADMIN'
                          WHEN (
                            [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                          ) THEN 'BUNGA + ADMIN'
                          WHEN (
                            [IDVM] IS NULL
                            AND a.createddate BETWEEN c.StartDate AND c.EndDate
                            AND a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate
                          ) THEN 'BUNGA + ADMIN'
                          WHEN (
                            [IDVM] IS NULL
                            AND (((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate))
                            OR ((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate))
                            OR ((a.createddate BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate)))
                          ) THEN 'BUNGA'
                          ELSE 'NO'
                        END
                      ELSE 
                        CASE 
                          WHEN [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove
                          THEN (
                            CASE 
                              WHEN d1.BENEFIT_ID IS NOT NULL 
                                THEN d13.NAMA_BENEFIT
                              ELSE 'NO'
                            END
                          )
                          WHEN [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                          THEN (
                            CASE 
                              WHEN d3.BENEFIT_ID IS NOT NULL 
                                THEN d33.NAMA_BENEFIT
                              ELSE 'NO'
                            END
                          )
                          ELSE 'NO'
                        END
                    END
                WHEN 
                  [Dealer Type] LIKE 'PARTISIPAN. PARTISIPAN BUNGA SAJA KHUSUS KANWIL%' THEN
                    CASE
                      WHEN c.Kanwil IN (
                        SELECT 
                          LTRIM(RTRIM(Item))
                        FROM 
                          dbo.fnSplitString(SUBSTRING([Dealer Type], CHARINDEX('KANWIL', [Dealer Type]) + LEN('KANWIL '), LEN([Dealer Type])), ',')
                        WHERE
                          TRY_CAST(LTRIM(RTRIM(Item)) AS INT) IS NOT NULL
                      )
                      THEN 
                        CASE       
                          WHEN (
                            [IDVM] IS NULL
                          ) THEN 'BUNGA'       
                          WHEN (
                            [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                          ) THEN 'BUNGA + ADMIN'
                          WHEN (
                            [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                          ) THEN 'BUNGA + ADMIN'
                          ELSE 'NO'
                        END
                      ELSE 
                        CASE
                          WHEN (
                            [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                          ) THEN 'BUNGA + ADMIN'
                          WHEN (
                            [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                          ) THEN 'BUNGA + ADMIN'
                          WHEN (
                            [IDVM] IS NULL
                            AND a.createddate BETWEEN c.StartDate AND c.EndDate
                            AND a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate
                          ) THEN 'BUNGA + ADMIN'
                          WHEN (
                            [IDVM] IS NULL
                            AND (((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate))
                            OR ((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate))
                            OR ((a.createddate BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate)))
                          ) THEN 'BUNGA'
                          ELSE 'NO'
                        END
                    END
                WHEN (
                  --[Dealer Type] = 'PARTISIPAN'
                  [IDVM] IS NOT NULL
                  AND (a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput) 
                  AND (a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove)
                  )
                  THEN (
                    CASE 
                      WHEN d1.BENEFIT_ID IS NOT NULL 
                        THEN d13.NAMA_BENEFIT
                      ELSE 'NO'
                    END
                  )
                  WHEN
                    [Dealer Type] = 'NON PARTISIPAN' 
                    THEN
                      CASE 
                        WHEN [IDVM] IS NOT NULL
                          AND a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput
                          AND a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove
                        THEN (
                          CASE 
                            WHEN d1.BENEFIT_ID IS NOT NULL 
                              THEN d13.NAMA_BENEFIT
                            ELSE 'NO'
                          END
                        )
                        WHEN [IDVM] IS NOT NULL
                          AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                          AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                        THEN (
                          CASE 
                            WHEN d3.BENEFIT_ID IS NOT NULL 
                              THEN d33.NAMA_BENEFIT
                            ELSE 'NO'
                          END
                        )
                        ELSE 'NO'
                      END
                  WHEN (
                    --[Dealer Type] = 'PARTISIPAN' 
                    [IDVM] IS NOT NULL
                    AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                    AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                  )
                  THEN (
                    CASE 
                      WHEN d3.BENEFIT_ID IS NOT NULL 
                        THEN d33.NAMA_BENEFIT
                      ELSE 'NO'
                    END
                  )
                WHEN (
                  [Dealer Type] = 'PARTISIPAN' 
                  AND [IDVM] IS NULL
                  AND a.createddate BETWEEN c.StartDate AND c.EndDate
                  AND a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate
                )
                THEN (
                  CASE 
                    WHEN d4.BENEFIT_ID IS NOT NULL 
                      THEN d43.NAMA_BENEFIT
                    ELSE 'NO'
                  END
                )
                WHEN (
                  [Dealer Type] = 'PARTISIPAN'
                  AND [IDVM] IS NULL
                  AND (((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate))
                  OR ((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate))
                  OR ((a.createddate BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate)))
                ) THEN (
                  CASE 
                    WHEN d5.BENEFIT_ID IS NOT NULL 
                      THEN d53.NAMA_BENEFIT
                      --THEN 'p5'
                    ELSE 'GATHERING'
                  END
                )
                WHEN (
                  [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
                  AND [IDVM] IS NULL
                  --AND (((a.createddate NOT BETWEEN d7.StartDateInput AND d7.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d7.StartDateApprove AND d7.EndDateApprove))
                  --OR ((a.createddate BETWEEN d7.StartDateInput AND d7.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d7.StartDateApprove AND d7.EndDateApprove)))
                )
                THEN (
                  CASE 
                    WHEN d7.BENEFIT_ID IS NOT NULL 
                      THEN d73.NAMA_BENEFIT
                    ELSE 'NO'
                  END
                )
                WHEN (
                  [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
                  AND [IDVM] IS NOT NULL
                  AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                  AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                )
                THEN (
                  CASE 
                    WHEN d8.BENEFIT_ID IS NOT NULL 
                      THEN d83.NAMA_BENEFIT
                    ELSE 'NO'
                  END
                )
                WHEN (
                  [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
                  AND [IDVM] IS NOT NULL
                  AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                  AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                )
                THEN (
                  CASE 
                    WHEN d9.BENEFIT_ID IS NOT NULL 
                      THEN d93.NAMA_BENEFIT
                    ELSE 'NO'
                  END
                )
              ELSE 'NO'
            END

    -- when c.[Ket Event] = EXPO and kanwil NON DS
        ELSE
          CASE
            WHEN
            [Dealer Type] LIKE 'NON PARTISIPAN. PARTISIPAN KHUSUS KANWIL%' THEN
              CASE
                WHEN a.Kanwil IN (
                  SELECT 
                    LTRIM(RTRIM(Item))
                  FROM 
                    dbo.fnSplitString(SUBSTRING([Dealer Type], CHARINDEX('KANWIL', [Dealer Type]) + LEN('KANWIL '), LEN([Dealer Type])), ',')
                  WHERE
                    TRY_CAST(LTRIM(RTRIM(Item)) AS INT) IS NOT NULL
                )
                THEN
                  CASE
                    WHEN (
                      [IDVM] IS NOT NULL
                      AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                      AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                    ) THEN 'BUNGA + ADMIN'
                    WHEN (
                      [IDVM] IS NOT NULL
                      AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                      AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                    ) THEN 'BUNGA + ADMIN'
                    WHEN (
                      [IDVM] IS NULL
                      AND a.createddate BETWEEN d4.StartDateInput AND d4.EndDateInput
                      AND a.TglApproveSPKDealer BETWEEN d4.StartDateApprove AND d4.EndDateApprove
                    ) THEN 'BUNGA + ADMIN'
                    WHEN (
                      [IDVM] IS NULL
                      AND (((a.createddate NOT BETWEEN d5.StartDateInput AND d5.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d5.StartDateApprove AND d5.EndDateApprove))
                      OR ((a.createddate NOT BETWEEN d5.StartDateInput AND d5.EndDateInput) AND (a.TglApproveSPKDealer BETWEEN d5.StartDateApprove AND d5.EndDateApprove))
                      OR ((a.createddate BETWEEN d5.StartDateInput AND d5.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d5.StartDateApprove AND d5.EndDateApprove)))
                    ) THEN 'BUNGA'
                    ELSE 'NO'
                  END
                ELSE 
                  CASE 
                    WHEN [IDVM] IS NOT NULL
                      AND a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput
                      AND a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove
                    THEN (
                      CASE 
                        WHEN d1.BENEFIT_ID IS NOT NULL 
                          THEN d13.NAMA_BENEFIT
                        ELSE 'NO'
                      END
                    )
                    WHEN [IDVM] IS NOT NULL
                      AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                      AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                    THEN (
                      CASE 
                        WHEN d3.BENEFIT_ID IS NOT NULL 
                          THEN d33.NAMA_BENEFIT
                        ELSE 'NO'
                      END
                    )
                    ELSE 'NO'
                  END
              END
          WHEN 
            [Dealer Type] LIKE 'PARTISIPAN. PARTISIPAN BUNGA SAJA KHUSUS KANWIL%' THEN
              CASE
                WHEN a.Kanwil IN (
                  SELECT 
                    LTRIM(RTRIM(Item))
                  FROM 
                    dbo.fnSplitString(SUBSTRING([Dealer Type], CHARINDEX('KANWIL', [Dealer Type]) + LEN('KANWIL '), LEN([Dealer Type])), ',')
                  WHERE
                    TRY_CAST(LTRIM(RTRIM(Item)) AS INT) IS NOT NULL
                )
                THEN 
                  CASE       
                    WHEN (
                      [IDVM] IS NULL
                    ) THEN 'BUNGA'       
                    WHEN (
                      [IDVM] IS NOT NULL
                      AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                      AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                    ) THEN 'BUNGA + ADMIN'
                    WHEN (
                      [IDVM] IS NOT NULL
                      AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                      AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                    ) THEN 'BUNGA + ADMIN'
                    ELSE 'NO'
                  END
                ELSE 
                  CASE
                    WHEN (
                      [IDVM] IS NOT NULL
                      AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                      AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                    ) THEN 'BUNGA + ADMIN'
                    WHEN (
                      [IDVM] IS NOT NULL
                      AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                      AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                    ) THEN 'BUNGA + ADMIN'
                    WHEN (
                      [IDVM] IS NULL
                      AND a.createddate BETWEEN d4.StartDateInput AND d4.EndDateInput
                      AND a.TglApproveSPKDealer BETWEEN d4.StartDateApprove AND d4.EndDateApprove
                    ) THEN 'BUNGA + ADMIN'
                    WHEN (
                      [IDVM] IS NULL
                      AND (((a.createddate NOT BETWEEN d5.StartDateInput AND d5.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d5.StartDateApprove AND d5.EndDateApprove))
                      OR ((a.createddate NOT BETWEEN d5.StartDateInput AND d5.EndDateInput) AND (a.TglApproveSPKDealer BETWEEN d5.StartDateApprove AND d5.EndDateApprove))
                      OR ((a.createddate BETWEEN d5.StartDateInput AND d5.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d5.StartDateApprove AND d5.EndDateApprove)))
                    ) THEN 'BUNGA'
                    ELSE 'NO'
                  END
              END
            WHEN (
            --[Dealer Type] = 'PARTISIPAN'
            [IDVM] IS NOT NULL
            AND (a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput) 
            AND (a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove)
            )
            THEN (
              CASE 
                WHEN d1.BENEFIT_ID IS NOT NULL 
                  THEN d13.NAMA_BENEFIT
                ELSE 'NO'
              END
            )
            WHEN
              [Dealer Type] = 'NON PARTISIPAN' 
              THEN
                CASE 
                  WHEN [IDVM] IS NOT NULL
                    AND a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput
                    AND a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove
                  THEN (
                    CASE 
                      WHEN d1.BENEFIT_ID IS NOT NULL 
                        THEN d13.NAMA_BENEFIT
                      ELSE 'NO'
                    END
                  )
                  WHEN [IDVM] IS NOT NULL
                    AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                    AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                  THEN (
                    CASE 
                      WHEN d3.BENEFIT_ID IS NOT NULL 
                        THEN d33.NAMA_BENEFIT
                      ELSE 'NO'
                    END
                  )
                  ELSE 'NO'
                END
            WHEN (
              --[Dealer Type] = 'PARTISIPAN' 
              [IDVM] IS NOT NULL
              AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
              AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
            )
              THEN (
                CASE 
                  WHEN d3.BENEFIT_ID IS NOT NULL 
                    THEN d33.NAMA_BENEFIT
                  ELSE 'NO'
                END
              )
            WHEN (
              [Dealer Type] = 'PARTISIPAN' 
              AND [IDVM] IS NULL
              AND a.createddate BETWEEN d4.StartDateInput AND d4.EndDateInput
              AND a.TglApproveSPKDealer BETWEEN d4.StartDateApprove AND d4.EndDateApprove
            )
              THEN (
                CASE 
                  WHEN d4.BENEFIT_ID IS NOT NULL 
                    THEN d43.NAMA_BENEFIT
                  ELSE 'NO'
                END
              )
            WHEN (
              [Dealer Type] = 'PARTISIPAN'
              AND [IDVM] IS NULL
              AND (((a.createddate NOT BETWEEN d5.StartDateInput AND d5.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d5.StartDateApprove AND d5.EndDateApprove))
              OR ((a.createddate NOT BETWEEN d5.StartDateInput AND d5.EndDateInput) AND (a.TglApproveSPKDealer BETWEEN d5.StartDateApprove AND d5.EndDateApprove))
              OR ((a.createddate BETWEEN d5.StartDateInput AND d5.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d5.StartDateApprove AND d5.EndDateApprove)))
            ) THEN (
              CASE 
                WHEN d5.BENEFIT_ID IS NOT NULL 
                  THEN d53.NAMA_BENEFIT
                  --THEN 'p5'
                ELSE 'GATHERING'
              END
            )
            
            WHEN (
              [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
              AND [IDVM] IS NULL
              --AND (((a.createddate NOT BETWEEN d7.StartDateInput AND d7.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d7.StartDateApprove AND d7.EndDateApprove))
              --OR ((a.createddate BETWEEN d7.StartDateInput AND d7.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d7.StartDateApprove AND d7.EndDateApprove)))
            )
            THEN (
              CASE 
                WHEN d7.BENEFIT_ID IS NOT NULL 
                  THEN d73.NAMA_BENEFIT
                ELSE 'NO'
              END
            )
            WHEN (
              [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
              AND [IDVM] IS NOT NULL
              AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
              AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
            )
            THEN (
              CASE 
                WHEN d8.BENEFIT_ID IS NOT NULL 
                  THEN d83.NAMA_BENEFIT
                ELSE 'NO'
              END
            )
            WHEN (
              [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
              AND [IDVM] IS NOT NULL
              AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
              AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
            )
            THEN (
              CASE 
                WHEN d9.BENEFIT_ID IS NOT NULL 
                  THEN d93.NAMA_BENEFIT
                ELSE 'NO'
              END
            )
          ELSE 'NO'
        END
      END

    -- when c.[Ket Event] = AUTOSHOW AND KAnwil DS
    WHEN UPPER(c.[Ket Event]) = 'AUTOSHOW'
      THEN
        CASE
          WHEN a.Kanwil LIKE '%D%'
            THEN
              CASE
                WHEN
                  [Dealer Type] LIKE 'NON PARTISIPAN. PARTISIPAN KHUSUS KANWIL%' THEN
                    CASE
                      WHEN c.Kanwil IN (
                        SELECT 
                          LTRIM(RTRIM(Item))
                        FROM 
                          dbo.fnSplitString(SUBSTRING([Dealer Type], CHARINDEX('KANWIL', [Dealer Type]) + LEN('KANWIL '), LEN([Dealer Type])), ',')
                        WHERE
                          TRY_CAST(LTRIM(RTRIM(Item)) AS INT) IS NOT NULL
                      )
                      THEN
                        CASE
                          WHEN (
                            [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                          ) THEN 'BUNGA + ADMIN'
                          WHEN (
                            [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                          ) THEN 'BUNGA + ADMIN'
                          WHEN (
                            [IDVM] IS NULL
                            AND a.createddate BETWEEN c.StartDate AND c.EndDate
                            AND a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate
                          ) THEN 'BUNGA + ADMIN'
                          WHEN (
                            [IDVM] IS NULL
                            AND (((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate))
                            OR ((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate))
                            OR ((a.createddate BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate)))
                          ) THEN 'BUNGA'
                          ELSE 'NO'
                        END
                      ELSE
                        CASE 
                          WHEN [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove
                          THEN (
                            CASE 
                              WHEN d1.BENEFIT_ID IS NOT NULL 
                                THEN d13.NAMA_BENEFIT
                              ELSE 'NO'
                            END
                          )
                          WHEN [IDVM] IS NOT NULL
                            AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                            AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                          THEN (
                            CASE 
                              WHEN d3.BENEFIT_ID IS NOT NULL 
                                THEN d33.NAMA_BENEFIT
                              ELSE 'NO'
                            END
                          )
                          ELSE 'NO'
                        END
                    END
                  WHEN 
                    [Dealer Type] LIKE 'PARTISIPAN. PARTISIPAN BUNGA SAJA KHUSUS KANWIL%' THEN
                      CASE
                        WHEN c.Kanwil IN (
                          SELECT 
                            LTRIM(RTRIM(Item))
                          FROM 
                            dbo.fnSplitString(SUBSTRING([Dealer Type], CHARINDEX('KANWIL', [Dealer Type]) + LEN('KANWIL '), LEN([Dealer Type])), ',')
                          WHERE
                            TRY_CAST(LTRIM(RTRIM(Item)) AS INT) IS NOT NULL
                        )
                        THEN 
                          CASE       
                            WHEN (
                              [IDVM] IS NULL
                            ) THEN 'BUNGA'       
                            WHEN (
                              [IDVM] IS NOT NULL
                              AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                              AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                            ) THEN 'BUNGA + ADMIN'
                            WHEN (
                              [IDVM] IS NOT NULL
                              AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                              AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                            ) THEN 'BUNGA + ADMIN'
                            ELSE 'NO'
                          END
                        ELSE 
                          CASE
                            WHEN (
                              [IDVM] IS NOT NULL
                              AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                              AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                            ) THEN 'BUNGA + ADMIN'
                            WHEN (
                              [IDVM] IS NOT NULL
                              AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                              AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                            ) THEN 'BUNGA + ADMIN'
                            WHEN (
                              [IDVM] IS NULL
                              AND a.createddate BETWEEN c.StartDate AND c.EndDate
                              AND a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate
                            ) THEN 'BUNGA + ADMIN'
                            WHEN (
                              [IDVM] IS NULL
                              AND (((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate))
                              OR ((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate))
                              OR ((a.createddate BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate)))
                            ) THEN 'BUNGA'
                            ELSE 'NO'
                          END
                      END
                  WHEN (
                  --[Dealer Type] = 'PARTISIPAN'
                  [IDVM] IS NOT NULL
                  AND (a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput) 
                  AND (a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove)
                  )
                  THEN (
                    CASE 
                      WHEN d1.BENEFIT_ID IS NOT NULL 
                        THEN d13.NAMA_BENEFIT
                        --THEN 'p1'
                      ELSE 'NO'
                    END
                  )
                  WHEN
                  [Dealer Type] = 'NON PARTISIPAN' 
                  THEN
                    CASE 
                      WHEN [IDVM] IS NOT NULL
                        AND a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput
                        AND a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove
                      THEN (
                        CASE 
                          WHEN d1.BENEFIT_ID IS NOT NULL 
                            THEN d13.NAMA_BENEFIT
                          ELSE 'NO'
                        END
                      )
                      WHEN [IDVM] IS NOT NULL
                        AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                        AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                      THEN (
                        CASE 
                          WHEN d3.BENEFIT_ID IS NOT NULL 
                            THEN d33.NAMA_BENEFIT
                          ELSE 'NO'
                        END
                      )
                      ELSE 'NO'
                    END
                  WHEN (
                    --[Dealer Type] = 'PARTISIPAN' 
                    [IDVM] IS NOT NULL
                    AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                    AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                  )
                  THEN (
                    CASE 
                      WHEN d3.BENEFIT_ID IS NOT NULL 
                        THEN d33.NAMA_BENEFIT
                        --THEN 'p3'
                      ELSE 'NO'
                    END
                  )
                  WHEN (
                    [Dealer Type] = 'PARTISIPAN'
                    AND [IDVM] IS NULL
                    AND a.createddate BETWEEN c.StartDate AND c.EndDate
                    AND a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate
                  )
                  THEN (
                    CASE 
                      WHEN d10.BENEFIT_ID IS NOT NULL 
                        THEN d103.NAMA_BENEFIT  
                      ELSE 'NO'
                    END
                  )
                  WHEN (
                    [Dealer Type] = 'PARTISIPAN'
                    AND [IDVM] IS NULL
                    AND (((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate))
                    OR ((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate))
                    OR ((a.createddate BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate)))
                  )
                  THEN (
                    CASE 
                      WHEN d5.BENEFIT_ID IS NOT NULL 
                        THEN d53.NAMA_BENEFIT
                        --THEN 'p5'
                      ELSE 'NO'
                    END
                  )
                  WHEN (
                    [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
                    AND [IDVM] IS NULL
                    --AND (((a.createddate NOT BETWEEN d7.StartDateInput AND d7.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d7.StartDateApprove AND d7.EndDateApprove))
                    --OR ((a.createddate BETWEEN d7.StartDateInput AND d7.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d7.StartDateApprove AND d7.EndDateApprove)))
                  )
                  THEN (
                    CASE 
                      WHEN d7.BENEFIT_ID IS NOT NULL 
                        THEN d73.NAMA_BENEFIT
                      --THEN 'p7'
                      ELSE 'NO'
                    END
                  )
                  WHEN (
                    [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
                    AND [IDVM] IS NOT NULL
                    AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                    AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                  )
                  THEN (
                    CASE 
                      WHEN d8.BENEFIT_ID IS NOT NULL 
                        THEN d83.NAMA_BENEFIT
                        --THEN 'p8'
                      ELSE 'NO'
                    END
                  )
                  WHEN (
                    [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
                    AND [IDVM] IS NOT NULL
                    AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                    AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                  )
                  THEN (
                    CASE 
                      WHEN d9.BENEFIT_ID IS NOT NULL 
                        THEN d93.NAMA_BENEFIT
                        --THEN 'p9'
                      ELSE 'NO'
                    END
                  )
                ELSE 'NO'
              END
          ELSE
            CASE
              WHEN
                [Dealer Type] LIKE 'NON PARTISIPAN. PARTISIPAN KHUSUS KANWIL%' THEN
                  CASE
                    WHEN a.Kanwil IN (
                      SELECT 
                        LTRIM(RTRIM(Item))
                      FROM 
                        dbo.fnSplitString(SUBSTRING([Dealer Type], CHARINDEX('KANWIL', [Dealer Type]) + LEN('KANWIL '), LEN([Dealer Type])), ',')
                      WHERE
                        TRY_CAST(LTRIM(RTRIM(Item)) AS INT) IS NOT NULL
                    )
                    THEN
                      CASE
                        WHEN (
                          [IDVM] IS NOT NULL
                          AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                          AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                        ) THEN 'BUNGA + ADMIN'
                        WHEN (
                          [IDVM] IS NOT NULL
                          AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                          AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                        ) THEN 'BUNGA + ADMIN'
                        WHEN (
                          [IDVM] IS NULL
                          AND a.createddate BETWEEN c.StartDate AND c.EndDate
                          AND a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate
                        ) THEN 'BUNGA + ADMIN'
                        WHEN (
                          [IDVM] IS NULL
                          AND (((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate))
                          OR ((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate))
                          OR ((a.createddate BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate)))
                        ) THEN 'BUNGA'
                        ELSE 'NO'
                      END
                    ELSE 
                      CASE 
                        WHEN [IDVM] IS NOT NULL
                          AND a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput
                          AND a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove
                        THEN (
                          CASE 
                            WHEN d1.BENEFIT_ID IS NOT NULL 
                              THEN d13.NAMA_BENEFIT
                            ELSE 'NO'
                          END
                        )
                        WHEN [IDVM] IS NOT NULL
                          AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                          AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                        THEN (
                          CASE 
                            WHEN d3.BENEFIT_ID IS NOT NULL 
                              THEN d33.NAMA_BENEFIT
                            ELSE 'NO'
                          END
                        )
                        ELSE 'NO'
                      END
                  END
            WHEN 
              [Dealer Type] LIKE 'PARTISIPAN. PARTISIPAN BUNGA SAJA KHUSUS KANWIL%' THEN
                CASE
                  WHEN a.Kanwil IN (
                    SELECT 
                      LTRIM(RTRIM(Item))
                    FROM 
                      dbo.fnSplitString(SUBSTRING([Dealer Type], CHARINDEX('KANWIL', [Dealer Type]) + LEN('KANWIL '), LEN([Dealer Type])), ',')
                    WHERE
                      TRY_CAST(LTRIM(RTRIM(Item)) AS INT) IS NOT NULL
                  )
                  THEN 
                    CASE       
                      WHEN (
                        [IDVM] IS NULL
                      ) THEN 'BUNGA'       
                      WHEN (
                        [IDVM] IS NOT NULL
                        AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                        AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                      ) THEN 'BUNGA + ADMIN'
                      WHEN (
                        [IDVM] IS NOT NULL
                        AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                        AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                      ) THEN 'BUNGA + ADMIN'
                      ELSE 'NO'
                    END
                  ELSE 
                    CASE
                      WHEN (
                        [IDVM] IS NOT NULL
                        AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                        AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                      ) THEN 'BUNGA + ADMIN'
                      WHEN (
                        [IDVM] IS NOT NULL
                        AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                        AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                      ) THEN 'BUNGA + ADMIN'
                      WHEN (
                        [IDVM] IS NULL
                        AND a.createddate BETWEEN c.StartDate AND c.EndDate
                        AND a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate
                      ) THEN 'BUNGA + ADMIN'
                      WHEN (
                        [IDVM] IS NULL
                        AND (((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate))
                        OR ((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate))
                        OR ((a.createddate BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate)))
                      ) THEN 'BUNGA'
                      ELSE 'NO'
                    END
                END
            WHEN (
            --[Dealer Type] = 'PARTISIPAN'
            [IDVM] IS NOT NULL
            AND (a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput) 
            AND (a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove)
            )
            THEN (
              CASE 
                WHEN d1.BENEFIT_ID IS NOT NULL 
                  THEN d13.NAMA_BENEFIT
                  --THEN 'p1'
                ELSE 'NO'
              END
            )
            WHEN
              [Dealer Type] = 'NON PARTISIPAN' 
              THEN
                CASE 
                  WHEN [IDVM] IS NOT NULL
                    AND a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput
                    AND a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove
                  THEN (
                    CASE 
                      WHEN d1.BENEFIT_ID IS NOT NULL 
                        THEN d13.NAMA_BENEFIT
                      ELSE 'NO'
                    END
                  )
                  WHEN [IDVM] IS NOT NULL
                    AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                    AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                  THEN (
                    CASE 
                      WHEN d3.BENEFIT_ID IS NOT NULL 
                        THEN d33.NAMA_BENEFIT
                      ELSE 'NO'
                    END
                  )
                  ELSE 'NO'
                END
            WHEN (
              --[Dealer Type] = 'PARTISIPAN' 
              [IDVM] IS NOT NULL
              AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
              AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
            )
              THEN (
                CASE 
                  WHEN d3.BENEFIT_ID IS NOT NULL 
                    THEN d33.NAMA_BENEFIT
                    --THEN 'p3'
                  ELSE 'NO'
                END
              )
            WHEN (
            [Dealer Type] = 'PARTISIPAN'
                AND [IDVM] IS NULL
                AND a.createddate BETWEEN c.StartDate AND c.EndDate
                AND a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate
              )
              THEN (
                CASE 
                  WHEN d10.BENEFIT_ID IS NOT NULL 
                    THEN d103.NAMA_BENEFIT  
                  ELSE 'NO'
                END
              )
            WHEN (
              [Dealer Type] = 'PARTISIPAN'
              AND [IDVM] IS NULL
              AND (((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate))
              OR ((a.createddate NOT BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer BETWEEN c.StartDate AND c.EndDate))
              OR ((a.createddate BETWEEN c.StartDate AND c.EndDate) AND (a.TglApproveSPKDealer NOT BETWEEN c.StartDate AND c.EndDate)))
            )
              THEN (
                CASE 
                  WHEN d5.BENEFIT_ID IS NOT NULL 
                    THEN d53.NAMA_BENEFIT
                    --THEN 'p5'
                  ELSE 'NO'
                END
              )
            
            WHEN (
              [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
              AND [IDVM] IS NULL
              --AND (((a.createddate NOT BETWEEN d7.StartDateInput AND d7.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d7.StartDateApprove AND d7.EndDateApprove))
              --OR ((a.createddate BETWEEN d7.StartDateInput AND d7.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d7.StartDateApprove AND d7.EndDateApprove)))
            )
            THEN (
              CASE 
                WHEN d7.BENEFIT_ID IS NOT NULL 
                  THEN d73.NAMA_BENEFIT
                --THEN 'p7'
                ELSE 'NO'
              END
            )
            WHEN (
              [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
              AND [IDVM] IS NOT NULL
              AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
              AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
            )
            THEN (
              CASE 
                WHEN d8.BENEFIT_ID IS NOT NULL 
                  THEN d83.NAMA_BENEFIT
                  --THEN 'p8'
                ELSE 'NO'
              END
            )
            WHEN (
              [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
              AND [IDVM] IS NOT NULL
              AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
              AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
            )
            THEN (
              CASE 
                WHEN d9.BENEFIT_ID IS NOT NULL 
                  THEN d93.NAMA_BENEFIT
                  --THEN 'p9'
                ELSE 'NO'
              END
            )
          ELSE 'NO'
        END
    END


    -- when c.[Ket Event] = not 'EXPO' or AUTOSHOW, or kanwil not participate/not include in master dealer list
    ELSE 
      CASE
        WHEN (
        [Dealer Type] = 'PARTISIPAN'
        AND [IDVM] IS NULL
        ) THEN (
          CASE 
            WHEN d5.BENEFIT_ID IS NOT NULL 
              THEN d53.NAMA_BENEFIT
              --THEN 'p5'
            ELSE 'NO'
          END
        )
        WHEN (
          --[Dealer Type] = 'PARTISIPAN'
          [IDVM] IS NOT NULL
          AND (a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput) 
          AND (a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove)
          ) THEN (
              CASE 
                WHEN d1.BENEFIT_ID IS NOT NULL 
                  THEN d13.NAMA_BENEFIT
                ELSE 'NO'
              END
          )
        WHEN (
          --[Dealer Type] = 'PARTISIPAN' 
          [IDVM] IS NOT NULL
          AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
          AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
        ) THEN (
            CASE 
              WHEN d3.BENEFIT_ID IS NOT NULL 
                THEN d33.NAMA_BENEFIT
              ELSE 'NO'
            END
        )
        WHEN
          [Dealer Type] = 'NON PARTISIPAN' 
          THEN
            CASE 
              WHEN [IDVM] IS NOT NULL
                AND a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput
                AND a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove
              THEN (
                CASE 
                  WHEN d1.BENEFIT_ID IS NOT NULL 
                    THEN d13.NAMA_BENEFIT
                  ELSE 'NO'
                END
              )
              WHEN [IDVM] IS NOT NULL
                AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
              THEN (
                CASE 
                  WHEN d3.BENEFIT_ID IS NOT NULL 
                    THEN d33.NAMA_BENEFIT
                  ELSE 'NO'
                END
              )
              ELSE 'NO'
            END
        WHEN (
          [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
          AND [IDVM] IS NULL
          --AND (((a.createddate NOT BETWEEN d7.StartDateInput AND d7.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d7.StartDateApprove AND d7.EndDateApprove))
          --OR ((a.createddate BETWEEN d7.StartDateInput AND d7.EndDateInput) AND (a.TglApproveSPKDealer NOT BETWEEN d7.StartDateApprove AND d7.EndDateApprove)))
        )
        THEN (
          CASE 
            WHEN d7.BENEFIT_ID IS NOT NULL 
              THEN d73.NAMA_BENEFIT
            --THEN 'p7'
            ELSE 'NO'
          END
        )
        WHEN (
          [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
          AND [IDVM] IS NOT NULL
          AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
          AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
        )
        THEN (
          CASE 
            WHEN d8.BENEFIT_ID IS NOT NULL 
              THEN d83.NAMA_BENEFIT
              --THEN 'p8'
            ELSE 'NO'
          END
        )
        WHEN (
          [Dealer Type] = 'PARTISIPAN BUNGA SAJA'
          AND [IDVM] IS NOT NULL
          AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
          AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
        )
        THEN (
          CASE 
            WHEN d9.BENEFIT_ID IS NOT NULL 
              THEN d93.NAMA_BENEFIT
              --THEN 'p9'
            ELSE 'NO'
          END
        )
        WHEN
          [Dealer Type] LIKE 'NON PARTISIPAN. PARTISIPAN KHUSUS KANWIL%' THEN
            CASE
              WHEN a.Kanwil IN (
                SELECT 
                  LTRIM(RTRIM(Item))
                FROM 
                  dbo.fnSplitString(SUBSTRING([Dealer Type], CHARINDEX('KANWIL', [Dealer Type]) + LEN('KANWIL '), LEN([Dealer Type])), ',')
                WHERE
                  TRY_CAST(LTRIM(RTRIM(Item)) AS INT) IS NOT NULL
              )
              THEN
                CASE
                  WHEN (
                    [IDVM] IS NOT NULL
                    AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                    AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                  ) THEN 'BUNGA + ADMIN'
                  WHEN (
                    [IDVM] IS NOT NULL
                    AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                    AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                  ) THEN 'BUNGA + ADMIN'
                  WHEN (
                    [IDVM] IS NULL
                  ) THEN 'BUNGA'
                  ELSE 'NO'
                END
              ELSE 
                CASE 
                  WHEN [IDVM] IS NOT NULL
                    AND a.createddate BETWEEN d1.StartDateInput AND d1.EndDateInput
                    AND a.TglApproveSPKDealer BETWEEN d1.StartDateApprove AND d1.EndDateApprove
                  THEN (
                    CASE 
                      WHEN d1.BENEFIT_ID IS NOT NULL 
                        THEN d13.NAMA_BENEFIT
                      ELSE 'NO'
                    END
                  )
                  WHEN [IDVM] IS NOT NULL
                    AND a.createddate BETWEEN d3.StartDateInput AND d3.EndDateInput
                    AND a.TglApproveSPKDealer BETWEEN d3.StartDateApprove AND d3.EndDateApprove
                  THEN (
                    CASE 
                      WHEN d3.BENEFIT_ID IS NOT NULL 
                        THEN d33.NAMA_BENEFIT
                      ELSE 'NO'
                    END
                  )
                  ELSE 'NO'
                END
            END
        WHEN 
        [Dealer Type] LIKE 'PARTISIPAN. PARTISIPAN BUNGA SAJA KHUSUS KANWIL%' THEN
          CASE
            WHEN a.Kanwil IN (
              SELECT 
                LTRIM(RTRIM(Item))
              FROM 
                dbo.fnSplitString(SUBSTRING([Dealer Type], CHARINDEX('KANWIL', [Dealer Type]) + LEN('KANWIL '), LEN([Dealer Type])), ',')
              WHERE
                TRY_CAST(LTRIM(RTRIM(Item)) AS INT) IS NOT NULL
            )
            THEN 
              CASE       
                WHEN (
                  [IDVM] IS NULL
                ) THEN 'BUNGA'       
                WHEN (
                  [IDVM] IS NOT NULL
                  AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                  AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                ) THEN 'BUNGA + ADMIN'
                WHEN (
                  [IDVM] IS NOT NULL
                  AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                  AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                ) THEN 'BUNGA + ADMIN'
                ELSE 'NO'
              END
            ELSE 
              CASE
                WHEN (
                  [IDVM] IS NOT NULL
                  AND a.createddate BETWEEN d8.StartDateInput AND d8.EndDateInput
                  AND a.TglApproveSPKDealer BETWEEN d8.StartDateApprove AND d8.EndDateApprove
                ) THEN 'BUNGA + ADMIN'
                WHEN (
                  [IDVM] IS NOT NULL
                  AND a.createddate BETWEEN d9.StartDateInput AND d9.EndDateInput
                  AND a.TglApproveSPKDealer BETWEEN d9.StartDateApprove AND d9.EndDateApprove
                ) THEN 'BUNGA + ADMIN'
                WHEN (
                  [IDVM] IS NULL
                ) THEN 'BUNGA'
                ELSE 'NO'
              END
          END
      END
  END as [Nama Benefit]
FROM (
  SELECT 
    a.dealerName as [Dealer Name],
    --b.[GROUP DEALER ID] as [Dealer Id],
    a.dealer_id as [Dealer Id],
    a.nomor_spk,
    a.tanggal_spk,
    a.nama_pembeli,
    --LEFT(a.kcukcp, CHARINDEX('-', a.kcukcp) - 1) as Kanwil,
	  CASE 
        WHEN LEFT(a.kcukcp, CHARINDEX('-', a.kcukcp) - 1) LIKE '%[^0-9]%' 
        THEN SUBSTRING(LEFT(a.kcukcp, CHARINDEX('-', a.kcukcp) - 1), PATINDEX('%[0-9]%', LEFT(a.kcukcp, CHARINDEX('-', a.kcukcp) - 1)), LEN(LEFT(a.kcukcp, CHARINDEX('-', a.kcukcp) - 1)))
        ELSE CAST(LEFT(a.kcukcp, CHARINDEX('-', a.kcukcp) - 1) AS VARCHAR(10))
    END as KanwilFormatted,
	  CAST(LEFT(a.kcukcp, CHARINDEX('-', a.kcukcp) - 1) AS VARCHAR(10)) as Kanwil,
    --LEFT(RIGHT(a.kcukcp, LEN(a.kcukcp) - CHARINDEX('-', a.kcukcp)), CHARINDEX('-', RIGHT(a.kcukcp, LEN(a.kcukcp) - CHARINDEX('-', a.kcukcp))) - 1) as [KCU],
    CAST(LEFT(RIGHT(a.kcukcp, LEN(a.kcukcp) - CHARINDEX('-', a.kcukcp)), CHARINDEX('-', RIGHT(a.kcukcp, LEN(a.kcukcp) - CHARINDEX('-', a.kcukcp))) - 1) AS VARCHAR(255)) AS [KCU],
    right(right(a.kcukcp,len(a.kcukcp)-charindex('-',a.kcukcp)),len(right(a.kcukcp,len(a.kcukcp)-charindex('-',a.kcukcp)))-charindex('-',right(a.kcukcp,len(a.kcukcp)-charindex('-',a.kcukcp)))) as KCP,
    cast(format(isnull(a.jum_unit,0),'N0')  as varchar(50)) as [Jumlah Unit],
    a.createddate,
    a.reqIdVM as [IDVM],
    a.reqIdVM,
    a.TglApproveSPKDealer,
    a.kcukcp,
    REPLACE(CAST(FORMAT((ISNULL(a.otr, 0)) / 1000000000.00, 'N2') as varchar(50)), '.', ',') as [OTR Pakai(M)],
    CAST(FORMAT((ISNULL(a.otr, 0) * ISNULL(a.jum_unit, 0)) / 1000000000.00, 'N2') as varchar(50)) + ' M' as [Unit*OTR],
    b.[STATUS BENEFIT] as [Dealer Type],
    isnull(a.merk,'') as Merk,
    isnull(a.model,'') as Model,
    isnull(a.tipe,'') as Tipe,
    d.appid as [APPID],
    d.mid as [MID],
    --a.APPID,
    --a.MID,
    (case   
			when (a.reqidvm is not null and a.stsData=4) then 'Not Yet Checked'  
			when isnull(a.preapprove,0)=0 then 'Non Instant Approval'  
			else 'Instant Approval'  
		end) as [PreApprove],
    a.dealerName
    --c.[Ket Event] as [Keterangan Event]
  FROM register_spk a WITH (NOLOCK) 
  LEFT JOIN MasterDealerReportOpUAT b WITH (NOLOCK) 
    ON a.dealer_id= b.[GROUP DEALER ID]
  LEFT JOIN tbl_push_me d WITH(NOLOCK)
    ON a.nomor_spk = d.idSPK
  --LEFT JOIN MatriksDateExpoUAT c WITH (NOLOCK) 
    --ON [KCU] = c.KCU
  WHERE a.createddate BETWEEN @tglStart and @tglEnd
  and dbo.fHTA_GetLastStateSPK(a.nomor_spk)='Approved'
) a
LEFT JOIN STG_MASTER_KCU_REPORT c WITH (NOLOCK) 
    ON a.[KCU] = c.KCU
--WHERE a.createddate BETWEEN @tglStart and @tglEnd
OUTER APPLY (
  SELECT TOP 1 
    c.PAKET_ID,
    c.BENEFIT_ID,
    c.StartDateInput,
    c.EndDateInput,
    c.StartDateApprove,
    c.EndDateApprove,
    c.MatriksName
  FROM STG_MATRIKS_REPORT_OP c WITH (NOLOCK)
  WHERE
    c.Dealer = 'PARTISIPAN'
    --AND a.TglApproveSPKDealer BETWEEN c.StartDateApprove AND c.EndDateApprove
    --AND a.createddate BETWEEN c.StartDateInput AND c.EndDateInput
    AND c.Keterangan = 'VM'
    AND a.KanwilFormatted = CAST(c.Kanwil AS VARCHAR(255))
    AND c.MatriksName = 'spkonlinejk'
) d1
LEFT JOIN STG_NAMA_PAKET_REPORT d12 WITH (NOLOCK)
  ON d1.Paket_ID = d12.Paket_ID
LEFT JOIN STG_NAMA_BENEFIT_REPORT d13 WITH (NOLOCK)
  ON d1.Benefit_ID = d13.Benefit_ID
OUTER APPLY (
  SELECT TOP 1 
    c.PAKET_ID,
    c.BENEFIT_ID,
    c.StartDateInput,
    c.EndDateInput,
    c.StartDateApprove,
    c.EndDateApprove,
    c.MatriksName
  FROM STG_MATRIKS_REPORT_OP c WITH (NOLOCK)
  WHERE
    c.Dealer = 'NON PARTISIPAN'
    --AND a.TglApproveSPKDealer BETWEEN c.StartDateApprove AND c.EndDateApprove
    --AND a.createddate BETWEEN c.StartDateInput AND c.EndDateInput
    AND c.Keterangan = 'VM'
    AND a.KanwilFormatted = CAST(c.Kanwil AS VARCHAR(255))
    AND c.MatriksName = 'gathering'
) d2
LEFT JOIN STG_NAMA_PAKET_REPORT d22 WITH (NOLOCK)
  ON d2.Paket_ID = d22.Paket_ID
LEFT JOIN STG_NAMA_BENEFIT_REPORT d23 WITH (NOLOCK)
  ON d2.Benefit_ID = d23.Benefit_ID
OUTER APPLY (
  SELECT TOP 1 
    c.PAKET_ID,
    c.BENEFIT_ID,
    c.StartDateInput,
    c.EndDateInput,
    c.StartDateApprove,
    c.EndDateApprove,
    c.MatriksName
  FROM STG_MATRIKS_REPORT_OP c WITH (NOLOCK)
  WHERE
    c.Dealer = 'PARTISIPAN'
    --AND a.TglApproveSPKDealer BETWEEN c.StartDateApprove AND c.EndDateApprove
    --AND a.createddate BETWEEN c.StartDateInput AND c.EndDateInput
    AND c.Keterangan = 'VM'
    AND a.KanwilFormatted = CAST(c.Kanwil AS VARCHAR(255))
    AND c.MatriksName = 'spkonline'
) d3
LEFT JOIN STG_NAMA_PAKET_REPORT d32 WITH (NOLOCK)
  ON d3.Paket_ID = d32.Paket_ID
LEFT JOIN STG_NAMA_BENEFIT_REPORT d33 WITH (NOLOCK)
  ON d3.Benefit_ID = d33.Benefit_ID
OUTER APPLY (
  SELECT TOP 1 
    c.PAKET_ID,
    c.BENEFIT_ID,
    c.StartDateInput,
    c.EndDateInput,
    c.StartDateApprove,
    c.EndDateApprove,
    c.MatriksName
  FROM STG_MATRIKS_REPORT_OP c WITH (NOLOCK)
  WHERE
    c.Dealer = 'PARTISIPAN'
    --AND a.TglApproveSPKDealer BETWEEN c.StartDateApprove AND c.EndDateApprove
    --AND a.createddate BETWEEN c.StartDateInput AND c.EndDateInput
    AND c.Keterangan = 'eMas'
    AND a.KanwilFormatted = CAST(c.Kanwil AS VARCHAR(255))
    AND c.MatriksName = 'expoversary'
) d4
LEFT JOIN STG_NAMA_PAKET_REPORT d42 WITH (NOLOCK)
  ON d4.Paket_ID = d42.Paket_ID
LEFT JOIN STG_NAMA_BENEFIT_REPORT d43 WITH (NOLOCK)
  ON d4.Benefit_ID = d43.Benefit_ID
OUTER APPLY (
  SELECT TOP 1 
    c.PAKET_ID,
    c.BENEFIT_ID,
    c.StartDateInput,
    c.EndDateInput,
    c.StartDateApprove,
    c.EndDateApprove,
    c.MatriksName
  FROM STG_MATRIKS_REPORT_OP c WITH (NOLOCK)
  WHERE
    c.Dealer = 'PARTISIPAN'
    --AND (a.createddate BETWEEN c.StartDateInput AND c.EndDateInput AND a.TglApproveSPKDealer NOT BETWEEN c.StartDateApprove AND c.EndDateApprove)
    --OR ()
    AND c.Keterangan = 'eMas'
    AND a.KanwilFormatted = CAST(c.Kanwil AS VARCHAR(255))
    AND c.MatriksName = 'specialrate'
) d5
LEFT JOIN STG_NAMA_PAKET_REPORT d52 WITH (NOLOCK)
  ON d5.Paket_ID = d52.Paket_ID
LEFT JOIN STG_NAMA_BENEFIT_REPORT d53 WITH (NOLOCK)
  ON d5.Benefit_ID = d53.Benefit_ID
OUTER APPLY (
  SELECT TOP 1 
    c.PAKET_ID,
    c.BENEFIT_ID,
    c.StartDateInput,
    c.EndDateInput,
    c.StartDateApprove,
    c.EndDateApprove,
    c.MatriksName
  FROM STG_MATRIKS_REPORT_OP c WITH (NOLOCK)
  WHERE
    c.Dealer = 'PARTISIPAN'
    --AND a.createddate BETWEEN c.StartDateInput AND c.EndDateInput
    --AND a.TglApproveSPKDealer BETWEEN c.StartDateApprove AND c.EndDateApprove
    AND c.Keterangan = 'Kanwil Expo Offline'
    AND a.KanwilFormatted = CAST(c.Kanwil AS VARCHAR(255))
    AND c.MatriksName = 'spkice'
) d6
LEFT JOIN STG_NAMA_PAKET_REPORT d62 WITH (NOLOCK)
  ON d6.Paket_ID = d62.Paket_ID
LEFT JOIN STG_NAMA_BENEFIT_REPORT d63 WITH (NOLOCK)
  ON d6.Benefit_ID = d63.Benefit_ID
OUTER APPLY (
  SELECT TOP 1 
    c.PAKET_ID,
    c.BENEFIT_ID,
    c.StartDateInput,
    c.EndDateInput,
    c.StartDateApprove,
    c.EndDateApprove,
    c.MatriksName
  FROM STG_MATRIKS_REPORT_OP c WITH (NOLOCK)
  WHERE
    c.Dealer = 'PARTISIPAN BUNGA SAJA'
    AND a.KanwilFormatted = CAST(c.Kanwil AS VARCHAR(255))
    AND c.MatriksName = 'specialrate'
) d7
LEFT JOIN STG_NAMA_PAKET_REPORT d72 WITH (NOLOCK)
  ON d7.Paket_ID = d72.Paket_ID
LEFT JOIN STG_NAMA_BENEFIT_REPORT d73 WITH (NOLOCK)
  ON d7.Benefit_ID = d73.Benefit_ID
OUTER APPLY (
  SELECT TOP 1 
    c.PAKET_ID,
    c.BENEFIT_ID,
    c.StartDateInput,
    c.EndDateInput,
    c.StartDateApprove,
    c.EndDateApprove,
    c.MatriksName
  FROM STG_MATRIKS_REPORT_OP c WITH (NOLOCK)
  WHERE
    c.Dealer = 'PARTISIPAN BUNGA SAJA'
    AND a.KanwilFormatted = CAST(c.Kanwil AS VARCHAR(255))
    AND c.MatriksName = 'spkonlinejk'
) d8
LEFT JOIN STG_NAMA_PAKET_REPORT d82 WITH (NOLOCK)
  ON d8.Paket_ID = d82.Paket_ID
LEFT JOIN STG_NAMA_BENEFIT_REPORT d83 WITH (NOLOCK)
  ON d8.Benefit_ID = d83.Benefit_ID
OUTER APPLY (
  SELECT TOP 1 
    c.PAKET_ID,
    c.BENEFIT_ID,
    c.StartDateInput,
    c.EndDateInput,
    c.StartDateApprove,
    c.EndDateApprove,
    c.MatriksName
  FROM STG_MATRIKS_REPORT_OP c WITH (NOLOCK)
  WHERE
    c.Dealer = 'PARTISIPAN BUNGA SAJA'
    AND a.KanwilFormatted = CAST(c.Kanwil AS VARCHAR(255))
    AND c.MatriksName = 'spkonline'
) d9
LEFT JOIN STG_NAMA_PAKET_REPORT d92 WITH (NOLOCK)
  ON d9.Paket_ID = d92.Paket_ID
LEFT JOIN STG_NAMA_BENEFIT_REPORT d93 WITH (NOLOCK)
  ON d9.Benefit_ID = d93.Benefit_ID
OUTER APPLY (
  SELECT TOP 1 
    c.PAKET_ID,
    c.BENEFIT_ID,
    c.StartDateInput,
    c.EndDateInput,
    c.StartDateApprove,
    c.EndDateApprove,
    c.MatriksName
  FROM STG_MATRIKS_REPORT_OP c WITH (NOLOCK)
  WHERE
    c.Dealer = 'PARTISIPAN AUTOSHOW'
    AND a.KanwilFormatted = CAST(c.Kanwil AS VARCHAR(255))
    AND c.MatriksName = 'expoversary'
) d10
LEFT JOIN STG_NAMA_PAKET_REPORT d102 WITH (NOLOCK)
  ON d10.Paket_ID = d102.Paket_ID
LEFT JOIN STG_NAMA_BENEFIT_REPORT d103 WITH (NOLOCK)
  ON d10.Benefit_ID = d103.Benefit_ID
END
Leave a Comment