SELECT
  ntm.No_Ref,
  pa.No_Polis,
  p.No_Peserta,
  p.Nama,
  --mu.Deskripsi AS mata_uang,
  pr.Kode_Bank,
  pr.Rek_No,
  SUM(krd.Jml_Dibayar - krd.Jml_Dibayar_Old) AS Jml_Bayar
FROM
	Klaim_Ref_Detail krd
	INNER JOIN
  Peserta p
  ON (krd.No_Proposal = p.No_Proposal) AND 
     (krd.No_Santunan = p.No_Peserta) 
  INNER JOIN
  Polis_Asuransi pa
  ON krd.No_Proposal = pa.No_Proposal 
  INNER JOIN
  Note_Terbit_Master ntm
  ON krd.No_Ref = ntm.No_Ref 
  INNER JOIN
  Perusahaan prs
  ON p.No_Prs = prs.No_Prs 
  LEFT OUTER JOIN
  Peserta_Rekening pr
  ON LEFT(krd.No_Peserta, 11) + '0' = pr.No_Peserta AND pa.No_Polis = pr.No_Polis 
  LEFT OUTER JOIN
  Bank_Master bm
  ON pr.Kode_Bank = bm.Kode_Bank
  LEFT JOIN
  Mata_Uang mu
  ON ntm.Mata_Uang = mu.Kode
WHERE
  (krd.No_Proposal = '2016.0058') AND
  (ntm.Tgl_Bayar = dbo.func_fixed_date('2017-03-03')) AND
  (ntm.jenis ='21')
   AND (ntm.status = 'I')
GROUP BY
  ntm.No_Ref,
  pa.No_Polis,
  p.No_Peserta,
  p.Nama,
  pr.Kode_Bank,
  pr.Rek_No

SELECT
	*
FROM
	dbo.Peserta_Rekening
WHERE
  No_Polis = '2015.0033.1'
  AND No_Peserta IN ('2015007666-0',
'2015007666-1',
'2015007678-1',
'2015007699-0')

SELECT
	*
FROM
	dbo.Peserta
WHERE
  No_Peserta IN ('2015036991-0','2015007666-0')

SELECT
	*
FROM
	dbo.Polis_Asuransi
WHERE
  No_Proposal IN ('2016.0058','2017.0074')

SELECT * FROM dbo.Peserta_Rekening WHERE No_Peserta IN (SELECT No_Peserta FROM dbo.Peserta WHERE No_Proposal='2016.0058' AND Status='I')
SELECT * FROM dbo.Peserta WHERE No_Proposal='2016.0058' AND Status='I' AND No_Peserta NOT IN (SELECT No_Peserta FROM dbo.Peserta_Rekening WHERE No_Polis='2015.0033.1')

SELECT     TOP (100) PERCENT dbo.Peserta.Status AS SPst, dbo.Peserta_Rekening.No_Peserta, dbo.Peserta.Nama, dbo.Peserta_Rekening.Rek_No, 
                      dbo.Peserta_Rekening.Rek_Nama, dbo.Bank_Master.Nama_Bank, dbo.Peserta_Rekening.Bank_Cabang, dbo.Peserta_Rekening.Bank_Lokasi, 
                      dbo.Peserta_Rekening.No_Polis, dbo.Peserta_Rekening.Bank_Nama, dbo.Peserta_Rekening.Create_By, dbo.Peserta_Rekening.Create_Date, 
                      dbo.Peserta_Rekening.Change_By, dbo.Peserta_Rekening.Change_Date
FROM         dbo.Peserta_Rekening INNER JOIN
                      dbo.Polis_Asuransi ON dbo.Peserta_Rekening.No_Polis = dbo.Polis_Asuransi.No_Polis INNER JOIN
                      dbo.Peserta ON dbo.Peserta_Rekening.No_Peserta = dbo.Peserta.No_Peserta AND 
                      dbo.Polis_Asuransi.No_Proposal = dbo.Peserta.No_Proposal LEFT OUTER JOIN
                      dbo.Bank_Master ON dbo.Peserta_Rekening.Kode_Bank = dbo.Bank_Master.Kode_Bank
WHERE     --(dbo.Polis_Asuransi.Active_Ya = 1)
dbo.Polis_Asuransi.No_Polis = '2015.0033.1'
