IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[AKTARIM_YATISLISTESI_PUSULA]') AND OBJECTPROPERTY(id,N'IsVIew') = 1)
BEGIN
    INSERT INTO SCRIPT_BACKUPS (SCRIPTNAME,SCRIPT,SCRIPTDATE) SELECT 'AKTARIM_YATISLISTESI_PUSULA' SCRIPTNAME,OBJECT_DEFINITION(OBJECT_ID('AKTARIM_YATISLISTESI_PUSULA')) AS SCRIPT,GETDATE() AS SCRIPTDATE
    DROP VIEW AKTARIM_YATISLISTESI_PUSULA
END
go


CREATE VIEW  AKTARIM_YATISLISTESI_PUSULA
AS

SELECT TY.Id YatisNo,TY.ProtokolId, HH.Adi + ' ' + HH.Soyadi 'Hasta',HP.BolumId, OB.Adi 'Bölüm', HP.DoktorId,IKP.Adi + ' ' + IKP.Soyadi 'Doktor',  
      'Yatış Tipi' = CASE TY.YatisTipiId  WHEN '1' THEN 'Medikal Tedavi'  WHEN '2' THEN 'Ameliyat'  WHEN '3' THEN 'Yoğun Bakım'  END,  
	  TY.ServisKabulTarihi 'Servis Kabul Tarihi',  
	  TY.TaburcuTarihi 'Taburcu Tarihi',DateDiff(Day,TY.ServisKabulTarihi,ISNULL(TY.TaburcuTarihi,GetDate()))  YatilanGunSayisi,
	  (SELECT Adi + ' ' + Soyadi FROM MetCer.IK.Personel IKP WHERE IKP.Id = TY.HastaKabulEdenPersonelId) 'Hasta Kabul Eden Personel', 
	   (SELECT Adi + ' ' + Soyadi FROM MetCer.IK.Personel IKP WHERE IKP.Id = TY.TaburcuEdenPersonelId) 'Taburcu Eden Personel'
	   ,	   OBB2.Adi AS Kat, OBB.Adi AS Oda, OY.Adi AS Yatak  

FROM MetCer.Tedavi.Yatis TY  
INNER JOIN MetCer.Hasta.Protokol HP ON HP.Id = TY.ProtokolId  
INNER JOIN MetCer.Hasta.Hasta HH ON HH.Id = HP.HastaId  
INNER JOIN MetCer.IK.Personel IKP ON IKP.Id = HP.DoktorId  
INNER JOIN MetCer.Ortak.Bolum OB ON OB.Id = HP.BolumId  
INNER JOIN MetCer.Ortak.Brans oba (nolock) ON OBA.Id=OB.BransId


  left JOIN MetCer.Ortak.Yatak OY ON OY.Id = TY.YatakId  
  left JOIN MetCer.Ortak.BinaBirim OBB ON OBB.Id = OY.BinaBirimId  
  left JOIN MetCer.Ortak.BinaBirim OBB2 ON OBB2.Id = OBB.ParentId  


WHERE HP.State <> 0  AND HH.State <> 0 


--select * from MetCer.Tedavi.Yatis TY  WHERE ID=1017