ALTER 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(100),
	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) 1
		FROM (SELECT TOP(1) HESAPISLID FROM HTETKIK T WITH(NOLOCK) WHERE T.HTETKIKKARTID = VWTETKIKHASTA.HTETKIKKARTID AND T.DURUM =2 ) T
		INNER JOIN HESAPISL I WITh(NOLOCK) ON I.HESAPISLID = T.HESAPISLID
		INNER JOIN HDOSYA   D WITH(NOLOCK) ON D.HDOSYAID = I.HDOSYAID AND D.HKIMLIKID = VWTETKIKHASTA.HKIMLIKID
		INNER JOIN TTETKIK TT WITH(NOLOCK) ON TT.ITEMID = I.ITEMID AND TT.WEBGORUNSUN = 1)		
    RETURN
END

GO


