SELECT
	prs.Nama_Perusahaan,
	cab.Nama AS Nama_Cabang,
	pa.No_Proposal,
	pa.No_Polis,
	--pa.Tgl_Mulai,
	--pa.Tgl_Selesai,
	--CASE pa.AdMedika_Ya
 --   WHEN 1 THEN 'Ya'
 --   ELSE 'Tidak'
 -- END AS AdMedika,
  --pst.Status,
	pst.No_Peserta,
	pst.Nama,
	pst.Nik,
	dbo.func_GetUmur(pst.Tgl_Lahir, pst.Tgl_Mulai) AS Umur, 
	pst.Kelamin,
	--pst.Sebagai,
	sbg.Status_Peserta,
	--pst.Tgl_Mulai,
	--pst.Tgl_Lapse,
	--pst.Tgl_Masuk,
	--pst.Cabang,
	ri.Kelas AS RI_Kelas,
	COALESCE(lh.Kelas,'') AS LH_Kelas,
	rj.Kelas AS RJ_Kelas,
	bm.Nama_Bank,
	pr.Rek_No,
	pr.Bank_Cabang,
	pr.Rek_Nama
FROM
	dbo.Peserta AS pst INNER JOIN
	dbo.Polis_Asuransi AS pa ON pst.No_Proposal = pa.No_Proposal INNER JOIN
	dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs LEFT JOIN
	dbo.Cabang AS cab ON pst.Cabang = cab.Kode
	                     AND pa.No_Prs = cab.No_Prs LEFT JOIN
	dbo.Sebagai AS sbg ON pst.Sebagai = sbg.IDI LEFT JOIN
	(
	  SELECT
	  	No_Proposal,
	  	No_Peserta,
	  	Kelas
	  FROM
	  	dbo.Peserta_Produk
	  WHERE
	    Sub_Kelompok = '0302' -- RI
	) AS ri ON pst.No_Proposal = ri.No_Proposal
	           AND pst.No_Peserta = ri.No_Peserta LEFT JOIN
	(
	  SELECT
	  	No_Proposal,
	  	No_Peserta,
	  	Kelas
	  FROM
	  	dbo.Peserta_Produk
	  WHERE
	    Sub_Kelompok = '0304' -- LH
	) AS lh ON pst.No_Proposal = lh.No_Proposal
	           AND pst.No_Peserta = lh.No_Peserta LEFT JOIN
	(
	  SELECT
	  	No_Proposal,
	  	No_Peserta,
	  	Kelas
	  FROM
	  	dbo.Peserta_Produk
	  WHERE
	    Sub_Kelompok = '0301' -- RJ
	) AS rj ON pst.No_Proposal = rj.No_Proposal
	           AND pst.No_Peserta = rj.No_Peserta LEFT JOIN
	dbo.Peserta_Rekening AS pr ON pst.No_Peserta = pr.No_Peserta
	                              AND pa.No_Polis = pr.No_Polis LEFT JOIN
	dbo.Bank_Master AS bm ON pr.Kode_Bank = bm.Kode_Bank
WHERE
  pa.No_Polis IN ('2015.0002.1','2015.0003.1','2015.0001.1')
  --AND pa.Tgl_Mulai <= '2015-06-01'
  --AND pa.Tgl_Selesai >= '2015-06-01'
  AND pa.Status = 'I'
  AND pst.Status = 'I'  -- O
ORDER BY
  prs.Nama_Perusahaan,
  pst.No_Peserta
  
--SELECT * FROM dbo.Sub_Kelompok AS sk

--nama prs
--proposal
--no polis
--tgl mulai
--tgl selesai
--admedika
--no pst
--nama pst
--Umur    Status    Sebagai    Tgl_Mulai    RI_Kelas (0302)    LH_Kelas (0304)    RJ_Kelas (0301)
--Cabang

--aktif & item (keluar)

