DECLARE @hasil TABLE ( 
                  no_polis VARCHAR(20),
                  no_proposal VARCHAR(9),
                  nama_perusahaan VARCHAR(50),
                  jml_pst_0 INT,
                  premi_0 FLOAT,
                  jml_pst_1_9 INT,
                  premi_1_9 FLOAT,
                  jml_pst_10_22 INT,
                  premi_10_22 FLOAT,
                  premi_total FLOAT
               )

DECLARE
  @no_polis VARCHAR(20),
  @no_proposal VARCHAR(9),
  @nama_perusahaan VARCHAR(50)
  
DECLARE list_cursor CURSOR FAST_FORWARD FOR
SELECT
	no_polis,
	no_proposal,
	[NAMA PERUSAHAAN]
FROM
	temp_prib_polis_nonaktif_peserta_v2
WHERE
  grade_max=0 -- prs yg peserta tidak dikeluarkan

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @no_polis, @no_proposal, @nama_perusahaan

WHILE @@FETCH_STATUS = 0  
BEGIN
  DECLARE
    @premi_0 FLOAT,
    @premi_1_9 FLOAT,
    @premi_10_22 FLOAT,
    @jml_pst_0 INT,
    @jml_pst_1_9 INT,
    @jml_pst_10_22 INT

  SELECT
	  @premi_0 = SUM(pp.Premi * 1.0 * DATEDIFF(DAY, pp.Tgl_Mulai, p.Tgl_Selesai + 1)/DATEDIFF(DAY, pa.Tgl_Mulai, pa.Tgl_Selesai + 1))
  FROM
	  dbo.Polis_Asuransi AS pa INNER JOIN
	  dbo.Peserta AS p ON pa.No_Proposal = p.No_Proposal INNER JOIN
	  dbo.Peserta_Produk AS pp ON p.No_Proposal = pp.No_Proposal AND p.No_Peserta = pp.No_Peserta
  WHERE
    pa.No_Proposal = @no_proposal
    AND COALESCE(p.Grade, 0) = 0
    AND p.Status <> 'C'

  SELECT
	  @jml_pst_0 = COUNT(p.No_Peserta)
  FROM
	  dbo.Polis_Asuransi AS pa INNER JOIN
	  dbo.Peserta AS p ON pa.No_Proposal = p.No_Proposal
  WHERE
    pa.No_Proposal = @no_proposal
    AND COALESCE(p.Grade, 0) = 0
    AND p.Status <> 'C'

  SELECT
	  @premi_1_9 = SUM(pp.Premi * 1.0 * DATEDIFF(DAY, pp.Tgl_Mulai, p.Tgl_Selesai + 1)/DATEDIFF(DAY, pa.Tgl_Mulai, pa.Tgl_Selesai + 1))
  FROM
	  dbo.Polis_Asuransi AS pa INNER JOIN
	  dbo.Peserta AS p ON pa.No_Proposal = p.No_Proposal INNER JOIN
	  dbo.Peserta_Produk AS pp ON p.No_Proposal = pp.No_Proposal AND p.No_Peserta = pp.No_Peserta
  WHERE
    pa.No_Proposal = @no_proposal
    AND p.Grade >= 1
    AND p.Grade <= 9
    AND p.Status <> 'C'

  SELECT
	  @jml_pst_1_9 = COUNT(p.No_Peserta)
  FROM
	  dbo.Polis_Asuransi AS pa INNER JOIN
	  dbo.Peserta AS p ON pa.No_Proposal = p.No_Proposal
  WHERE
    pa.No_Proposal = @no_proposal
    AND p.Grade >= 1
    AND p.Grade <= 9
    AND p.Status <> 'C'

  SELECT
	  @premi_10_22 = SUM(pp.Premi * 1.0 * DATEDIFF(DAY, pp.Tgl_Mulai, p.Tgl_Selesai + 1)/DATEDIFF(DAY, pa.Tgl_Mulai, pa.Tgl_Selesai + 1))
  FROM
	  dbo.Polis_Asuransi AS pa INNER JOIN
	  dbo.Peserta AS p ON pa.No_Proposal = p.No_Proposal INNER JOIN
	  dbo.Peserta_Produk AS pp ON p.No_Proposal = pp.No_Proposal AND p.No_Peserta = pp.No_Peserta
  WHERE
    pa.No_Proposal = @no_proposal
    AND p.Grade >= 10
    AND p.Grade <= 22
    AND p.Status <> 'C'

  SELECT
	  @jml_pst_10_22 = COUNT(p.No_Peserta)
  FROM
	  dbo.Polis_Asuransi AS pa INNER JOIN
	  dbo.Peserta AS p ON pa.No_Proposal = p.No_Proposal
  WHERE
    pa.No_Proposal = @no_proposal
    AND p.Grade >= 10
    AND p.Grade <= 22
    AND p.Status <> 'C'
  
  INSERT @hasil
          (no_polis,
           no_proposal,
           nama_perusahaan,
           jml_pst_0,
           premi_0,
           jml_pst_1_9,
           premi_1_9,
           jml_pst_10_22,
           premi_10_22,
           premi_total)
  VALUES
          (@no_polis, -- no_polis - varchar(20)
           @no_proposal, -- no_proposal - varchar(9)
           @nama_perusahaan, -- nama_perusahaan - varchar(50)
           @jml_pst_0, -- jml_pst_0
           COALESCE(@premi_0, 0), -- premi_0
           @jml_pst_1_9,
           COALESCE(@premi_1_9, 0), -- premi_1_9 - float
           @jml_pst_10_22,
           COALESCE(@premi_10_22, 0 ), -- premi_10_22 - float
           COALESCE(@premi_0, 0) + (COALESCE(@premi_1_9, 0) + COALESCE(@premi_10_22, 0 ))  -- premi_total - float
           )
    
  FETCH NEXT FROM list_cursor INTO @no_polis, @no_proposal, @nama_perusahaan
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 


SELECT
	*
FROM
	@hasil
ORDER BY
  nama_perusahaan