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

CREATE FUNCTION [dbo].[DashBoard_Hasta_Analiz]
(
    @BasTar datetime,
    @BitTar datetime,
    @SubeId int
)
RETURNS TABLE
AS
RETURN
(
SELECT 'Genel Durum' AS [Aciklama]
	,ISNULL((
			SELECT COUNT(DISTINCT HKIMLIKID)
			FROM HDOSYA
			WHERE HDIPTAL = 0
				AND HDOSYA.SUBEID = @SubeId
				AND HDOSYA.HDTARIH >= @BasTar
				AND HDOSYA.HDTARIH < DATEADD(DAY,1,@BitTar)
			), 0) AS [Hasta Sayisi]
	,ISNULL((
			SELECT COUNT(DISTINCT HKIMLIK.HKIMLIKID)
			FROM HKIMLIK
			INNER JOIN HDOSYA ON HKIMLIK.HKIMLIKID = HDOSYA.HKIMLIKID
			INNER JOIN TDRKIMLIK ON HDOSYA.DRID = TDRKIMLIK.DRID
			WHERE HDOSYA.HDIPTAL = 0
				AND HDOSYA.SUBEID = @SubeId
				AND HKIMLIK.KYTTARIH >= @BasTar
				AND HKIMLIK.KYTTARIH < DATEADD(DAY,1,@BitTar)
				AND HDOSYA.HDTARIH >= @BasTar
				AND HDOSYA.HDTARIH < DATEADD(DAY,1,@BitTar)
			), 0) AS [Yeni Gelen Hasta Sayisi]
	,ISNULL((
			SELECT COUNT(DISTINCT HKIMLIKID)
			FROM VWMUAYENEISL
			WHERE IPTAL = 0
				AND VWMUAYENEISL.SUBEID = @SubeId
				AND MUAYENETIPIID <> 2
				AND ITARIH >= @BasTar
				AND ITARIH < DATEADD(DAY,1,@BitTar)
			), 0) AS [Muayene Hasta Sayisi]
	,ISNULL((
			SELECT COUNT(DISTINCT HKIMLIKID)
			FROM VWYATIS
			WHERE DURUM IN (
					1
					,2
					)
				AND VWYATIS.SUBEID = @SubeId
				AND HYYATISTARIH >= @BasTar
				AND HYYATISTARIH < DATEADD(DAY,1,@BitTar)
			), 0) AS [Yatan Hasta Sayisi]

	,ISNULL((
			SELECT COUNT(DISTINCT HKIMLIKID)
			FROM VWYATIS
			WHERE DURUM IN (
					2
					)
				AND VWYATIS.SUBEID = @SubeId
				AND HYCIKISTARIH >= @BasTar
				AND HYCIKISTARIH < DATEADD(DAY,1,@BitTar)
			), 0) AS [Taburcu Hasta Sayisi]

	,ISNULL((
			SELECT COUNT(DISTINCT HKIMLIKID)
			FROM VWYATIS
			WHERE DURUM IN (
					1
					,2
					)
				AND VWYATIS.SUBEID = @SubeId
				AND HYYATISTARIH < DATEADD(DAY,1,@BitTar)
				AND (
					HYCIKISTARIH >= DATEADD(DAY,1,@BitTar)
					OR HYCIKISTARIH IS NULL
					)
			), 0) AS [Yatisi Devam Eden Hasta Sayisi]
)