DECLARE @list_polis TABLE ( no_polis VARCHAR(11))
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0194.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0191.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0216.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0028.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0036.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0030.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0034.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0089.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0075.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0053.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0029.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0080.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0065.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0061.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0052.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0060.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0031.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0033.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0193.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0038.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0062.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0063.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0192.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0032.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0035.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0146.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0064.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0085.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0051.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0092.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0027.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0084.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0059.1')
INSERT INTO @list_polis ( no_polis ) VALUES ('2010.0111.1')

DECLARE @hasil TABLE ( 
                  no_polis VARCHAR(11),
                  no_proposal VARCHAR(9),
                  nama_prs VARCHAR(50),
                  no_ref VARCHAR(11),
                  jml_ri FLOAT,
                  jml_rj FLOAT,
                  tgl_buat DATETIME,
                  jml_pst INTEGER
               )

DECLARE
  @no_polis VARCHAR(11),
  @no_proposal VARCHAR(9),
  @nama_prs VARCHAR(50),
  @no_ref VARCHAR(11),
  @tgl_buat DATETIME,
  @jml_pst INTEGER,
  @no_proposal_loop VARCHAR(9) = ''
  

DECLARE list_cursor CURSOR FOR  
SELECT
	pa.No_Polis,
	pa.No_Proposal,
	p.Nama_Perusahaan,
	ntm.No_Ref,
	ntm.Tgl_Buat,
	ntm.Jml_Peserta
FROM
	@list_polis AS lp INNER JOIN
	dbo.Polis_Asuransi AS pa ON lp.no_polis = pa.No_Polis INNER JOIN
	dbo.Perusahaan AS p ON pa.No_Prs = p.No_Prs INNER JOIN
	dbo.Note_Terbit_Master AS ntm ON pa.No_Proposal = ntm.No_Proposal
WHERE
  ntm.Jenis IN (1,2,3,4)
  AND ntm.Status = 'I'
ORDER BY
  p.Nama_Perusahaan,
  ntm.No_Ref

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @no_polis, @no_proposal, @nama_prs, @no_ref, @tgl_buat, @jml_pst

WHILE @@FETCH_STATUS = 0  
BEGIN 

  DECLARE
    @jumlah_rj FLOAT,
    @jumlah_ri FLOAT
    
  SELECT
  	@jumlah_rj = Jumlah
  FROM
  	dbo.Note_Terbit_Detail
  WHERE
    No_Ref = @no_ref
    AND Sub_Kelompok = '0301' -- RJ
    
  SELECT
  	@jumlah_ri = Jumlah
  FROM
  	dbo.Note_Terbit_Detail
  WHERE
    No_Ref = @no_ref
    AND Sub_Kelompok = '0302' -- RI
  
  IF @no_proposal_loop <> @no_proposal
    BEGIN
      INSERT INTO @hasil
              (no_polis,
               no_proposal,
               nama_prs,
               no_ref,
               jml_ri,
               jml_rj,
               tgl_buat,
               jml_pst)
      VALUES
              (@no_polis, -- no_polis - varchar(11)
               @no_proposal, -- no_proposal - varchar(9)
               @nama_prs, -- nama_prs - varchar(50)
               @no_ref, -- no_ref - varchar(11)
               @jumlah_ri, -- jml_ri - float
               @jumlah_rj, -- jml_rj - float
               @tgl_buat, -- tgl_buat - datetime
               @jml_pst  -- jml_pst - integer
               )
    END
  ELSE
    BEGIN
      INSERT INTO @hasil
              (no_polis,
               no_proposal,
               nama_prs,
               no_ref,
               jml_ri,
               jml_rj,
               tgl_buat,
               jml_pst)
      VALUES
              ('', -- no_polis - varchar(11)
               '', -- no_proposal - varchar(9)
               '', -- nama_prs - varchar(50)
               @no_ref, -- no_ref - varchar(11)
               @jumlah_ri, -- jml_ri - float
               @jumlah_rj, -- jml_rj - float
               @tgl_buat, -- tgl_buat - datetime
               @jml_pst  -- jml_pst - integer
               )
    END
    
  SET @no_proposal_loop = @no_proposal
  	
  FETCH NEXT FROM list_cursor INTO @no_polis, @no_proposal, @nama_prs, @no_ref, @tgl_buat, @jml_pst
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 


SELECT
	*
FROM
	@hasil