IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Fn_Dr_Icd_Tetkik_TopList]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].Fn_Dr_Icd_Tetkik_TopList
GO
CREATE FUNCTION [dbo].[Fn_Dr_Icd_Tetkik_TopList]
(
    @ICDKOD NVARCHAR(100),
    @MDLTEDAVITURU NVARCHAR(20),
    @DRID INT
)
RETURNS TABLE
AS
RETURN
(
    /*'['+CAST(ISLEMSAYISI AS nvarchar)+'] '+ITEMADI ITEMADI,TURNO,TUR,ITEMID,SUTKODU,ITEMADI ITEMADI2,ITEMGRUPADI*/
	SELECT * FROM
    (
        SELECT TOP 20  
            'Radyoloji' AS Tur,'1' TurNo,
            H.ITEMID,
            ISNULL(H.ITEMKODU2_LOINC, H.ITEMKODU2) AS SUTKODU,
            H.ITEMADI,
            H.ITEMGRUPADI,
            COUNT(*) AS ISLEMSAYISI 
        FROM 
            VWHESAPISLDRM H
        INNER JOIN 
            HDOSYAICD ICD ON H.HDOSYAID = ICD.HDOSYAID 
        WHERE 
            H.MDLTEDAVITURU IN (SELECT Value FROM dbo.SplitString(@MDLTEDAVITURU, ',') WHERE Value<>'')  
            AND H.UYGBOLUMID IN (SELECT BOLUMID FROM TBOLUM WHERE BOLUMTURU = 3) 
            AND H.IPTAL = 0 
            AND H.TABLOTIPI = 1 
            AND ICD.DRID = @DRID 
            AND ICD.ICDKOD IN (SELECT Value FROM dbo.SplitString(@ICDKOD, ',') WHERE Value<>'' )
        GROUP BY 
            H.ITEMID,
            ISNULL(H.ITEMKODU2_LOINC, H.ITEMKODU2),
            H.ITEMADI,
            H.ITEMGRUPADI
        ORDER BY 
            COUNT(*) DESC, 
            H.ITEMGRUPADI
    ) R

    UNION ALL 
/*'['+CAST(ISLEMSAYISI AS nvarchar)+'] '+ITEMADI ITEMADI,TURNO,TUR,ITEMID,SUTKODU,ITEMADI ITEMADI2,ITEMGRUPADI*/
    SELECT  *  FROM 
    (
        SELECT TOP 20  
            'Lab/Patoloji' AS Tur,'2' TurNo,
            H.ITEMID,
            ISNULL(H.ITEMKODU2_LOINC, H.ITEMKODU2) AS SUTKODU,
            H.ITEMADI,
            H.ITEMGRUPADI,
            COUNT(*) AS ISLEMSAYISI 
        FROM 
            VWHESAPISLDRM H
        INNER JOIN 
            HDOSYAICD ICD ON H.HDOSYAID = ICD.HDOSYAID 
        WHERE 
            H.MDLTEDAVITURU IN (SELECT Value FROM dbo.SplitString(@MDLTEDAVITURU, ',') WHERE Value<>'')  
            AND H.UYGBOLUMID IN (SELECT BOLUMID FROM TBOLUM WHERE BOLUMTURU = 2) 
            AND H.IPTAL = 0 
            AND H.TABLOTIPI = 1 
            AND ICD.DRID = @DRID 
            AND ICD.ICDKOD IN (SELECT Value FROM dbo.SplitString(@ICDKOD, ',') WHERE Value<>'')
        GROUP BY 
            H.ITEMID,
            ISNULL(H.ITEMKODU2_LOINC, H.ITEMKODU2),
            H.ITEMADI,
            H.ITEMGRUPADI
        ORDER BY 
            COUNT(*) DESC 
    ) L
);


--SELECT Tur,ITEMID,ITEMADI,ITEMGRUPADI FROM dbo.Fn_Dr_Icd_Tetkik_TopList( 'M54.5,S83.6,,', 'A,G,',16)


--SELECT TURNO,TUR,ITEMID,SUTKODU,ITEMADI,ITEMADI2,ITEMGRUPADI,ISLEMSAYISI FROM dbo.Fn_Dr_Icd_Tetkik_TopList('J06,','A',102)