--SELECT
--	COALESCE(kp.AdMedika_Ya,0),
--	pa.AdMedika_Ya
	
--FROM
--	dbo.Klaim_Peserta AS kp INNER JOIN
--	dbo.Polis_Asuransi AS pa ON kp.No_Proposal = pa.No_Proposal INNER JOIN
--	dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs
--WHERE
--  kp.Create_Date >= '2016-03-16'
--  AND kp.Create_Date <= '2016-03-29'
--  AND COALESCE(kp.AdMedika_Ya,0) <> pa.AdMedika_Ya


SELECT
	prs.Nama_Perusahaan,
	sk.Nama_Sub,
	COUNT(kp.No_Klaim) AS Jml_Klaim,
	--COALESCE(kp.AdMedika_Ya,0),
	CASE kp.AdMedika_Ya
	  WHEN 1 THEN 'AdMedika'
	  ELSE 'Non AdMedika'
	END AS [System],
	gun.Nama AS Nama_User,
	CAST(kp.Create_Date AS DATE) AS Create_Date
FROM
	dbo.Klaim_Peserta AS kp INNER JOIN
	dbo.Polis_Asuransi AS pa ON kp.No_Proposal = pa.No_Proposal INNER JOIN
	dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs INNER JOIN
	dbo.Sub_Kelompok AS sk ON kp.Sub_Kelompok = sk.Sub_Kelompok INNER JOIN
	dbo.gUser_Name AS gun ON kp.Create_By = gun.Id_User
WHERE
  kp.Create_Date >= '2016-03-01'
  AND kp.Create_Date <= '2016-03-30'
  AND kp.Status = 'I'
  AND kp.Jenis_Pembayaran = 'P'
GROUP BY
	prs.Nama_Perusahaan,
	sk.Nama_Sub,
	kp.AdMedika_Ya,
	gun.Nama,
	CAST(kp.Create_Date AS DATE)
ORDER BY
  gun.Nama,
  CAST(kp.Create_Date AS DATE),
  prs.Nama_Perusahaan