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

CREATE FUNCTION [dbo].[FNMDL_EK10C](@HDOSYAID INTEGER,@MDLTEDAVITURU NVARCHAR(1))
RETURNS TABLE AS
RETURN

SELECT TOP 1   CASE WHEN @MDLTEDAVITURU<>'A' THEN NULL ELSE Kontrol end Kontrol,'EK10C İstem Nedeni Kontrol' KontrolAdi FROM
(
	SELECT (SELECT CASE WHEN COUNT(*)=0 THEN '1' else '2' end  FROM HMUAYENE WHERE  ISNULL(CAST(HMUAYENE.HMRADNOT AS NVARCHAR(50)),'') <>'' AND HMUAYENE.HMUAYENEID=T.HMUAYENEID) Kontrol, * FROM 
	(
	 SELECT CASE WHEN COUNT(*)=0 THEN 0 else 1 end MrVarMi,HMUAYENEID FROM HMUAYENE WHERE HMUAYENE.HDOSYAID IN
	 (
	 SELECT HESAPISL.HDOSYAID FROM HESAPISL INNER JOIN HDOSYA ON HDOSYA.HDOSYAID=HESAPISL.HDOSYAID 
	INNER JOIN TITEM ON TITEM.ITEMID=HESAPISL.ITEMID
	WHERE HESAPISL.MDLKAYDET=1 and ITEMKODU2 BETWEEN '803870' AND '804510' AND ITEMTURUID=2 AND HESAPISL.IPTAL=0 AND 
	      MDLTEDAVITURU='A' AND HESAPISL.HDOSYAID=@HDOSYAID--3442556 MR VAR --3443701 mr var     --3439329 hiç yok
		  )  GROUP BY HMUAYENEID
	) AS T --order by 
) TT ORDER BY MrVarMi DESC
GO


