
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FNMED_TABLETSIRAMATIKBILGILERI]') AND xtype in (N'FN', N'IF', N'TF'))
	DROP FUNCTION [dbo].[FNMED_TABLETSIRAMATIKBILGILERI]
GO
CREATE FUNCTION [dbo].[FNMED_TABLETSIRAMATIKBILGILERI](@SUBEID INT, @TABLETIP NVARCHAR(15)) RETURNS @TABLESIRAMATIK TABLE (  
 HKIMLIKID int,  
 HADI NVARCHAR(100) NULL,  
 HSOYADI NVARCHAR(100) NULL,  
 DOKTOR NVARCHAR(100) NULL,  
 HYASI int,  
 ENGELDURUM bit,  
 TUR nvarchar(100) NULL  
) AS BEGIN  
INSERT INTO  
 @TABLESIRAMATIK (  
  HKIMLIKID,  
  HADI,  
  HSOYADI,  
  DOKTOR,  
  HYASI,  
  ENGELDURUM,  
  TUR  
 )  
SELECT  
 TOP 10 VWMUAYENEISL.HKIMLIKID,  
 HADI,  
 HSOYADI,  
 ISTDRADI,  
 (CONVERT(INT, GETDATE() - HDTARIHI) / 365) AS HYASI,  
 HSAGBILGI.ENGELLILIK,  
 (  
  CASE  
   VWMUAYENEISL.YESILALANM  
   WHEN 0 THEN 'Acil Servis'  
   WHEN 1 THEN 'Yeşil Alan'  
   ELSE ''  
  END  
 ) AS 'TUR' 
FROM  
 VWMUAYENEISL  
 JOIN TTABLET WITH(NOLOCK) ON TTABLET.SUBEID = VWMUAYENEISL.SUBEID  
 AND (  
  TTABLET.SIRABOLUMID = VWMUAYENEISL.ISTBOLUMID  
  OR ISNULL(TTABLET.SIRABOLUMID, 0) = 0  
 )  
 AND (  
  TTABLET.SIRABIRIMID = VWMUAYENEISL.POLBIRIMID  
  OR ISNULL(TTABLET.SIRABIRIMID, 0) = 0  
 )  
 AND (  
  TTABLET.SIRADRID = VWMUAYENEISL.ISTDRID  
  OR ISNULL(TTABLET.SIRADRID, 0) = 0  
 )  
 LEFT JOIN HSAGBILGI WITH(NOLOCK) ON VWMUAYENEISL.HKIMLIKID = HSAGBILGI.HKIMLIKID  
WHERE  
 DURUM = 0  
 AND VWMUAYENEISL.ISTBOLUMID=VWMUAYENEISL.UYGBOLUMID  
 AND ITARIH > DATEADD(HOUR, -6, GETDATE())  
 AND TTABLET.TABLETIP = @TABLETIP  
-- AND TTABLET.SUBEID = @SUBEID --AND 1=0  
ORDER BY  
 ITARIH ASC --(CASE WHEN VWMUAYENEISL.HYASI>75 THEN 0 ELSE 1 END),  
 RETURN  
END  
GO