--SELECT
--	*
--FROM
--	gt_lapse_header

SELECT
	  no_proposal,
	  no_peserta,
	  id_produk,
	  nama_peserta,
	  id_cabang,
	  nama_cabang,
	  sub_kelompok,
	  nama_produk,
	  tgl_mulai_produk,
	  tgl_selesai_produk,
	  tgl_transaksi,
	  hari_polis,
	  hari_produk_prev,
	  hari_produk_next,
	  Premi,
	  jml_klaim,
	  adm_prosen,
	  refund_premi,
	  --Premi - (adm_prosen * Premi) AS test2,
	  --adm_prosen * Premi AS test,
	  1.0 * hari_produk_next / hari_polis * Premi AS test3,
	  refund_premi_rounded,
	  status,
	  tgl_lapse_old
FROM
	gt_lapse_detail
------------

SELECT
	x.no_proposal,
	p.Nama_Perusahaan,
	x.refund_premi
FROM
	(
    SELECT
      no_proposal,
      SUM(refund_premi) AS refund_premi
    FROM
	    gt_lapse_detail
    WHERE
      status<>'C'
    GROUP BY
      no_proposal
	) AS x INNER JOIN
	dbo.Polis_Asuransi AS pa ON x.no_proposal = pa.No_Proposal INNER JOIN
	dbo.Perusahaan AS p ON pa.No_Prs = p.No_Prs
ORDER BY
  p.Nama_Perusahaan