SELECT
	*
FROM
	temp_prib_polis_nonaktif_peserta_v2
ORDER BY
  [NAMA PERUSAHAAN]

--SELECT
--	[GRADE YANG DITERUSKAN KEPESERTAANNYA],
--	CAST(REPLACE(LEFT([GRADE YANG DITERUSKAN KEPESERTAANNYA],2), '-', '') AS TINYINT) AS grade_lanjut_min,
--	CAST(REPLACE(RIGHT([GRADE YANG DITERUSKAN KEPESERTAANNYA],2), '-', '') AS TINYINT) AS grade_lanjut_max
--FROM
--	temp_prib_polis_nonaktif_peserta_v2

--UPDATE
--  temp_prib_polis_nonaktif_peserta_v2
--SET
--  grade_lanjut_min = CAST(REPLACE(LEFT([GRADE YANG DITERUSKAN KEPESERTAANNYA],2), '-', '') AS TINYINT),
--  grade_lanjut_max = CAST(REPLACE(RIGHT([GRADE YANG DITERUSKAN KEPESERTAANNYA],2), '-', '') AS TINYINT)

------------------------------------------------------------------------------------------------
-- proses
DECLARE
  @nomor TINYINT, 
  @no_polis VARCHAR(20),
  @grade_lanjut_min TINYINT, 
  @grade_lanjut_max TINYINT
  
DECLARE list_cursor CURSOR FAST_FORWARD FOR
SELECT
	nomor,
	no_polis,
	grade_lanjut_min,
	grade_lanjut_max
FROM
	temp_prib_polis_nonaktif_peserta_v2
--WHERE
--  grade_min > 0

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @nomor, @no_polis, @grade_lanjut_min, @grade_lanjut_max

WHILE @@FETCH_STATUS = 0  
BEGIN
  
  --DECLARE @jml_peserta INT = NULL

  --SELECT
  --	@jml_peserta = COUNT(*)
  --FROM
  --	dbo.Peserta AS p INNER JOIN
  --	dbo.Polis_Asuransi AS pa ON p.No_Proposal = pa.No_Proposal
  --WHERE
  --  pa.Status = 'I'
  --  AND pa.No_Polis = @no_polis
  --  AND p.Status = 'I'
  --  AND p.Grade >= @grade_lanjut_min
  --  AND p.Grade <= @grade_lanjut_max
  
  ----
  
  DECLARE
    @no_proposal VARCHAR(9) = NULL,
    --@premi_ajb FLOAT = NULL,
    --@premi_acc FLOAT = NULL,
    --@premi_ri FLOAT = NULL,
    --@premi_rj FLOAT = NULL,
    --@premi_lh FLOAT = NULL,
    --@premi_km FLOAT = NULL
    @premi_all FLOAT = NULL
  
  SELECT
  	@no_proposal = pa.No_Proposal
  FROM
  	dbo.Polis_Asuransi AS pa 
  WHERE
    pa.Status = 'I'
    AND pa.No_Polis = @no_polis
  
  --SELECT
  --	@premi_ajb = SUM(Curr_Premi)
  --FROM
  --	dbo.Peserta_Produk AS pp INNER JOIN
	 -- dbo.Peserta AS p ON pp.No_Peserta = p.No_Peserta AND pp.No_Proposal = p.No_Proposal
  --WHERE
  --  p.No_Proposal = @no_proposal
  --  AND p.Status = 'I'
  --  AND p.Grade >= @grade_lanjut_min
  --  AND p.Grade <= @grade_lanjut_max
  --  AND pp.Sub_Kelompok = '0100' -- AJB
  
  --SELECT
  --	@premi_acc = SUM(Curr_Premi)
  --FROM
  --	dbo.Peserta_Produk AS pp INNER JOIN
	 -- dbo.Peserta AS p ON pp.No_Peserta = p.No_Peserta AND pp.No_Proposal = p.No_Proposal
  --WHERE
  --  p.No_Proposal = @no_proposal
  --  AND p.Status = 'I'
  --  AND p.Grade >= @grade_lanjut_min
  --  AND p.Grade <= @grade_lanjut_max
  --  AND pp.Sub_Kelompok = '0200' -- ACC

  --SELECT
  --	@premi_ri = SUM(Curr_Premi)
  --FROM
  --	dbo.Peserta_Produk AS pp INNER JOIN
	 -- dbo.Peserta AS p ON pp.No_Peserta = p.No_Peserta AND pp.No_Proposal = p.No_Proposal
  --WHERE
  --  p.No_Proposal = @no_proposal
  --  AND p.Status = 'I'
  --  AND p.Grade >= @grade_lanjut_min
  --  AND p.Grade <= @grade_lanjut_max
  --  AND pp.Sub_Kelompok = '0302' -- RI

  --SELECT
  --	@premi_rj = SUM(Curr_Premi)
  --FROM
  --	dbo.Peserta_Produk AS pp INNER JOIN
	 -- dbo.Peserta AS p ON pp.No_Peserta = p.No_Peserta AND pp.No_Proposal = p.No_Proposal
  --WHERE
  --  p.No_Proposal = @no_proposal
  --  AND p.Status = 'I'
  --  AND p.Grade >= @grade_lanjut_min
  --  AND p.Grade <= @grade_lanjut_max
  --  AND pp.Sub_Kelompok = '0301' -- RJ

  --SELECT
  --	@premi_lh = SUM(Curr_Premi)
  --FROM
  --	dbo.Peserta_Produk AS pp INNER JOIN
	 -- dbo.Peserta AS p ON pp.No_Peserta = p.No_Peserta AND pp.No_Proposal = p.No_Proposal
  --WHERE
  --  p.No_Proposal = @no_proposal
  --  AND p.Status = 'I'
  --  AND p.Grade >= @grade_lanjut_min
  --  AND p.Grade <= @grade_lanjut_max
  --  AND pp.Sub_Kelompok = '0304' -- Lahir

  --SELECT
  --	@premi_km = SUM(Curr_Premi)
  --FROM
  --	dbo.Peserta_Produk AS pp INNER JOIN
	 -- dbo.Peserta AS p ON pp.No_Peserta = p.No_Peserta AND pp.No_Proposal = p.No_Proposal
  --WHERE
  --  p.No_Proposal = @no_proposal
  --  AND p.Status = 'I'
  --  AND p.Grade >= @grade_lanjut_min
  --  AND p.Grade <= @grade_lanjut_max
  --  AND pp.Sub_Kelompok = '0305' -- Kaca Mata
  
  SELECT
  	@premi_all = SUM(Curr_Premi)
  FROM
  	dbo.Peserta_Produk AS pp INNER JOIN
	  dbo.Peserta AS p ON pp.No_Peserta = p.No_Peserta AND pp.No_Proposal = p.No_Proposal
  WHERE
    p.No_Proposal = @no_proposal
    AND p.Status = 'I'
    AND p.Grade >= @grade_lanjut_min
    AND p.Grade <= @grade_lanjut_max

    
  UPDATE
    temp_prib_polis_nonaktif_peserta_v2
  SET
    total_premi_lanjut = @premi_all
  WHERE
    nomor = @nomor
    AND no_polis = @no_polis

  FETCH NEXT FROM list_cursor INTO @nomor, @no_polis, @grade_lanjut_min, @grade_lanjut_max
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 

------

SELECT
	*
FROM
	temp_prib_polis_nonaktif_peserta_v2
ORDER BY
  nomor
  
------------------------------------------------------------------------------------------------
-- proses TOTAL SEMUA PREMI
DECLARE
  @nomor TINYINT, 
  @no_polis VARCHAR(20),
  @grade_lanjut_min TINYINT, 
  @grade_lanjut_max TINYINT,
  @no_proposal VARCHAR(9)
  
DECLARE list_cursor CURSOR FAST_FORWARD FOR
SELECT
	nomor,
	no_polis,
	grade_lanjut_min,
	grade_lanjut_max,
	no_proposal
FROM
	temp_prib_polis_nonaktif_peserta_v2
--WHERE
--  grade_min > 0

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @nomor, @no_polis, @grade_lanjut_min, @grade_lanjut_max, @no_proposal

WHILE @@FETCH_STATUS = 0  
BEGIN
  
  --DECLARE @jml_peserta INT = NULL

  --SELECT
  --	@jml_peserta = COUNT(*)
  --FROM
  --	dbo.Peserta AS p INNER JOIN
  --	dbo.Polis_Asuransi AS pa ON p.No_Proposal = pa.No_Proposal
  --WHERE
  --  pa.Status = 'I'
  --  AND pa.No_Polis = @no_polis
  --  AND p.Status = 'I'
  --  AND p.Grade >= @grade_lanjut_min
  --  AND p.Grade <= @grade_lanjut_max
  
  ----
  
  DECLARE
    --@no_proposal VARCHAR(9) = NULL,
    @premi_all FLOAT = NULL
  
  --SELECT
  --	@no_proposal = pa.No_Proposal
  --FROM
  --	dbo.Polis_Asuransi AS pa 
  --WHERE
  --  pa.Status = 'I'
  --  AND pa.No_Polis = @no_polis
  

  
  SELECT
  	@premi_all = SUM(Curr_Premi)
  FROM
  	dbo.Peserta_Produk AS pp INNER JOIN
	  dbo.Peserta AS p ON pp.No_Peserta = p.No_Peserta AND pp.No_Proposal = p.No_Proposal
  WHERE
    p.No_Proposal = @no_proposal
    AND ( p.Status = 'I'
          OR p.Keterangan = 'LAPSE AUTO 2015-07-01')
    --AND p.Grade >= @grade_lanjut_min
    --AND p.Grade <= @grade_lanjut_max

    
  UPDATE
    temp_prib_polis_nonaktif_peserta_v2
  SET
    total_semua_premi = @premi_all
  WHERE
    nomor = @nomor
    AND no_polis = @no_polis

  FETCH NEXT FROM list_cursor INTO @nomor, @no_polis, @grade_lanjut_min, @grade_lanjut_max, @no_proposal
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 

------