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

CREATE FUNCTION [dbo].[DashBoard_Hasta_Cinsiyet_Portfoy]
(
    @Yil int,
    @SubeId int
)
RETURNS TABLE
AS
RETURN
(
SELECT Ay, SUM(Erkek) Erkek, SUM(Kadin) Kadin FROM
			(
				SELECT 
					MONTH(HDTARIH) AS Ay,    
					COUNT(DISTINCT HKIMLIKID) AS Erkek , 0 Kadin

				FROM 
					VWHDOSYA WITH (NOLOCK)
				WHERE 
				   HCINSIYETI='E' and  YEAR(HDTARIH)=@Yil AND SUBEID = @SubeId
				GROUP BY 
					MONTH(HDTARIH), HCINSIYETI

				UNION ALL 

				SELECT 
					MONTH(HDTARIH) AS Ay,    
					0 AS Erkek , COUNT(DISTINCT HKIMLIKID) Kadin

				FROM 
					VWHDOSYA WITH (NOLOCK)
				WHERE 
				   HCINSIYETI='K' and  YEAR(HDTARIH)=@Yil AND SUBEID = @SubeId
				GROUP BY 
					MONTH(HDTARIH), HCINSIYETI
			) AS TBL GROUP BY AY


)

