DECLARE @TopikKode VARCHAR(255) = 'GPSI-2019-00003'

DECLARE @result_table TABLE ( 
							sektor VARCHAR(255),
							depkom VARCHAR(255),
							departemen VARCHAR(255),
							menunggu_respon INT,
							dalam_proses INT,
							jumlah_belum_selesai INT,
							prosentase_belum_selesai TINYINT,
							jumlah_selesai INT,
							prosentase_selesai TINYINT,
							total_penugasan INT
						)

DECLARE 
	@AdkOrganizationName VARCHAR(255),
	@DeputiOrganizationInitial VARCHAR(255),
	@SatkerOrganizationInitial VARCHAR(255),
	@OrganizationCode VARCHAR(255)

DECLARE list_cursor CURSOR FAST_FORWARD FOR  
SELECT 
	AdkOrganizationName,
	DeputiOrganizationInitial,
	SatkerOrganizationInitial,
	OrganizationCode
FROM 
	[Master].OrganizationHirarki
WHERE
	OrganizationStatus = 1
	AND OrganizationLevel <= 3
	AND OrganizationIsSatuanKerja = 1
ORDER BY
	LEFT(KodeAdk, 3),
	DeputiOrganizationInitial,
	SatkerOrganizationInitial

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @AdkOrganizationName, @DeputiOrganizationInitial, @SatkerOrganizationInitial, @OrganizationCode

WHILE @@FETCH_STATUS = 0  
BEGIN

	DECLARE
		@menunggu_respon INT = 0,
		@dalam_proses INT = 0,
		@jumlah_belum_selesai INT = 0,
		@prosentase_belum_selesai TINYINT = 0,
		@jumlah_selesai INT = 0,
		@prosentase_selesai TINYINT = 0,
		@total_penugasan INT = 0

	-- saran untuk meningkatkan performa (kecepatan):
	-- jangan query dari view, tapi langsung ke tabelnya dan hanya untuk tabel-tabel yang dibutuhkan saja (join disini saja)
	-- sebaiknya gunakan panjang data sesuai yang diperlukan saja, misal jangan menggunakan VARCHAR(255) untuk kode satker/depkom dsb, secukupnya saja misal VARCHAR(6), dan juga untuk tipe data lainnya, lalu beri index jika diperlukan
	SELECT 
		@menunggu_respon = COALESCE(COUNT(AssignmentDetailStatus), 0)
	FROM 
		[Transaction].View_AssignmentDetails
	WHERE
		1 = 1
		AND OrganizationStatus = 1
		AND OrganizationLevel = 3
		AND AssignmentDetailStatus = 1
		AND TopikKode = @TopikKode
		AND OrganizationCode = @OrganizationCode
	GROUP BY 
		OrganizationCode

	SELECT 
		@dalam_proses = COALESCE(COUNT(AssignmentDetailStatus), 0)
	FROM 
		[Transaction].View_AssignmentDetails
	WHERE
		1 = 1
		AND OrganizationStatus = 1
		AND OrganizationLevel = 3
		AND AssignmentDetailStatus = 2
		AND TopikKode = @TopikKode
		AND OrganizationCode = @OrganizationCode
	GROUP BY 
		OrganizationCode

	SELECT 
		@jumlah_selesai = COALESCE(COUNT(AssignmentDetailStatus), 0)
	FROM 
		[Transaction].View_AssignmentDetails
	WHERE
		1 = 1
		AND OrganizationStatus = 1
		AND OrganizationLevel = 3
		AND AssignmentDetailStatus = 3
		AND TopikKode = @TopikKode
		AND OrganizationCode = @OrganizationCode
	GROUP BY 
		OrganizationCode

	SET @jumlah_belum_selesai = @menunggu_respon + @dalam_proses
	SET @total_penugasan = @jumlah_belum_selesai + @jumlah_selesai
	SET @prosentase_belum_selesai = @jumlah_belum_selesai * 100 / NULLIF(@total_penugasan, 0)
	SET @prosentase_selesai = @jumlah_selesai * 100 / NULLIF(@total_penugasan, 0)

	INSERT INTO @result_table ( 
							sektor,
							depkom,
							departemen,
							menunggu_respon,
							dalam_proses,
							jumlah_belum_selesai,
							prosentase_belum_selesai,
							jumlah_selesai,
							prosentase_selesai,
							total_penugasan
						)
		VALUES
		(
			@AdkOrganizationName, 
			@DeputiOrganizationInitial, 
			@SatkerOrganizationInitial,
			@menunggu_respon,
			@dalam_proses,
			@jumlah_belum_selesai,
			COALESCE(@prosentase_belum_selesai, 0),
			@jumlah_selesai,
			COALESCE(@prosentase_selesai, 0),
			@total_penugasan
		)

	FETCH NEXT FROM list_cursor INTO @AdkOrganizationName, @DeputiOrganizationInitial, @SatkerOrganizationInitial, @OrganizationCode
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 


SELECT
	*
FROM
	@result_table