IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MEDWEB_SONRECETELER]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION  [dbo].[MEDWEB_SONRECETELER]
GO
CREATE FUNCTION [dbo].[MEDWEB_SONRECETELER]()
RETURNS @retGecmisReceteler TABLE 
(
	BOLUMADI nvarchar(100),
	DRADI nvarchar(50),
	RECTARIH datetime,
	HKIMLIKID nvarchar(50),
	HTCKIMLIKNO nvarchar(50),
	SUBEID int,
	SUBEADI nvarchar(200),
	ISIM nvarchar(150),
	CEPTEL nvarchar(50),
	MDLERECETENO nvarchar(50),
	TARIH datetime
)
AS
BEGIN
	INSERT INTO @retGecmisReceteler
		  SELECT VWRECETE.BOLUMADI,
			    VWRECETE.DRADI,
			    VWRECETE.HRECTARIH,
			    VWRECETE.HKIMLIKID,
			    VWRECETE.HTCKIMLIKNO,
			    VWRECETE.SUBEID,
		  (
			 SELECT SUBEADI
			 FROM SUBELER
			 WHERE SUBEID = VWRECETE.SUBEID
		  ) AS SUBEADI,
			    VWRECETE.HADI+' '+VWRECETE.HSOYADI AS ISIM,
			    ISNULL(HKIMLIK.HCEP, ISNULL(HKIMLIK.HCEP2, ISNULL(HKIMLIK.HTEL, ''))) AS CEPTEL,
			    ISNULL(VWRECETE.MDLERECETENO,'') AS MDLERECETENO,
			    ISNULL(HRECETE.KYTTARIH, HRECETE.HRECTARIH) AS TARIH
		  FROM VWRECETE
			  JOIN HKIMLIK ON HKIMLIK.HKIMLIKID = VWRECETE.HKIMLIKID
			  JOIN HRECETE ON HRECETE.HRECETEID = VWRECETE.HRECETEID
		  WHERE HRECETE.HRECTARIH > DATEADD(DAY, -3, GETDATE())
		
    RETURN
END
