
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FNMDLYATAKDURUM]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FNMDLYATAKDURUM]
GO

CREATE FUNCTION [dbo].[FNMDLYATAKDURUM] ( @SUBEID int, @ODANO nvarchar(5), @TARIH smalldatetime)
RETURNS TABLE
AS
RETURN(
SELECT TODA.ODANO AS ODANO, YATAKNO,YTKDURUMU,TODA.SUBEID AS SUBEID, ODADURUMU,
CASE WHEN HUYATAKNO IS NOT NULL  
THEN 1
ELSE 0
END AS DOLU, MDLYATAKKODU
FROM TYATAK INNER JOIN 
          TODA ON TYATAK.ODANO=TODA.ODANO AND TYATAK.SUBEID=TODA.SUBEID LEFT JOIN
	(SELECT SUBEID, HUODANO, HUYATAKNO FROM HUYGISLEM
	INNER JOIN HESAPISL ON HUYGISLEM.HESAPISLID = HESAPISL.HESAPISLID
	INNER JOIN HDOSYA ON HESAPISL.HDOSYAID = HDOSYA.HDOSYAID
	WHERE HDOSYA.SUBEID = @SUBEID AND HUYGISLEM.DURUM IN(1,2) AND 
	(ITARIH >=@TARIH AND ITARIH <@TARIH + 1) AND IPTAL = 0 AND ONAYLI = 1 AND TEKLIF = 0
	GROUP BY SUBEID, HUODANO, HUYATAKNO) AS AKTUYATAK ON AKTUYATAK.SUBEID = TYATAK.SUBEID AND HUODANO = TODA.ODANO AND HUYATAKNO = YATAKNO
WHERE (TYATAK.ODANO = @ODANO OR @ODANO IS NULL) AND TYATAK.SUBEID = @SUBEID)
GO


