SELECT
   * 
FROM
   (
      SELECT
         a.nip nip_peg,
         b.id_jns_absensi,
         b.jml,
         a.id,
         CONVERT(DATE, CONVERT(VARCHAR(8), a.tanggal_absen), 112) AS tanggal_1,
         a.tanggal_absen,
         CASE
            WHEN jam_masuk = '' THEN '' 
            ELSE SUBSTRING(jam_masuk, 1, 2) + ':' + SUBSTRING(jam_masuk, 3, 2) 
         END
         jam_masuk, 
         CASE
            WHEN jam_keluar = '' THEN '' 
            ELSE SUBSTRING(jam_keluar, 1, 2) + ':' + SUBSTRING(jam_keluar, 3, 2) 
         END
         jam_keluar, 
	    jam_efektif, 
	    jam_lembur, 
	    tipe_hari, 
	    tipe 
      FROM
         spg_report_absensi a 
	    INNER JOIN
         spg_report_absensi_detail b ON a.id = b.id 
	    INNER JOIN
         SPG_DATA_CURRENT c ON c.nip = a.nip 
	                          AND a.TANGGAL_ABSEN >= 
										  (
											SELECT
											   CONVERT(VARCHAR(10), tgl_masuk, 112) 
											FROM
											   SPG_DATA_CURRENT 
											WHERE
											   nip IN (
														SELECT
														ISNULL( 
																CASE
																	WHEN nip_lama = '' THEN NULL 
																	ELSE nip_lama 
																END
															, nip ) 
														FROM
														spg_data_current 
														WHERE
														nip = c.nip 
													)
										  )
         INNER JOIN
         SPG_JNS_ABSENSI d ON d.id = b.ID_JNS_ABSENSI 
      GROUP BY
         a.nip, b.id_jns_absensi, b.jml, a.id, CONVERT(DATE, CONVERT(VARCHAR(8), a.tanggal_absen), 112), c.KD_UNIT_ORG, a.TANGGAL_ABSEN, a.JAM_MASUK, a.JAM_KELUAR, a.JAM_EFEKTIF, a.JAM_LEMBUR, a.TIPE_HARI, a.TIPE, d.SKA_ID 
      HAVING
         a.nip = '02523' 
         AND d.SKA_ID != 'SKA-000029' 
         AND a.tanggal_absen >= '20210101' 
         AND a.tanggal_absen <= '20211231' 
      UNION ALL
      SELECT
         a.nip nip_peg,
         33 id_jns_absensi,
         b.jml,
         a.id,
         CONVERT(DATE, CONVERT(VARCHAR(8), a.tanggal_absen), 112) AS tanggal_1,
         a.tanggal_absen,
         CASE
            WHEN jam_masuk = '' THEN '' 
            ELSE SUBSTRING(jam_masuk, 1, 2) + ':' + SUBSTRING(jam_masuk, 3, 2) 
         END
         jam_masuk, 
         CASE
            WHEN jam_keluar = '' THEN '' 
            ELSE SUBSTRING(jam_keluar, 1, 2) + ':' + SUBSTRING(jam_keluar, 3, 2) 
         END
         jam_keluar, 
	    jam_efektif, 
	    jam_lembur, 
	    tipe_hari, 
	    tipe 
      FROM
         spg_report_absensi a 
         INNER JOIN
         spg_report_absensi_detail b ON a.id = b.id 
         INNER JOIN
         SPG_DATA_CURRENT c ON c.nip = a.nip 
                               AND a.TANGGAL_ABSEN >= 
										(
										SELECT
											CONVERT(VARCHAR(10), tgl_masuk, 112) 
										FROM
											SPG_DATA_CURRENT 
										WHERE
											nip IN 
											(
												SELECT
												ISNULL( 
														CASE
															WHEN
															nip_lama = '' 
															THEN
															NULL 
															ELSE
															nip_lama 
														END
													, nip ) 
												FROM
												spg_data_current 
												WHERE
												nip = c.nip 
											)
										)
         INNER JOIN
            SPG_JNS_ABSENSI d 
            ON d.id = b.ID_JNS_ABSENSI 
      GROUP BY
         a.nip, b.id_jns_absensi, b.jml, a.id, CONVERT(DATE, CONVERT(VARCHAR(8), a.tanggal_absen), 112), c.KD_UNIT_ORG, a.TANGGAL_ABSEN, a.JAM_MASUK, a.JAM_KELUAR, a.JAM_EFEKTIF, a.JAM_LEMBUR, a.TIPE_HARI, a.TIPE, d.id 
      HAVING
         a.nip = '02523' 
         AND d.id IN (9, 60)
         AND a.tanggal_absen >= '20210101' 
         AND a.tanggal_absen <= '20211231' 
   )
   a 
ORDER BY
   nip_peg, tanggal_1 ASC



SELECT 
	a.nip AS nip_peg, 
	a.nm_peg, 
	b.nm_golongan, 
	b.kd_gol
FROM 
	spg_data_current AS a
	INNER JOIN
	SPG_GOLONGAN AS b ON a.kd_gol = b.kd_gol
WHERE
	a.kd_unit_org = '<isi pake Satuan Kerja / Unit Kerja>'
	AND a.sts_pensiun = 0
	AND LEFT(a.nip, 1) <> 'h';