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

CREATE FUNCTION [dbo].[DashBoard_Doktor_DonemTopList]
(
    @BasTar datetime,
    @BitTar datetime,
    @SubeId int
)
RETURNS TABLE
AS
RETURN
(
WITH DoctorRanked AS (
    SELECT
        VWMDS_HESAPISL.ISTDRID AS DRID,
        TDRKIMLIK.DRADI,
        SUM(
            (GELIRHSTNETYTLTUTAR * (1 - HSTODENENINDYUZDE / 100.0) * (1 - HSTODMKESINTIYUZDE / 100.0)) / (1 + HSTKDVORAN / 100) 
            + (GELIRKRMNETYTLTUTAR * (1 - KRMODENENINDYUZDE / 100.0) * (1 - KRMODMKESINTIYUZDE / 100.0)) / (1 + KRMKDVORAN / 100) 
            + (GELIRSGNETYTLTUTAR * (1 - SGODENENINDYUZDE / 100.0) * (1 - SGODMKESINTIYUZDE / 100.0)) / (1 + SGKDVORAN / 100)
        ) AS ToplamGelir,
        TDRUZMANLIK.CERRAHI,
        ROW_NUMBER() OVER (PARTITION BY TDRUZMANLIK.CERRAHI ORDER BY 
            SUM(
                (GELIRHSTNETYTLTUTAR * (1 - HSTODENENINDYUZDE / 100.0) * (1 - HSTODMKESINTIYUZDE / 100.0)) / (1 + HSTKDVORAN / 100) 
                + (GELIRKRMNETYTLTUTAR * (1 - KRMODENENINDYUZDE / 100.0) * (1 - KRMODMKESINTIYUZDE / 100.0)) / (1 + KRMKDVORAN / 100) 
                + (GELIRSGNETYTLTUTAR * (1 - SGODENENINDYUZDE / 100.0) * (1 - SGODMKESINTIYUZDE / 100.0)) / (1 + SGKDVORAN / 100)
            ) DESC) AS RowNum
    FROM VWMDS_HESAPISL WITH (NOLOCK)
    INNER JOIN TDRKIMLIK ON TDRKIMLIK.DRID = VWMDS_HESAPISL.ISTDRID
    INNER JOIN TDRUZMANLIK WITH (NOLOCK) ON TDRKIMLIK.DRUZMANLIKID = TDRUZMANLIK.DRUZMANLIKID 
    WHERE GELIRDISI = 0
        AND VWMDS_HESAPISL.SUBEID = @SubeId
        AND VWMDS_HESAPISL.ITARIH >= @BasTar
        AND VWMDS_HESAPISL.ITARIH < DATEADD(DAY,1,@BitTar)
    GROUP BY VWMDS_HESAPISL.ISTDRID, TDRKIMLIK.DRADI, TDRUZMANLIK.CERRAHI
)

SELECT
    CASE 
        WHEN CERRAHI = 1 THEN 'Cerrahi'
        WHEN CERRAHI = 0 THEN 'Dahili'
    END AS Grup,
    DRID,
    DRADI,
    ToplamGelir,	
	ISNULL((		
			SELECT
				SUM(
				(GELIRHSTNETYTLTUTAR * (1 - HSTODENENINDYUZDE / 100.0) * (1 - HSTODMKESINTIYUZDE / 100.0)) / (1 + HSTKDVORAN / 100) 
				+ (GELIRKRMNETYTLTUTAR * (1 - KRMODENENINDYUZDE / 100.0) * (1 - KRMODMKESINTIYUZDE / 100.0)) / (1 + KRMKDVORAN / 100) 
				+ (GELIRSGNETYTLTUTAR * (1 - SGODENENINDYUZDE / 100.0) * (1 - SGODMKESINTIYUZDE / 100.0)) / (1 + SGKDVORAN / 100)
			      ) AS ToplamGelirOncekiYil

				FROM VWMDS_HESAPISL HISL WITH (NOLOCK)					
					WHERE GELIRDISI = 0 AND HISL.ISTDRID=DoctorRanked.DRID
						AND HISL.SUBEID = 1
						AND HISL.ITARIH >=DATEADD(MONTH,-1, @BasTar)
						AND HISL.ITARIH < DATEADD(MONTH,-1,DATEADD(DAY,1,@BitTar))
						
		),0) ToplamGelirOncekiAy
FROM DoctorRanked
WHERE RowNum <= 5
--ORDER BY CERRAHI DESC, RowNum
)