SELECT
	cpu.no_polis,
	pp.Nama_Perusahaan,
	cpu.username,
	cpu.no_peserta,
	ps.Nama,
	CASE cpu.status
		WHEN 'I' THEN 'Sudah Aktivasi'
		WHEN 'G' THEN 'Belum Aktivasi'
		ELSE 'Unknown'
	END AS status
FROM
	dbo.custport_user_login_client AS cpu INNER JOIN
	(
		SELECT DISTINCT
			pa.No_Polis,
			prs.Nama_Perusahaan
		FROM
			dbo.Polis_Asuransi AS pa INNER JOIN
			dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs
		WHERE
			pa.Active_Ya = 1
	) AS pp ON cpu.no_polis = pp.No_Polis INNER JOIN
	(
		SELECT DISTINCT
			pst.No_Peserta,
			pst.Nama,
			pa.No_Polis
		FROM
			dbo.Peserta AS pst INNER JOIN
			dbo.Polis_Asuransi AS pa ON pst.No_Proposal = pa.No_Proposal
		WHERE
			pa.Active_Ya = 1
			AND pa.Status = 'I'
	) AS ps ON pp.No_Polis = ps.No_Polis
				AND cpu.no_peserta = ps.No_Peserta
ORDER BY
	pp.Nama_Perusahaan,
	ps.Nama

SELECT 
	* 
FROM 
	dbo.Peserta AS pst INNER JOIN
	dbo.Polis_Asuransi AS pa ON pst.No_Proposal = pa.No_Proposal
	 
