DECLARE @no_proposal VARCHAR(9) = '2014.0835'

--DECLARE @hasil TABLE ( 
--DROP TABLE ##temp_tabel_20160907
CREATE TABLE ##temp_tabel_20160907 ( 
	                    [No_Polis] [varchar](11) NULL,
	                    [No_Proposal] [varchar](9) NULL,
	                    [No_Peserta] [varchar](12) NULL,
	                    [Nama_Perusahaan] [varchar](50) NULL,
	                    [Nama] [varchar](50) NULL,
	                    [Tgl_Lahir] [datetime] NULL,
	                    [Usia] [int] NULL,
	                    [Status_Kawin] [varchar](2) NULL,
	                    [Kelamin] [varchar](1) NULL,
	                    [Sebagai] [smallint] NULL,
	                    [Nik] [varchar](30) NULL,
	                    [Cabang] [varchar](8) NULL,
	                    [Grade] [smallint] NULL,
	                    [Pln] [smallint] NULL,
	                    [Kelas] [varchar](10) NULL,
	                    [Tgl_Masuk] [datetime] NULL,
	                    [Tgl_Mulai] [datetime] NULL,
	                    [Tgl_Selesai] [datetime] NULL,
	                    [Tgl_Lapse] [datetime] NULL,
	                    [Status] [varchar](1) NULL,
	                    [Create_By] [varchar](4) NULL,
	                    [Create_Date] [datetime] NULL,
	                    [Change_By] [varchar](4) NULL,
	                    [Change_Date] [datetime] NULL,
	                    [Keterangan] [varchar](30) NULL,
	                    [2014.03383_kelas] VARCHAR(10),
	                    [2014.03383_resiko] VARCHAR(15),
	                    [2014.03383_benefit] FLOAT,
	                    [2014.03383_santunan] FLOAT,
	                    [2014.03383_premi] FLOAT,
                      [2014.03384_kelas] VARCHAR(10),
                      [2014.03384_resiko] VARCHAR(15),
                      [2014.03384_benefit] FLOAT,
                      [2014.03384_santunan] FLOAT,
                      [2014.03384_premi] FLOAT,
                      [2014.03385_kelas] VARCHAR(10),
                      [2014.03385_resiko] VARCHAR(15),
                      [2014.03385_benefit] FLOAT,
                      [2014.03385_santunan] FLOAT,
                      [2014.03385_premi] FLOAT,
                      [2014.03386_kelas] VARCHAR(10),
                      [2014.03386_resiko] VARCHAR(15),
                      [2014.03386_benefit] FLOAT,
                      [2014.03386_santunan] FLOAT,
                      [2014.03386_premi] FLOAT,
                      [2014.03387_kelas] VARCHAR(10),
                      [2014.03387_resiko] VARCHAR(15),
                      [2014.03387_benefit] FLOAT,
                      [2014.03387_santunan] FLOAT,
                      [2014.03387_premi] FLOAT,
                      [2014.03388_kelas] VARCHAR(10),
                      [2014.03388_resiko] VARCHAR(15),
                      [2014.03388_benefit] FLOAT,
                      [2014.03388_santunan] FLOAT,
                      [2014.03388_premi] FLOAT,
                      [2014.03389_kelas] VARCHAR(10),
                      [2014.03389_resiko] VARCHAR(15),
                      [2014.03389_benefit] FLOAT,
                      [2014.03389_santunan] FLOAT,
                      [2014.03389_premi] FLOAT,
                      [2014.03390_kelas] VARCHAR(10),
                      [2014.03390_resiko] VARCHAR(15),
                      [2014.03390_benefit] FLOAT,
                      [2014.03390_santunan] FLOAT,
                      [2014.03390_premi] FLOAT,
                      [2014.03391_kelas] VARCHAR(10),
                      [2014.03391_resiko] VARCHAR(15),
                      [2014.03391_benefit] FLOAT,
                      [2014.03391_santunan] FLOAT,
                      [2014.03391_premi] FLOAT,
                      [2014.03392_kelas] VARCHAR(10),
                      [2014.03392_resiko] VARCHAR(15),
                      [2014.03392_benefit] FLOAT,
                      [2014.03392_santunan] FLOAT,
                      [2014.03392_premi] FLOAT,
                      [2014.03393_kelas] VARCHAR(10),
                      [2014.03393_resiko] VARCHAR(15),
                      [2014.03393_benefit] FLOAT,
                      [2014.03393_santunan] FLOAT,
                      [2014.03393_premi] FLOAT,
                      [2014.03394_kelas] VARCHAR(10),
                      [2014.03394_resiko] VARCHAR(15),
                      [2014.03394_benefit] FLOAT,
                      [2014.03394_santunan] FLOAT,
                      [2014.03394_premi] FLOAT
                     )
INSERT INTO ##temp_tabel_20160907
        (No_Polis,
         No_Proposal,
         No_Peserta,
         Nama_Perusahaan,
         Nama,
         Tgl_Lahir,
         Usia,
         Status_Kawin,
         Kelamin,
         Sebagai,
         Nik,
         Cabang,
         Grade,
         Pln,
         Kelas,
         Tgl_Masuk,
         Tgl_Mulai,
         Tgl_Selesai,
         Tgl_Lapse,
         Status,
         Create_By,
         Create_Date,
         Change_By,
         Change_Date,
         Keterangan)
SELECT DISTINCT
    pa.No_Polis,
	  pst.No_Proposal,
	  pst.No_Peserta,
	  prs.Nama_Perusahaan,
	  pst.Nama,
	  pst.Tgl_Lahir,
	  pst.Usia,
	  pst.Status_Kawin,
	  pst.Kelamin,
	  pst.Sebagai,
	  pst.Nik,
	  pst.Cabang,
	  pst.Grade,
	  pst.Pln,
	  pst.Kelas,
	  pst.Tgl_Masuk,
	  pst.Tgl_Mulai,
	  pst.Tgl_Selesai,
	  pst.Tgl_Lapse,
	  pst.Status,
	  pst.Create_By,
	  pst.Create_Date,
	  pst.Change_By,
	  pst.Change_Date,
	  pst.Keterangan
FROM
  dbo.Peserta_Produk_Detail AS ppd INNER JOIN
  dbo.Peserta AS pst ON ppd.No_Proposal = pst.No_Proposal AND ppd.No_Peserta = pst.No_Peserta 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
WHERE
  (ppd.No_Ref = '2015.001838')
ORDER BY
  pst.No_Peserta
  
DECLARE
  @no_peserta VARCHAR(12)

DECLARE list_cursor CURSOR FAST_FORWARD FOR  
SELECT
	No_Peserta
FROM
	##temp_tabel_20160907

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @no_peserta   

WHILE @@FETCH_STATUS = 0
BEGIN

  DECLARE @id_produk VARCHAR(10)
   
    
  DECLARE list_produk CURSOR FAST_FORWARD FOR  
  SELECT
  	Id_Produk
  FROM
  	dbo.Produk_Polis
  WHERE
    No_Proposal = @no_proposal
    
  OPEN list_produk  
  FETCH NEXT FROM list_produk INTO @id_produk   

  WHILE @@FETCH_STATUS = 0
  BEGIN
    
      DECLARE
        @kelas VARCHAR(10),
        @resiko VARCHAR(15),
        @benefit FLOAT,
        @santunan FLOAT,
        @premi FLOAT

      SELECT
      	@kelas=COALESCE(Kelas,''),
  	    @resiko=COALESCE(Resiko,''),
  	    @benefit=COALESCE(Benefit,0),
  	    @santunan=COALESCE(Santunan,0),
  	    @premi=COALESCE(Curr_Premi,0)
      FROM
      	dbo.Peserta_Produk_Detail
      WHERE
        No_Ref = '2015.001838'
        AND No_Peserta = @no_peserta
        AND Id_Produk = @id_produk
      
      IF (@@RowCount > 0)
        EXECUTE ('UPDATE ##temp_tabel_20160907 SET ['+@id_produk+'_kelas] = '''+@kelas+''', ['+@id_produk+'_resiko] = '''+@resiko+''', ['+@id_produk+'_benefit] = '+@benefit+', ['+@id_produk+'_santunan] = '+@santunan+', ['+@id_produk+'_premi] = '+@premi+' WHERE No_Proposal = '''+@no_proposal+''' AND No_Peserta = '''+@no_peserta+'''');

      --UPDATE
      --  @hasil
      --SET
      --  [2013.03519_kelas] = '',
      --  [2013.03519_resiko] = '',
      --  [2013.03519_benefit] = 0,
      --  [2013.03519_santunan] = 0
      --WHERE
      --  No_Proposal = @no_proposal
      --  AND No_Peserta = @no_peserta
      
    FETCH NEXT FROM list_produk INTO @id_produk
  END

  CLOSE list_produk
  DEALLOCATE list_produk


  FETCH NEXT FROM list_cursor INTO @no_peserta
END

CLOSE list_cursor
DEALLOCATE list_cursor


SELECT
	*
FROM
	##temp_tabel_20160907
-------------------------------

--SELECT TOP 20
--  ppd.*,
--  ppd.Usia AS Umr,
--  pst.Nama + ' ' + ISNULL(pst.Nik, '') AS Nama,
--  pst.Nama,
--  pst.Grade,
--  pst.Pln
--FROM
--  dbo.Peserta_Produk_Detail AS ppd INNER JOIN
--  dbo.Peserta AS pst ON ppd.No_Proposal = pst.No_Proposal AND ppd.No_Peserta = pst.No_Peserta INNER JOIN
--  dbo.Polis_Asuransi AS pa ON pst.No_Proposal = pa.No_Proposal
--WHERE
--  (ppd.No_Ref = '2015.001838')
--ORDER BY
--  ppd.No_Peserta

--SELECT *
--FROM
--  dbo.Peserta_Produk_Detail
--WHERE
--  No_Ref = '2015.001838'
----------

--SELECT DISTINCT
--    pa.No_Polis,
--	  pst.No_Proposal,
--	  pst.No_Peserta,
--	  prs.Nama_Perusahaan,
--	  pst.Nama,
--	  pst.Tgl_Lahir,
--	  pst.Usia,
--	  pst.Status_Kawin,
--	  pst.Kelamin,
--	  pst.Sebagai,
--	  pst.Nik,
--	  pst.Cabang,
--	  pst.Grade,
--	  pst.Pln,
--	  pst.Kelas,
--	  pst.Tgl_Masuk,
--	  pst.Tgl_Mulai,
--	  pst.Tgl_Selesai,
--	  pst.Tgl_Lapse,
--	  pst.Status,
--	  pst.Create_By,
--	  pst.Create_Date,
--	  pst.Change_By,
--	  pst.Change_Date,
--	  pst.Keterangan
--FROM
--  dbo.Peserta_Produk_Detail AS ppd INNER JOIN
--  dbo.Peserta AS pst ON ppd.No_Proposal = pst.No_Proposal AND ppd.No_Peserta = pst.No_Peserta 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
--WHERE
--  (ppd.No_Ref = '2015.001838')
--ORDER BY
--  pst.No_Peserta
