--SELECT
--  *
--FROM
--  TmpInsurance.dbo.Rep10358prib


---- list nama kolom
--DECLARE @satu VARCHAR(2000) = ''
--SELECT @satu=@satu + 'TmpInsurance.dbo.Rep10358prib.' + name + ', '
--FROM TmpInsurance.sys.columns WHERE object_id = OBJECT_ID('TmpInsurance.dbo.Rep10358prib') AND name<>'No_Proposal'
----SELECT LEFT(@satu,LEN(@satu)-1)

    
--DECLARE @QueryAkhir VARCHAR(4000)
--SET @QueryAkhir = 'SELECT TOP 100 PERCENT ' + LEFT(@satu,LEN(@satu)-1) + ', dbo.Cabang.Nama AS Cabang '
--SET @QueryAkhir = @QueryAkhir + 'FROM TmpInsurance.dbo.Rep10358prib
--    INNER JOIN dbo.Peserta ON TmpInsurance.dbo.Rep10358prib.No_Peserta = dbo.Peserta.No_Peserta
--                              AND TmpInsurance.dbo.Rep10358prib.No_Proposal = dbo.Peserta.No_Proposal
--    INNER JOIN dbo.Sebagai ON dbo.Peserta.Sebagai = dbo.Sebagai.IDI
--    LEFT OUTER JOIN dbo.Kartu_Peserta ON TmpInsurance.dbo.Rep10358prib.No_Proposal = dbo.Kartu_Peserta.No_Proposal
--                                         AND dbo.Peserta.No_Peserta = dbo.Kartu_Peserta.No_Peserta
--    LEFT OUTER JOIN dbo.Cabang ON dbo.Peserta.No_Prs = dbo.Cabang.No_Prs
--                                  AND dbo.Peserta.Cabang = dbo.Cabang.Kode
--WHERE
--  dbo.Cabang.Kode LIKE ''KP%''
--ORDER BY
--    TmpInsurance.dbo.Rep10358prib.No_Peserta'

----EXEC(@QueryAkhir)
--PRINT @QueryAkhir

SELECT
	*
FROM
	(
    SELECT
      TmpInsurance.dbo.Rep10358prib.No_Peserta,
      TmpInsurance.dbo.Rep10358prib.Nama,
      TmpInsurance.dbo.Rep10358prib.Nik,
      TmpInsurance.dbo.Rep10358prib.Tgl_Lahir,
      TmpInsurance.dbo.Rep10358prib.Sex,
      TmpInsurance.dbo.Rep10358prib.Umur,
      TmpInsurance.dbo.Rep10358prib.Status,
      TmpInsurance.dbo.Rep10358prib.Sebagai,
      TmpInsurance.dbo.Rep10358prib.Grd,
      TmpInsurance.dbo.Rep10358prib.Pln,
      TmpInsurance.dbo.Rep10358prib.Tgl_Mulai,
      TmpInsurance.dbo.Rep10358prib.AJB_Kelas,
      TmpInsurance.dbo.Rep10358prib.AJB_Premi,
      TmpInsurance.dbo.Rep10358prib.AJB_Up,
      TmpInsurance.dbo.Rep10358prib.ACC_Kelas,
      TmpInsurance.dbo.Rep10358prib.ACC_Premi,
      TmpInsurance.dbo.Rep10358prib.ACC_Up,
      TmpInsurance.dbo.Rep10358prib.RI_Kelas,
      TmpInsurance.dbo.Rep10358prib.RI_Premi,
      TmpInsurance.dbo.Rep10358prib.LH_Kelas,
      TmpInsurance.dbo.Rep10358prib.LH_Premi,
      TmpInsurance.dbo.Rep10358prib.RJ_Kelas,
      TmpInsurance.dbo.Rep10358prib.RJ_Premi,
      TmpInsurance.dbo.Rep10358prib.KM_Kelas,
      TmpInsurance.dbo.Rep10358prib.KM_Premi,
      TmpInsurance.dbo.Rep10358prib.Total,
      dbo.Cabang.Nama AS Cabang
    FROM
      TmpInsurance.dbo.Rep10358prib
      INNER JOIN dbo.Peserta ON TmpInsurance.dbo.Rep10358prib.No_Peserta = dbo.Peserta.No_Peserta
                                AND TmpInsurance.dbo.Rep10358prib.No_Proposal = dbo.Peserta.No_Proposal
      INNER JOIN dbo.Sebagai ON dbo.Peserta.Sebagai = dbo.Sebagai.IDI
      LEFT OUTER JOIN dbo.Kartu_Peserta ON TmpInsurance.dbo.Rep10358prib.No_Proposal = dbo.Kartu_Peserta.No_Proposal
                                           AND dbo.Peserta.No_Peserta = dbo.Kartu_Peserta.No_Peserta
      LEFT OUTER JOIN dbo.Cabang ON dbo.Peserta.No_Prs = dbo.Cabang.No_Prs
                                    AND dbo.Peserta.Cabang = dbo.Cabang.Kode
    WHERE
      dbo.Cabang.Kode NOT LIKE 'KP%'
  ) AS datalengkap LEFT JOIN
  (
    SELECT
      LEFT(No_Peserta,10) AS No_Peserta, 
	    SUM(Total) AS Total
    FROM
      TmpInsurance.dbo.Rep10358prib
    GROUP BY
      LEFT(No_Peserta,10)
  ) AS datatotal ON datalengkap.No_Peserta = datatotal.No_Peserta+'-0'
ORDER BY
  datalengkap.No_Peserta


--SELECT
--  LEFT(No_Peserta,10) AS No_Peserta, 
--	SUM(Total) AS Total
--FROM
--  TmpInsurance.dbo.Rep10358prib
--GROUP BY
--  LEFT(No_Peserta,10)