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

CREATE FUNCTION [dbo].[DashBoard_Tahsilat_Analiz]
(
    @BasTar datetime,
    @BitTar datetime,
    @SubeId int
)
RETURNS TABLE
AS
RETURN
(
SELECT SUM([Hasta Tahsilat]) [Hasta],	SUM(HKP) HKP ,	SUM([Kurum Fat.Tahsilat])[Kurum] FROM
	(
	SELECT ISNULL(SUM(CASE 
					WHEN ODEMETURUID = 0
						THEN OTLTUTAR
					ELSE 0
					END) - (
				- 1 * SUM(CASE 
						WHEN ODEMETURUID = 8
							THEN - 1 * OTLTUTAR
						ELSE 0
						END)
				) + SUM(CASE 
					WHEN ODEMETURUID = 3
						THEN OTLTUTAR
					ELSE 0
					END) - (
				- 1 * SUM(CASE 
						WHEN ODEMETURUID = 9
							THEN - 1 * OTLTUTAR
						ELSE 0
						END)
				) + SUM(CASE 
					WHEN ODEMETURUID = 1
						THEN OTLTUTAR
					ELSE 0
					END) + SUM(CASE 
					WHEN ODEMETURUID = 2
						THEN OTLTUTAR
					ELSE 0
					END) + SUM(CASE 
					WHEN ODEMETURUID = 6
						THEN OTLTUTAR
					ELSE 0
					END) - (
				- 1 * SUM(CASE 
						WHEN ODEMETURUID = 11
							THEN - 1 * OTLTUTAR
						ELSE 0
						END)
				) + SUM(CASE 
					WHEN ODEMETURUID = 4
						THEN OTLTUTAR
					ELSE 0
					END), 0) AS [Hasta Tahsilat],0 [HKP],0  AS [Kurum Fat.Tahsilat]
	FROM VWODEMEISL
	WHERE (
			ODEMETURUID NOT IN (
				5
				,7
				)
			)
		AND HKPODEME = 0
		AND (IPTAL = 0)
		AND OKRMODEME = 0
		AND SUBEID = @SubeId
		AND OTARIH >= @BasTar
		AND OTARIH < DateAdd(d, 1, @BitTar)

	UNION ALL

	SELECT  0,ISNULL(SUM(CASE 
					WHEN ODEMETURUID = 0
						THEN OTLTUTAR
					ELSE 0
					END) - (
				- 1 * SUM(CASE 
						WHEN ODEMETURUID = 8
							THEN - 1 * OTLTUTAR
						ELSE 0
						END)
				) + SUM(CASE 
					WHEN ODEMETURUID = 3
						THEN OTLTUTAR
					ELSE 0
					END) - (
				- 1 * SUM(CASE 
						WHEN ODEMETURUID = 9
							THEN - 1 * OTLTUTAR
						ELSE 0
						END)
				) + SUM(CASE 
					WHEN ODEMETURUID = 1
						THEN OTLTUTAR
					ELSE 0
					END) + SUM(CASE 
					WHEN ODEMETURUID = 2
						THEN OTLTUTAR
					ELSE 0
					END) + SUM(CASE 
					WHEN ODEMETURUID = 6
						THEN OTLTUTAR
					ELSE 0
					END) - (
				- 1 * SUM(CASE 
						WHEN ODEMETURUID = 11
							THEN - 1 * OTLTUTAR
						ELSE 0
						END)
				) + SUM(CASE 
					WHEN ODEMETURUID = 4
						THEN OTLTUTAR
					ELSE 0
					END), 0) AS [HKP],0
	FROM VWODEMEISL
	WHERE (
			ODEMETURUID NOT IN (
				5
				,7
				)
			)
		AND HKPODEME = 1
		AND (IPTAL = 0)
		AND OKRMODEME = 0
		AND SUBEID = @SubeId
		AND OTARIH >= @BasTar
		AND OTARIH < DateAdd(d, 1, @BitTar)

	UNION ALL

	SELECT 0,0,ISNULL(SUM(CASE 
					WHEN ODEMETURUID = 0
						THEN OTLTUTAR
					ELSE 0
					END) - (
				- 1 * SUM(CASE 
						WHEN ODEMETURUID = 8
							THEN - 1 * OTLTUTAR
						ELSE 0
						END)
				) + SUM(CASE 
					WHEN ODEMETURUID = 3
						THEN OTLTUTAR
					ELSE 0
					END) - (
				- 1 * SUM(CASE 
						WHEN ODEMETURUID = 9
							THEN - 1 * OTLTUTAR
						ELSE 0
						END)
				) + SUM(CASE 
					WHEN ODEMETURUID = 1
						THEN OTLTUTAR
					ELSE 0
					END) + SUM(CASE 
					WHEN ODEMETURUID = 2
						THEN OTLTUTAR
					ELSE 0
					END) + SUM(CASE 
					WHEN ODEMETURUID = 6
						THEN OTLTUTAR
					ELSE 0
					END) - (
				- 1 * SUM(CASE 
						WHEN ODEMETURUID = 11
							THEN - 1 * OTLTUTAR
						ELSE 0
						END)
				) + SUM(CASE 
					WHEN ODEMETURUID = 4
						THEN OTLTUTAR
					ELSE 0
					END), 0) AS [Kurum Fat.Tahsilat]
	FROM VWODEMEISL
	WHERE (
			ODEMETURUID NOT IN (
				5
				,7
				)
			)
		AND (IPTAL = 0)
		AND OKRMODEME > 0
		AND SUBEID = @SubeId
		AND OTARIH >= @BasTar
		AND OTARIH < DateAdd(d, 1, @BitTar)
) T
)