USE [HRIS]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[view_etlcacm_hari_libur] AS 

	SELECT 
		l.TAHUN AS [Tahun],
		l.TANGGAL_HARI_LIBUR AS [Tanggal Hari Libur],
		CASE l.NASIONAL
			WHEN '1' THEN 'Nasional'
			ELSE STUFF(
						(
							SELECT ', ' + k.NM_KANTOR FROM SPG_HARI_LIBUR_KANTOR AS lk LEFT JOIN SPG_08_KANTOR AS k ON lk.KD_KANTOR = k.KD_KANTOR WHERE lk.SHL_ID = l.SHL_ID FOR XML PATH, TYPE
						).value(N'.[1]', N'VARCHAR(MAX)')
						, 1, 2, ''
					)
		END AS [Lingkup Libur],
		l.KETERANGAN AS [Keterangan]
	FROM 
		dbo.SPG_HARI_LIBUR AS l


GO
