SELECT
	No_Ref_Provider,
	SUM(Jml_Transfer) AS Jml_Transfer
FROM
	temp_prib_20160628_10468
GROUP BY
  No_Ref_Provider
ORDER BY
  No_Ref_Provider

SELECT
	No_Ref_Provider,
	SUM(Tot_lunas) AS Tot_lunas
FROM
	temp_prib_20160628_20502
GROUP BY
  No_Ref_Provider
ORDER BY
  No_Ref_Provider



---- yg ada, tapi nilainya beda
--SELECT
--	*
--FROM
--	( SELECT
--	    No_Ref_Provider,
--	    SUM(Jml_Transfer) AS Jml_Transfer
--    FROM
--	    temp_prib_20160628_10468
--    GROUP BY
--      No_Ref_Provider) AS a INNER JOIN
--	( SELECT
--	    No_Ref_Provider,
--	    SUM(Tot_lunas) AS Tot_lunas
--    FROM
--	    temp_prib_20160628_20502
--    GROUP BY
--      No_Ref_Provider
--    ) AS b ON a.No_Ref_Provider = b.No_Ref_Provider
--WHERE
--  CAST(a.Jml_Transfer AS NUMERIC(18,2)) <> CAST(b.Tot_lunas AS NUMERIC(18,2))
--ORDER BY
--  a.No_Ref_Provider


-- yg tidak ada
SELECT
	*
FROM
	( SELECT
	    No_Ref_Provider,
	    SUM(Jml_Transfer) AS Jml_Transfer
    FROM
	    temp_prib_20160628_10468
    GROUP BY
      No_Ref_Provider) AS a LEFT JOIN
	( SELECT
	    No_Ref_Provider,
	    SUM(Tot_lunas) AS Tot_lunas
    FROM
	    temp_prib_20160628_20502
    GROUP BY
      No_Ref_Provider
    ) AS b ON a.No_Ref_Provider = b.No_Ref_Provider
WHERE
  --a.No_Ref_Provider IS NULL
  b.No_Ref_Provider IS NULL
ORDER BY
  a.No_Ref_Provider


--------
SELECT
	*
FROM
	dbo.Note_Terbit_Provider_Detail
WHERE
  No_Ref_Provider = '2016.010981'
  --No_Ref_Provider IN ('2016.010981','2016.011291','2016.011518')
--
SELECT
  polis_asuransi.no_proposal,
  Perusahaan.GT_OG,
  Perusahaan.afiliasi,
  Klaim_Ref_Master.No_Ref,
  Mata_uang.Terbilang AS mata_uang,
  Sub_Kelompok.Nama_Sub AS Produk,
  SUM(Klaim_Ref_Master.Jml_Diajukan) AS jml_diajukan 
FROM
  Klaim_Ref_Master
  INNER JOIN Sub_Kelompok ON CASE WHEN ISNULL(Klaim_Ref_Master.Sub_Kelompok, '') = '0302' THEN '0302'
                                  ELSE '0301'
                             END = Sub_Kelompok.Sub_Kelompok
  INNER JOIN Polis_Asuransi ON Klaim_Ref_Master.no_proposal = Polis_Asuransi.no_proposal
  INNER JOIN Perusahaan ON Polis_Asuransi.No_Prs = Perusahaan.No_Prs
  INNER JOIN Mata_Uang ON Polis_Asuransi.Mata_Uang = Mata_Uang.Kode
WHERE
  Klaim_Ref_Master.No_Ref = '2016.033337'
GROUP BY
  Klaim_Ref_Master.No_Ref,
  Sub_Kelompok.Nama_Sub,
  polis_asuransi.no_proposal,
  Perusahaan.GT_OG,
  Perusahaan.afiliasi,
  Mata_uang.Terbilang
--
SELECT
  Note_Lunas_Provider_Header.Tgl_Bayar,
  Note_Lunas_Provider_Header.Bank_Asal,
  Note_Lunas_Provider_Header.No_Bukti,
  Note_Terbit_Provider_Header.No_Ref_Provider,
  Note_Terbit_Provider_Header.Sub_Total,
  Note_Terbit_Provider_Header.Uang_Muka,
  tot_diskon = ISNULL(Note_Terbit_Provider_Header.Diskon, 0) + ISNULL(Note_Terbit_Provider_Header.Jml_Diskon, 0),
  Note_Terbit_Provider_Header.Total,
  SUM(Note_Lunas_Provider_Header.Jumlah) AS jml_lunas
FROM
  Note_Terbit_Provider_Header
  INNER JOIN Note_Lunas_Provider_Header ON Note_Terbit_Provider_Header.No_Ref_Provider = Note_Lunas_Provider_Header.No_Ref_Provider
WHERE
  Note_Terbit_Provider_Header.No_Ref_Provider = '2016.010981'
GROUP BY
  Note_Terbit_Provider_Header.No_Ref_Provider,
  Note_Terbit_Provider_Header.Sub_Total,
  Note_Terbit_Provider_Header.Uang_Muka,
  Note_Terbit_Provider_Header.Jml_Diskon,
  Note_Terbit_Provider_Header.Diskon,
  Note_Terbit_Provider_Header.Total,
  Note_Lunas_Provider_Header.Tgl_Bayar,
  Note_Lunas_Provider_Header.No_Bukti,
  Note_Lunas_Provider_Header.Bank_Asal
