Query Report Op Prod
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