USE [GrpInsurance]
GO

EXEC [dbo].[proc_rep_provider_3]
		@pi_tgl_masuk_awal = N'2014-08-01',
		@pi_tgl_masuk_akhir = N'2014-08-10',
		@pi_tgl_masuk_awalYa = 1,
		@pi_tgl_masuk_akhirYa = 1,
		@pi_GTOG = 0

GO

------------
  SELECT
    TOP 10
    diag.Kode,
    diag.Diagnosis1,
    COALESCE(mr.Jml,0) AS Jumlah
  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-08-01'
        AND x1.Tgl_Masuk <= '2014-08-10 23:59:59.999'
        --AND x1.Status = 'I'  -- disantun
        AND x3.GT_OG = 'OG'
        AND (x3.GT_OG='GT' OR @pi_GTOG<>1) 
        AND (x3.GT_OG='OG' OR @pi_GTOG<>2)
      GROUP BY
        x1.Kode_Diagnosa
    ) AS mr ON diag.Kode=mr.Kode_Diagnosa
  WHERE
    (COALESCE(mr.Jml,0)<>0)
  GROUP BY
    diag.Kode,
    diag.Diagnosis1,
    mr.Jml
  ORDER BY
    mr.Jml DESC