SELECT
  rs.IDI,
  rs.Nama,
  COUNT(mr.No_Pasien) AS Jamin
FROM
  dbo.Medical_Record AS mr INNER JOIN
  dbo.Provider AS rs ON mr.Kode_Provider=rs.IDI
WHERE
  Tgl_Masuk>='2014-09-01'
  AND Tgl_Masuk<='2014-09-16'
  AND mr.Status='I'
GROUP BY
  rs.IDI,
  rs.Nama
ORDER BY
  Jamin DESC
  
---------------

SELECT
  rs.IDI,
  rs.Nama,
  COUNT(mr.No_Pasien) AS Jamin,
  COALESCE(mr2.Jml,0) AS Tolak
FROM
  dbo.Medical_Record AS mr INNER JOIN
  dbo.Provider AS rs ON mr.Kode_Provider=rs.IDI LEFT JOIN
  ( 
    SELECT 
      Kode_Provider,
      COUNT(*) AS Jml
    FROM
      dbo.Medical_Record
    WHERE
      Tgl_Masuk>='2014-09-01'
      AND Tgl_Masuk<='2014-09-16'
      AND Status='C'  -- ditolak
    GROUP BY
      Kode_Provider
  ) AS mr2 ON rs.IDI=mr2.Kode_Provider
WHERE
  Tgl_Masuk>='2014-09-01'
  AND Tgl_Masuk<='2014-09-16'
  AND mr.Status='I'
GROUP BY
  rs.IDI,
  rs.Nama,
  mr2.Jml
ORDER BY
  Jamin DESC

----------------------------
-- 1
SELECT
  rs.IDI,
  rs.Nama,
  COALESCE(mr1.Jml,0) AS Jamin,
  COALESCE(mr2.Jml,0) AS Tolak
FROM
  dbo.Provider AS rs LEFT JOIN
  ( 
    SELECT 
      x1.Kode_Provider,
      COUNT(*) AS Jml
    FROM
      dbo.Medical_Record AS x1 INNER JOIN
      dbo.Polis_Asuransi AS x2 ON x1.No_Proposal=x2.No_Proposal INNER JOIN
      dbo.Perusahaan AS x3 ON x2.No_Prs = x3.No_Prs
    WHERE
      x1.Tgl_Masuk>='2014-09-01'
      AND x1.Tgl_Masuk<='2014-09-16'
      AND x1.Status='I'  -- disantun
      AND x3.GT_OG='OG'
    GROUP BY
      x1.Kode_Provider
  ) AS mr1 ON rs.IDI=mr1.Kode_Provider LEFT JOIN
  ( 
    SELECT 
      x1.Kode_Provider,
      COUNT(*) AS Jml
    FROM
      dbo.Medical_Record AS x1 INNER JOIN
      dbo.Polis_Asuransi AS x2 ON x1.No_Proposal=x2.No_Proposal INNER JOIN
      dbo.Perusahaan AS x3 ON x2.No_Prs = x3.No_Prs
    WHERE
      x1.Tgl_Masuk>='2014-09-01'
      AND x1.Tgl_Masuk<='2014-09-16'
      AND x1.Status='C'  -- ditolak
      AND x3.GT_OG='OG'
    GROUP BY
      x1.Kode_Provider
  ) AS mr2 ON rs.IDI=mr2.Kode_Provider
WHERE
  (COALESCE(mr1.Jml,0)<>0 OR COALESCE(mr2.Jml,0)<>0)
GROUP BY
  rs.IDI,
  rs.Nama,
  mr1.Jml,
  mr2.Jml
ORDER BY
  mr1.Jml DESC,
  mr2.Jml DESC

----------------------------
SELECT * FROM dbo.Diagnosa
----------------------------
-- 3
SELECT
  rs.IDI,
  rs.Nama,
  COALESCE(mr1.Jml,0) AS Jamin,
  COALESCE(mr2.Jml,0) AS Tolak
FROM
  dbo.Diagnosa AS diag LEFT JOIN
  ( 
    SELECT 
      x1.Kode_Diagnosa,
      COUNT(*) AS Jml
    FROM
      dbo.Medical_Record AS x1 INNER JOIN
      dbo.Polis_Asuransi AS x2 ON x1.No_Proposal=x2.No_Proposal INNER JOIN
      dbo.Perusahaan AS x3 ON x2.No_Prs = x3.No_Prs
    WHERE
      x1.Tgl_Masuk>='2014-09-01'
      AND x1.Tgl_Masuk<='2014-09-16'
      AND x1.Status='I'  -- disantun
      AND x3.GT_OG='OG'
    GROUP BY
      x1.Kode_Diagnosa
  ) AS mr1 ON diag.Kode=mr1.Kode_Diagnosa LEFT JOIN
  ( 
    SELECT 
      x1.Kode_Diagnosa,
      COUNT(*) AS Jml
    FROM
      dbo.Medical_Record AS x1 INNER JOIN
      dbo.Polis_Asuransi AS x2 ON x1.No_Proposal=x2.No_Proposal INNER JOIN
      dbo.Perusahaan AS x3 ON x2.No_Prs = x3.No_Prs
    WHERE
      x1.Tgl_Masuk>='2014-09-01'
      AND x1.Tgl_Masuk<='2014-09-16'
      AND x1.Status='C'  -- ditolak
      AND x3.GT_OG='OG'
    GROUP BY
      x1.Kode_Provider
  ) AS mr2 ON rs.IDI=mr2.Kode_Provider
WHERE
  (COALESCE(mr1.Jml,0)<>0 OR COALESCE(mr2.Jml,0)<>0)
GROUP BY
  rs.IDI,
  rs.Nama,
  mr1.Jml,
  mr2.Jml
ORDER BY
  mr1.Jml DESC,
  mr2.Jml DESC

