SELECT * FROM [GrpInsurance].[dbo].[custport_user_login_client] WHERE no_polis NOT IN ('000','1992.0011.0') AND username NOT LIKE '20%' AND status='I'
SELECT DISTINCT
	p.[no_polis],
	prs.Nama_Perusahaan,
	COALESCE(aktif.jml,0) AS jml_aktif,
	COALESCE(nonaktif.jml,0) AS jml_nonaktif
FROM
	[GrpInsurance].[dbo].[custport_user_login_client] AS p INNER JOIN
	Polis_Asuransi AS pa ON p.no_polis = pa.No_Polis INNER JOIN
	Perusahaan AS prs ON pa.No_Prs = prs.No_prs LEFT JOIN
	(
		SELECT
			no_polis,
			COUNT(username) AS jml
		FROM
			custport_user_login_client
		WHERE
			status = 'I'
			AND username NOT LIKE '20%'
		GROUP BY
			no_polis
	) AS aktif ON p.no_polis = aktif.no_polis LEFT JOIN
	(
		SELECT
			no_polis,
			COUNT(username) AS jml
		FROM
			custport_user_login_client
		WHERE
			status = 'G'
			AND username NOT LIKE '20%'
		GROUP BY
			no_polis
	) AS nonaktif ON p.no_polis = nonaktif.no_polis
WHERE
--	p.status = 'I'
	p.username NOT LIKE '20%'
	AND p.no_polis NOT IN ('000','1992.0011.0')
ORDER BY
	prs.Nama_Perusahaan
