DECLARE
	@nip VARCHAR(max) = '',
	@kd_unit_org VARCHAR(max) = '102009003000000000000000',
	@dari DATE = '2019-12-01',
	@sampai DATE = '2019-12-28'

--SELECT
--	sdc.NIP,
--	sdc.NM_PEG,
--	sdc.KD_UNIT_ORG,
--	suo.nama_satker_organisasi,
--	ss1.NIP,
--	ss2.KD_UNIT_ORG
--FROM
--	dbo.SPG_08_UNIT_ORGANISASI AS suo INNER JOIN
--	dbo.SPG_DATA_CURRENT AS sdc ON suo.KD_UNIT_ORG = sdc.KD_UNIT_ORG
--	LEFT JOIN
--	(
--		SELECT
--			LTRIM(RTRIM(Item)) AS NIP
--		FROM
--			dbo.SplitStrings_CTE (@nip,',')
--	) AS ss1 ON ss1.NIP = sdc.NIP
--	LEFT JOIN
--	(
--		SELECT
--			LTRIM(RTRIM(Item)) AS KD_UNIT_ORG
--		FROM
--			dbo.SplitStrings_CTE (@kd_unit_org,',')
--	) AS ss2 ON ss2.KD_UNIT_ORG = sdc.KD_UNIT_ORG
--WHERE
--	(COALESCE(ss1.NIP, '') <> '' OR ISNULL(@nip, '') = '')
--	AND (COALESCE(ss2.KD_UNIT_ORG, '') <> ''  OR ISNULL(@kd_unit_org, '') = '')


IF OBJECT_ID(N'tempdb..##hasil') IS NOT NULL
BEGIN
DROP TABLE ##hasil
END


DECLARE
	@qry_create_tmp VARCHAR(max) = 'CREATE TABLE ##hasil ( NIP VARCHAR(8), NAMA VARCHAR(100), KD_UNIT_ORG VARCHAR(50), SATKER VARCHAR(300), ';
SELECT @qry_create_tmp = @qry_create_tmp + Stuff(
  (SELECT ' SMALLINT DEFAULT 0, ' + KD_ABSENSI FROM SPG_JNS_ABSENSI WHERE ACTIVE = 1 AND KD_ABSENSI <> 'LBR' GROUP BY KD_ABSENSI ORDER BY MIN(ORDER_BY) FOR XML PATH(''),TYPE)
  .value('text()[1]','varchar(max)'),1,21,N'')
SET @qry_create_tmp = @qry_create_tmp + ' SMALLINT DEFAULT 0 )';
PRINT @qry_create_tmp 
EXEC(@qry_create_tmp)

EXEC('SELECT * FROM ##hasil ')

--EXEC sp_executesql N'INSERT INTO ##hasil (NIP) SELECT @nip', N'@nip VARCHAR(6)', @nip = @nip

DECLARE @qry_ins_data_pegawai NVARCHAR(MAX)
SET @qry_ins_data_pegawai = 'INSERT INTO ##hasil (NIP, NAMA, KD_UNIT_ORG, SATKER) 
SELECT
	sdc.NIP,
	sdc.NM_PEG,
	sdc.KD_UNIT_ORG,
	suo.nama_satker_organisasi
FROM
	dbo.SPG_08_UNIT_ORGANISASI AS suo INNER JOIN
	dbo.SPG_DATA_CURRENT AS sdc ON suo.KD_UNIT_ORG = sdc.KD_UNIT_ORG
	LEFT JOIN
	(
		SELECT
			LTRIM(RTRIM(Item)) AS NIP
		FROM
			dbo.SplitStrings_CTE (@nip,'','')
	) AS ss1 ON ss1.NIP = sdc.NIP
	LEFT JOIN
	(
		SELECT
			LTRIM(RTRIM(Item)) AS KD_UNIT_ORG
		FROM
			dbo.SplitStrings_CTE (@kd_unit_org,'','')
	) AS ss2 ON ss2.KD_UNIT_ORG = sdc.KD_UNIT_ORG
WHERE
	(COALESCE(ss1.NIP, '''') <> '''' OR ISNULL(@nip, '''') = '''')
	AND (COALESCE(ss2.KD_UNIT_ORG, '''') <> ''''  OR ISNULL(@kd_unit_org, '''') = '''')'
EXEC sp_executesql @qry_ins_data_pegawai, N'@nip VARCHAR(max), @kd_unit_org VARCHAR(max)', @nip = @nip, @kd_unit_org = @kd_unit_org

EXEC('SELECT * FROM ##hasil ')


DECLARE @c_nip VARCHAR(6)
DECLARE nip_cursor CURSOR FAST_FORWARD FOR
SELECT
	NIP
FROM
	##hasil

OPEN nip_cursor
FETCH NEXT FROM nip_cursor INTO @c_nip

WHILE @@FETCH_STATUS = 0
BEGIN

	DECLARE
		@data_update VARCHAR(max)
	SELECT
	@data_update = Stuff(
	  (
	SELECT
		--sra.NIP,
		', ' + sja.KD_ABSENSI + '=' + CAST(COUNT(sra.ID) AS VARCHAR(8))
	FROM
		dbo.SPG_REPORT_ABSENSI AS sra INNER JOIN
		dbo.SPG_DATA_CURRENT AS sdc ON sra.NIP = sdc.NIP CROSS APPLY
		dbo.SplitStrings_CTE (REPLACE(sra.TIPE, ' ', '|'),'|') AS ss INNER JOIN
		dbo.SPG_JNS_ABSENSI AS sja ON ss.Item = sja.ID
	WHERE
		sra.NIP = @c_nip
		AND CAST(CAST(sra.TANGGAL_ABSEN AS VARCHAR(8)) AS DATE) >= @dari
		AND CAST(CAST(sra.TANGGAL_ABSEN AS VARCHAR(8)) AS DATE) <= @sampai
		AND sja.ACTIVE = 1 AND sja.KD_ABSENSI <> 'LBR'
	GROUP BY
		sra.NIP,
		sja.KD_ABSENSI
	--ORDER BY
	--	sra.TANGGAL_ABSEN
	 FOR XML PATH(''),TYPE).value('text()[1]','varchar(max)'),1,2,N'')

	DECLARE @sql nvarchar(max)
	SET @sql = 'UPDATE ##hasil SET ' + @data_update + ' WHERE NIP = @nip'
	EXEC sp_executesql @sql, N'@data_update VARCHAR(max), @nip VARCHAR(6)', @data_update = @data_update, @nip = @c_nip

	FETCH NEXT FROM nip_cursor INTO @c_nip
END

CLOSE nip_cursor
DEALLOCATE nip_cursor

EXEC('SELECT * FROM ##hasil ')
