SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MEDWEB_KURUMGECMISTETKIKKART]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].MEDWEB_KURUMGECMISTETKIKKART
GO

CREATE FUNCTION dbo.MEDWEB_KURUMGECMISTETKIKKART(@KURUMID int,@SUBEID int,@ILKTARIH datetime, @SONTARIH datetime) 
RETURNS @retKRMGECMISTETKIKKART TABLE 
(   UYGBOLUMADI nvarchar(100),
	BOLUMADI nvarchar(100),
	DRADI nvarchar(50),
	GIRISTARIH datetime,
	HTETKIKKARTID int,
	HADISOYADI nvarchar(50),
	HKIMLIKID int
)
AS
BEGIN
	INSERT INTO @retKRMGECMISTETKIKKART
	SELECT LUYGBOLUM, LBOLUM, LDOKTOR, GIRISTARIH, HTETKIKKARTID, HADI + ' ' + HSOYADI AS HADISOYADI, HKIMLIKID 
	FROM VWTETKIKHASTA 
	WHERE HKCARIID  = @KURUMID AND SUBEID = @SUBEID AND GIRISTARIH >= @ILKTARIH AND GIRISTARIH < DATEADD(DAY,1,@SONTARIH) AND 
	EXISTS (SELECT TOP 1 HTETKIKID FROM VWTETKIK WHERE VWTETKIK.HTETKIKKARTID = VWTETKIKHASTA.HTETKIKKARTID AND VWTETKIK.DURUM = 2 AND WEBGORUNSUN = 1 AND VWTETKIK.HKIMLIKID = VWTETKIKHASTA.HKIMLIKID)		
    RETURN
END
GO
