USE grpInsurance

go

--UPDATE
--  Peserta_Produk_Detail
--SET
--  Curr_Premi_Old = g1.premi_pakai
SELECT
  g1.premi_pakai
FROM
  (
    SELECT
      *,
      premi_pakai = ROUND(Premi * prop_pakai, 0),
      premi_sisa = ROUND(Premi * prop_sisa, 0),
      jml_adm = ROUND(adm_prosen * (Premi * prop_sisa), 0),
      jml_refund = CASE WHEN ROUND(Premi * prop_sisa * (1.00 - adm_prosen) - COALESCE(jml_klaim, 0), 0) < 0 THEN 0
                        ELSE ROUND(Premi * prop_sisa * (1.00 - adm_prosen) - COALESCE(jml_klaim, 0), 0)
                   END
    FROM
      (
        SELECT
          h.no_ref,
          d.no_proposal,
          d.no_peserta,
          d.id_produk,
          d.nama_peserta,
          d.id_cabang,
          d.nama_cabang,
          d.sub_kelompok,
          d.tgl_mulai_produk,
          d.tgl_selesai_produk,
          d.tgl_transaksi,
          hari_pakai = 1.0 * DATEDIFF(day, d.tgl_mulai_produk, d.tgl_transaksi),
          hari_sisa = 1.0 * DATEDIFF(day, d.tgl_transaksi, d.tgl_selesai_produk + 1),
          prop_pakai = 1.0 * DATEDIFF(day, d.tgl_mulai_produk, d.tgl_transaksi - 1) / DATEDIFF(day, d.tgl_mulai_produk, d.tgl_selesai_produk + 1), --tgl transaksi dikurang 1
          prop_sisa = 1.0 * DATEDIFF(day, d.tgl_transaksi, d.tgl_selesai_produk + 1) / DATEDIFF(day, d.tgl_mulai_produk, d.tgl_selesai_produk + 1),
          d.Premi,
          d.jml_klaim,
          d.adm_prosen
        FROM
          gt_lapse_detail AS d INNER JOIN 
          gt_lapse_header AS h ON h.no_proposal = d.no_proposal
      ) AS t1
  ) AS g1
  INNER JOIN
  Peserta_Produk_Detail ON g1.no_ref = Peserta_Produk_Detail.No_Ref
WHERE
  Peserta_Produk_Detail.No_Ref = g1.no_ref
  AND Peserta_Produk_Detail.No_Peserta = g1.no_peserta
  AND Peserta_Produk_Detail.No_proposal = g1.no_proposal
  AND Peserta_Produk_Detail.id_produk = g1.id_produk
  AND g1.no_ref = '2015.072347'
