/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (1000) [PaktaIntegritasTransactionId]
      ,[PaktaIntegritasPeriodId]
      ,[nip]
      ,[nama]
      ,[nama_satker]
      ,[kode_satker]
      ,[waktu_pengisian]
      ,[IsActive]
      ,[CreatedDt]
      ,[UpdatedDt]
      ,[CreatedBy]
      ,[UpdatedBy]
      ,[IsDeleted]
  FROM [OJK_SMARTHR].[dbo].[PaktaIntegritasTransactions]
ORDER BY PaktaIntegritasTransactionId DESC

SELECT * FROM [dbo].[PaktaIntegritasPeriods]

SELECT
	pip.PaktaIntegritasPeriodId,
	pip.PaktaIntegritasPeriodName,
	s.nama_satker,
	pit.kode_satker,
	COUNT(pit.nip) AS jml_pengisi,
	pis.jml_pegawai,
	CAST(jml_pegawai AS INT) * 100.00 AS persentase,
	CAST(ROUND(((CAST(COUNT(pit.nip) AS NUMERIC(4,2)) / CAST(jml_pegawai AS  NUMERIC(4,2))) * 100), 2) AS NUMERIC(3,2)) AS persentase
FROM
	PaktaIntegritasTransactions AS pit INNER JOIN
	PaktaIntegritasPeriods AS pip ON pit.PaktaIntegritasPeriodId = pip.PaktaIntegritasPeriodId INNER JOIN
	PaktaIntegritasSatkers AS pis ON pit.PaktaIntegritasPeriodId = pis.PaktaIntegritasPeriodId AND pit.kode_satker = pis.kode_satker INNER JOIN
	Satkers AS s ON pit.kode_satker = s.kode_satker
WHERE
	pit.IsActive = 1
	AND pit.IsDeleted = 0
	AND pip.IsActive = 1
	AND pip.IsDeleted = 0
GROUP BY
	pip.PaktaIntegritasPeriodId,
	pip.PaktaIntegritasPeriodName,
	pit.kode_satker,
	s.nama_satker,
	pis.jml_pegawai