Query Report Op Prod
unknown
sqlserver
5 months ago
119 kB
1
Indexable
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