--INSERT INTO
--  dbo.Peserta_Produk_Detail
--          (No_Ref,
--           No_Peserta,
--           Id_Produk,
--           Tgl_Mulai,
--           Tgl_Selesai,
--           Tgl_Ubah,
--           Pay_Date,
--           Status,
--           No_Proposal,
--           Sub_Kelompok,
--           Kelamin,
--           Usia,
--           Status_Kawin,
--           No_Induk,
--           Sebagai,
--           Cabang,
--           Kelas,
--           Resiko,
--           Benefit,
--           Premi,
--           Santunan,
--           Ekstra_Premi,
--           Curr_Premi,
--           Curr_Santunan,
--           Santunan_Duka,
--           Santunan_Cacat,
--           Santunan_Lain,
--           Kelamin_Old,
--           Usia_Old,
--           Status_Kawin_Old,
--           Cabang_Old,
--           Kelas_Old,
--           Resiko_Old,
--           Benefit_Old,
--           Premi_Old,
--           Santunan_Old,
--           Ekstra_Premi_Old,
--           Curr_Premi_Old,
--           Curr_Santunan_Old,
--           Santunan_Duka_Old,
--           Santunan_Cacat_Old,
--           Santunan_Lain_Old,
--           Adm_Keluar,
--           Jml_Klaim,
--           Jumlah,
--           Jenis,
--           Status1,
--           Change_By,
--           Change_Date,
--           Create_By,
--           Create_Date,
--           adm_prosen)
SELECT
	'2015.064524' AS No_Ref,
	pp.No_Peserta,
	pp.Id_Produk,
	pp.Tgl_Mulai,
	pp.Tgl_Selesai,
	pp.Tgl_Ubah,
	pp.Pay_Date,
	pp.Status,
	pp.No_Proposal,
	pp.Sub_Kelompok,
	pp.Kelamin_Old,
	pp.Usia_Old,
	pp.Status_Kawin_Old,
	pp.No_Induk,
	0 AS Sebagai, -- ???
	pp.Cabang_Old,
	pp.Kelas,  --	Kelas_Old ?
	pp.Resiko, --	Resiko_Old ?
	pp.Benefit,  --	Benefit_Old
	pp.Premi,
	--pp.Premi_Old,
	pp.Santunan,
	--pp.Santunan_Old,
	pp.Ekstra_Premi,
	--pp.Ekstra_Premi_Old,
	pp.Curr_Premi,
	--pp.Curr_Premi_Old,
	pp.Curr_Santunan,
	--pp.Curr_Santunan_Old,
	pp.Santunan_Duka,
	--pp.Santunan_Duka_Old,
	pp.Santunan_Cacat,
	--pp.Santunan_Cacat_Old,
	pp.Santunan_Lain,
	--pp.Santunan_Lain_Old,
	pp.Kelamin_Old,
	pp.Usia_Old,
	pp.Status_Kawin_Old,
	pp.Cabang_Old,
	pp.Kelas_Old,
	pp.Resiko_Old,
	pp.Benefit_Old,
	pp.Premi_Old,
	pp.Santunan_Old,
	pp.Ekstra_Premi_Old,
	pp.Curr_Premi_Old AS Curr_Premi_Old, -- ???
	pp.Curr_Santunan_Old,
	pp.Santunan_Duka_Old,
	pp.Santunan_Cacat_Old,
	pp.Santunan_Lain_Old,
	lapse.jml_adm AS Adm_Keluar,  -- ??
	lapse.Jml_Klaim AS Jml_Klaim,  -- ??
	lapse.jml_refund AS Jumlah,  -- ??
	pp.Jenis,
	NULL AS Status1,
	NULL AS Change_By,
	NULL Change_Date,
	NULL AS Create_By,
	NULL AS Create_Date,
	15 AS adm_prosen
FROM
	dbo.Peserta_Produk AS pp INNER JOIN
	(
    SELECT
      a.No_Proposal,
      a.No_Peserta,
      a.Id_Produk,
      a.Sub_Kelompok,
      sk.Nama_Sub,
      a.Premi,
      a.Curr_Premi,
      a.Curr_Premi_Old,
	    premi_pakai = ROUND(a.Premi * a.prop_pakai, 0),
      premi_sisa = ROUND(a.Premi * a.prop_sisa, 0),
      jml_adm = ROUND(0.15 * (a.Premi * a.prop_sisa), 0),
      k.Jml_Klaim,
      jml_refund = CASE WHEN ROUND(a.Premi * a.prop_sisa * (1.00 - 0.15) - COALESCE(k.Jml_Klaim, 0), 0) < 0 THEN 0
                        ELSE ROUND(a.Premi * a.prop_sisa * (1.00 - 0.15) - COALESCE(k.Jml_Klaim, 0), 0)
                   END
    FROM
	    (
        SELECT
          pp.No_Proposal,
          pp.No_Peserta,
          pp.Id_Produk,
          pp.Sub_Kelompok,
          pp.Premi,
          pp.Curr_Premi,
          pp.Curr_Premi_Old,
	        hari_pakai = 1.0 * DATEDIFF(day, pp.Tgl_Mulai, pp.Tgl_Trans),  -- hari_pakai = 1.0 * DATEDIFF(day, d.tgl_mulai_produk, d.tgl_transaksi),
          hari_sisa = 1.0 * DATEDIFF(day, pp.Tgl_Trans, pp.Tgl_Selesai + 1),  -- hari_sisa = 1.0 * DATEDIFF(day, d.tgl_transaksi, d.tgl_selesai_produk + 1),
          prop_pakai = 1.0 * DATEDIFF(day, pp.Tgl_Mulai, pp.Tgl_Trans - 1) / DATEDIFF(day, pp.Tgl_Mulai, pp.Tgl_Selesai + 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, pp.Tgl_Trans, pp.Tgl_Selesai + 1) / DATEDIFF(day, pp.Tgl_Mulai, pp.Tgl_Selesai + 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)
        FROM
	        dbo.Peserta_Produk AS pp
	    ) AS a LEFT JOIN
	    (
        SELECT
          kp.No_Proposal,
          kp.No_Peserta,
          kp.Id_Produk,
	        SUM(Jml_Dibayar) AS Jml_Klaim
        FROM
	        dbo.Klaim_Peserta AS kp
        GROUP BY
          kp.No_Proposal,
          kp.No_Peserta,
          kp.Id_Produk
      ) AS k ON a.No_Proposal = k.No_Proposal
                AND a.No_Peserta = k.No_Peserta
                AND a.Id_Produk = k.Id_Produk
      INNER JOIN dbo.Sub_Kelompok AS sk ON a.Sub_Kelompok = sk.Sub_Kelompok
    --WHERE
    --  a.No_Proposal = '2014.0832'
    --  AND a.No_Peserta = '2000004170-0'
	) AS lapse ON pp.No_Proposal = lapse.No_Proposal
	              AND pp.No_Peserta = lapse.No_Peserta
	              AND pp.Sub_Kelompok = lapse.Sub_Kelompok
	              AND pp.Id_Produk = lapse.Id_Produk
WHERE
  pp.No_Proposal = '2014.0843'
  AND pp.No_Peserta IN ('2010046365-0', '2010046394-0')
  
--------------------------------------------  

---- update ----

SELECT
	*
--UPDATE
--  ppd
--SET
--  Adm_Keluar = lapse.jml_adm,
--  Jumlah = lapse.jml_refund,
--  Curr_Premi_Old = premi_sisa
FROM
	(
    SELECT
      a.No_Proposal,
      a.No_Peserta,
      a.Id_Produk,
      a.Sub_Kelompok,
      sk.Nama_Sub,
      a.Premi,
      a.Curr_Premi,
      a.Curr_Premi_Old,
	    premi_pakai = ROUND(a.Premi * a.prop_pakai, 0),
      premi_sisa = ROUND(a.Premi * a.prop_sisa, 0),
      jml_adm = ROUND(0.15 * (a.Premi * a.prop_sisa), 0),
      k.Jml_Klaim,
      jml_refund = CASE WHEN ROUND(a.Premi * a.prop_sisa * (1.00 - 0.15) - COALESCE(k.Jml_Klaim, 0), 0) < 0 THEN 0
                        ELSE ROUND(a.Premi * a.prop_sisa * (1.00 - 0.15) - COALESCE(k.Jml_Klaim, 0), 0)
                   END,
      hari_pakai,
      hari_sisa,
      test1,
      test2
    FROM
	    (
        SELECT
          pp.No_Proposal,
          pp.No_Peserta,
          pp.Id_Produk,
          pp.Sub_Kelompok,
          pp.Premi,
          pp.Curr_Premi,
          pp.Curr_Premi_Old,
	        hari_pakai = 1.0 * DATEDIFF(day, pp.Tgl_Mulai, pp.Tgl_Trans),  -- hari_pakai = 1.0 * DATEDIFF(day, d.tgl_mulai_produk, d.tgl_transaksi),
          hari_sisa = 1.0 * DATEDIFF(day, pp.Tgl_Trans, pp.Tgl_Selesai + 1),  -- hari_sisa = 1.0 * DATEDIFF(day, d.tgl_transaksi, d.tgl_selesai_produk + 1),
          prop_pakai = 1.0 * DATEDIFF(day, pp.Tgl_Mulai, pp.Tgl_Trans - 1) / DATEDIFF(day, pp.Tgl_Mulai, pp.Tgl_Selesai + 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, pp.Tgl_Trans, pp.Tgl_Selesai + 1) / DATEDIFF(day, pp.Tgl_Mulai, pp.Tgl_Selesai + 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)
          prop_sisa = 1.0 * DATEDIFF(day, pp.Tgl_Trans - 1, pp.Tgl_Selesai + 1) / DATEDIFF(day, pp.Tgl_Mulai, pp.Tgl_Selesai + 1),
          DATEDIFF(day, pp.Tgl_Trans - 1, pp.Tgl_Selesai + 1) AS test1,
          DATEDIFF(day, pp.Tgl_Mulai, pp.Tgl_Selesai + 1) AS test2
        FROM
	        dbo.Peserta_Produk AS pp
	    ) AS a LEFT JOIN
	    (
        SELECT
          kp.No_Proposal,
          kp.No_Peserta,
          kp.Id_Produk,
	        SUM(Jml_Dibayar) AS Jml_Klaim
        FROM
	        dbo.Klaim_Peserta AS kp
        GROUP BY
          kp.No_Proposal,
          kp.No_Peserta,
          kp.Id_Produk
      ) AS k ON a.No_Proposal = k.No_Proposal
                AND a.No_Peserta = k.No_Peserta
                AND a.Id_Produk = k.Id_Produk
      INNER JOIN dbo.Sub_Kelompok AS sk ON a.Sub_Kelompok = sk.Sub_Kelompok
    WHERE
      a.No_Proposal = '2014.0843'
      AND a.No_Peserta IN ('2010046365-0', '2010046394-0')
	) AS lapse INNER JOIN
	dbo.Peserta_Produk_Detail AS ppd ON lapse.Sub_Kelompok = ppd.Sub_Kelompok
	                                    AND lapse.No_Proposal = ppd.No_Proposal
	                                    AND lapse.No_Peserta = ppd.No_Peserta
WHERE
  ppd.No_Ref = '2015.064524'

---

--SELECT
--	*
UPDATE
  ppd
SET
  Adm_Keluar = lapse.jml_adm,
  Jumlah = lapse.jml_refund,
  Curr_Premi_Old = premi_sisa
FROM
	(
    SELECT
      a.No_Proposal,
      a.No_Peserta,
      a.Id_Produk,
      a.Sub_Kelompok,
      sk.Nama_Sub,
      a.Premi,
      a.Curr_Premi,
      a.Curr_Premi_Old,
	    premi_pakai = ROUND(a.Premi * a.prop_pakai, 0),
      premi_sisa = ROUND(a.Premi * a.prop_sisa, 0),
      jml_adm = ROUND(0.15 * (a.Premi * a.prop_sisa), 0),
      k.Jml_Klaim,
      jml_refund = CASE WHEN ROUND(a.Premi * a.prop_sisa * (1.00 - 0.15) - COALESCE(k.Jml_Klaim, 0), 0) < 0 THEN 0
                        ELSE ROUND(a.Premi * a.prop_sisa * (1.00 - 0.15) - COALESCE(k.Jml_Klaim, 0), 0)
                   END,
      hari_pakai,
      hari_sisa,
      test1,
      test2
    FROM
	    (
        SELECT
          pp.No_Proposal,
          pp.No_Peserta,
          pp.Id_Produk,
          pp.Sub_Kelompok,
          pp.Premi,
          pp.Curr_Premi,
          pp.Curr_Premi_Old,
	        hari_pakai = 1.0 * DATEDIFF(day, pp.Tgl_Mulai, pp.Tgl_Trans),  -- hari_pakai = 1.0 * DATEDIFF(day, d.tgl_mulai_produk, d.tgl_transaksi),
          hari_sisa = 1.0 * DATEDIFF(day, pp.Tgl_Trans, pp.Tgl_Selesai + 1),  -- hari_sisa = 1.0 * DATEDIFF(day, d.tgl_transaksi, d.tgl_selesai_produk + 1),
          prop_pakai = 1.0 * DATEDIFF(day, pp.Tgl_Mulai, pp.Tgl_Trans - 1) / DATEDIFF(day, pp.Tgl_Mulai, pp.Tgl_Selesai + 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, pp.Tgl_Trans, pp.Tgl_Selesai + 1) / DATEDIFF(day, pp.Tgl_Mulai, pp.Tgl_Selesai + 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)
          prop_sisa = 1.0 * DATEDIFF(day, pp.Tgl_Trans - 1, pp.Tgl_Selesai + 1) / DATEDIFF(day, pp.Tgl_Mulai, pp.Tgl_Selesai + 1),
          DATEDIFF(day, pp.Tgl_Trans - 1, pp.Tgl_Selesai + 1) AS test1,
          DATEDIFF(day, pp.Tgl_Mulai, pp.Tgl_Selesai + 1) AS test2
        FROM
	        dbo.Peserta_Produk AS pp
	    ) AS a LEFT JOIN
	    (
        SELECT
          kp.No_Proposal,
          kp.No_Peserta,
          kp.Id_Produk,
	        SUM(Jml_Dibayar) AS Jml_Klaim
        FROM
	        dbo.Klaim_Peserta AS kp
        GROUP BY
          kp.No_Proposal,
          kp.No_Peserta,
          kp.Id_Produk
      ) AS k ON a.No_Proposal = k.No_Proposal
                AND a.No_Peserta = k.No_Peserta
                AND a.Id_Produk = k.Id_Produk
      INNER JOIN dbo.Sub_Kelompok AS sk ON a.Sub_Kelompok = sk.Sub_Kelompok
    WHERE
      a.No_Proposal = '2014.0832'
      AND a.No_Peserta = '2000004170-0'
	) AS lapse INNER JOIN
	dbo.Peserta_Produk_Detail AS ppd ON lapse.Sub_Kelompok = ppd.Sub_Kelompok
	                                    AND lapse.No_Proposal = ppd.No_Proposal
	                                    AND lapse.No_Peserta = ppd.No_Peserta
WHERE
  ppd.No_Ref = '2015.072347'