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

CREATE FUNCTION [dbo].[DashBoard_Hasta_Randevu_Sadakat]
(
    @Yil int,
    @SubeId int
)
RETURNS TABLE
AS
RETURN
(
SELECT Ay, SUM(Alinan) Alinan, SUM(Gerceklesen) Gerceklesen, ((SUM(Gerceklesen) *100)) / (SUM(Alinan)) Oran FROM
			(
				SELECT 
					MONTH(RTARIH) AS Ay,    
					COUNT(KIMLIKID) AS Alinan , 1 Gerceklesen

				FROM 
					HRANDEVU WITH (NOLOCK)
				WHERE 
				   HRANDEVU.RANDDURUMID NOT IN (5,6) AND HRANDEVU.KIMLIKID IS NOT NULL and  YEAR(RTARIH)=@Yil AND SUBEID =@SubeId
				GROUP BY 
					MONTH(RTARIH)

				UNION ALL 

				SELECT 
					MONTH(RTARIH) AS Ay,    
					1 AS Alinan , COUNT(DISTINCT HMUAYENEISLID) Gerceklesen

				FROM 
					HRANDEVU WITH (NOLOCK)
				WHERE 
				    HRANDEVU.RANDDURUMID NOT IN (5,6) AND HRANDEVU.HMUAYENEISLID IS NOT NULL and  YEAR(RTARIH)=@Yil AND SUBEID =@SubeId
				GROUP BY 
					MONTH(RTARIH)
			) AS TBL GROUP BY AY
)