SELECT
  *
FROM
  dbo.Peserta
WHERE
  No_Proposal = '2014.0795'
  --AND Id_Produk = '2014.03143'  --2014.03150
  AND No_Peserta = '1992003031-0'
  
SELECT
  *
FROM
  dbo.Peserta_Produk
WHERE
  No_Proposal = '2014.0795'
  --AND Id_Produk = '2014.03143'  --2014.03150
  AND No_Peserta = '1992003031-0'

SELECT
  No_Peserta,
  SUM(Jml_Dibayar)
FROM
  dbo.Klaim_Peserta
WHERE
  No_Proposal = '2014.0795'
  AND Id_Produk = '2014.03150'
GROUP BY
  No_Peserta
  

SELECT
    Klaim_Peserta.No_Peserta,
    SUM(dbo.Klaim_Detail.Jml_Dibayar) AS Jml_Dibayar
FROM
    dbo.Klaim_Peserta
    INNER JOIN dbo.Klaim_Detail ON dbo.Klaim_Peserta.No_Klaim = dbo.Klaim_Detail.No_Klaim
    INNER JOIN dbo.Item_Santunan_Polis ON dbo.Klaim_Peserta.Id_Produk = dbo.Item_Santunan_Polis.Id_Produk
                                          AND dbo.Klaim_Detail.Kode_Santunan = dbo.Item_Santunan_Polis.Kode_Santunan
WHERE
    (dbo.Klaim_Peserta.No_Proposal = '2014.0795')
    AND (dbo.Klaim_Peserta.No_Santunan = '1992002877-0')
    AND (dbo.Klaim_Peserta.Id_Produk = '2014.03143')
    AND (ISNULL(dbo.Item_Santunan_Polis.Exclude, 0) = 0) --AND (dbo.Klaim_Peserta.No_Klaim<>:No_Klaim)
    AND (ISNULL(dbo.Item_Santunan_Polis.No_Limit, 0) = 0)
    AND (dbo.Klaim_Peserta.Status <> 'C')
GROUP BY
  Klaim_Peserta.No_Peserta

  
SELECT
  pst.No_Peserta,
  pst.Nama,
  klm.Jml_Dibayar,
  prd.Santunan,
  prd.Santunan - klm.Jml_Dibayar
FROM
  dbo.Peserta AS pst INNER JOIN
  dbo.Peserta_Produk AS prd ON pst.No_Proposal = prd.No_Proposal AND pst.No_Peserta = prd.No_Peserta INNER JOIN
  ( SELECT
      No_Peserta,
      SUM(Jml_Dibayar) AS Jml_Dibayar
    FROM
      dbo.Klaim_Peserta
    WHERE
      No_Proposal = '2014.0795'
      AND Id_Produk = '2014.03143'
      AND Status = 'I'
    GROUP BY
      No_Peserta) AS klm ON prd.No_Peserta = klm.No_Peserta
WHERE
  pst.No_Proposal = '2014.0795'
  AND prd.Id_Produk = '2014.03143'
  AND klm.No_Peserta = '1992002877-0'
  --AND klm.Jml_Dibayar >= prd.Santunan

SELECT
  pst.No_Peserta,
  pst.Nama,
  klm.Jml_Dibayar,
  prd.Santunan,
  prd.Santunan - klm.Jml_Dibayar AS Sisa_Santunan
FROM
  dbo.Peserta AS pst INNER JOIN
  dbo.Peserta_Produk AS prd ON pst.No_Proposal = prd.No_Proposal AND pst.No_Peserta = prd.No_Peserta INNER JOIN
  ( SELECT
      Klaim_Peserta.No_Santunan,
      Klaim_Peserta.No_Proposal,
      Klaim_Peserta.Id_Produk,
      SUM(dbo.Klaim_Detail.Jml_Dibayar) AS Jml_Dibayar
    FROM
      Klaim_Peserta INNER JOIN 
      Klaim_Detail ON Klaim_Peserta.No_Klaim = Klaim_Detail.No_Klaim INNER JOIN 
      Item_Santunan_Polis ON Klaim_Peserta.Id_Produk = Item_Santunan_Polis.Id_Produk AND Klaim_Detail.Kode_Santunan = Item_Santunan_Polis.Kode_Santunan
    WHERE
      --Klaim_Peserta.No_Proposal = '2014.0795'
      --AND Klaim_Peserta.No_Santunan = '1992002877-0'
      --AND Klaim_Peserta.Id_Produk = '2014.03143'
      --AND 
      ISNULL(Item_Santunan_Polis.Exclude, 0) = 0
      AND ISNULL(Item_Santunan_Polis.No_Limit, 0) = 0
      AND Klaim_Peserta.Status <> 'C'
    GROUP BY
      Klaim_Peserta.No_Santunan,
      Klaim_Peserta.No_Proposal,
      Klaim_Peserta.Id_Produk) AS klm ON pst.No_Peserta = klm.No_Santunan AND prd.No_Proposal = klm.No_Proposal AND prd.Id_Produk = klm.Id_Produk
WHERE
  pst.No_Proposal = '2014.0795'
  AND prd.Id_Produk = '2014.03150'
  --AND pst.No_Peserta = '1992002877-0'
ORDER BY
  pst.No_Peserta