SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- by Pribadi Pamungkas. 2016-04-07
CREATE PROCEDURE dbo.proc_Rep90160_pasien_map
	@pi_tglmasuk1 DATETIME,
	@pi_tglmasuk2 DATETIME,
	@pi_tglmasuk1Ya BIT,
	@pi_tglmasuk2Ya BIT,
	@pi_nopolis VARCHAR(200)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  SELECT
	  mr.No_Pasien,
	  pa.No_Polis,
	  prs.Nama_Perusahaan,
	  mr.No_Peserta,
	  pst.Nama AS Nama_Pasien,
	  kary.No_Peserta AS No_Peserta_Karyawan,
	  kary.Nama AS Nama_Karyawan,
	  mr.Tgl_Masuk,
	  rs.Nama AS Nama_RS,
	  mr.Tgl_Keluar,
	  biaya.Biaya
  FROM
    dbo.Medical_Record AS mr INNER JOIN
	  dbo.Polis_Asuransi AS pa ON mr.No_Proposal = pa.No_Proposal INNER JOIN
	  dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs INNER JOIN
	  dbo.Group_Perusahaan AS gp ON prs.Group_Perusahaan = gp.IDI INNER JOIN
	  dbo.Peserta AS pst ON mr.No_Peserta = pst.No_Peserta
	                        AND mr.No_Proposal = pst.No_Proposal LEFT JOIN
	  dbo.Provider AS rs ON mr.Kode_Provider = rs.IDI LEFT JOIN
	  (
      SELECT
	      pfu.No_Pasien,
	      pfu.Biaya,
	      pfu.Tgl_Data
      FROM
	      dbo.PasienFollowUp AS pfu INNER JOIN
	      (
          SELECT
	          No_Pasien,
	          MAX(Tgl_Data) AS Tgl_Data
          FROM
	          dbo.PasienFollowUp
          GROUP BY
            No_Pasien
        ) AS fua ON pfu.No_Pasien = fua.No_Pasien
                    AND pfu.Tgl_Data = fua.Tgl_Data
	  ) AS biaya ON mr.No_Pasien = biaya.No_Pasien INNER JOIN
	  dbo.Peserta AS kary ON LEFT(pst.No_Peserta,11)+'0' = kary.No_Peserta
	                         AND pst.No_Proposal = kary.No_Proposal
  WHERE
    prs.Group_Perusahaan = 23017  -- grup MAP
    AND (mr.Tgl_Masuk >= @pi_tglmasuk1 OR @pi_tglmasuk1Ya = 0)
    AND (mr.Tgl_Masuk <= @pi_tglmasuk2 OR @pi_tglmasuk2Ya = 0)
    AND (CHARINDEX(pa.No_Polis, @pi_nopolis) <> 0 OR ISNULL(@pi_nopolis, '') = '')
  ORDER BY
    prs.Nama_Perusahaan,
    pst.Nama,
    mr.Tgl_Masuk

END
GO
