DROP FUNCTION IF EXISTS fnGetDokumentumTanulokFeljegyzeseiHavibontasban
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION fnGetDokumentumTanulokFeljegyzeseiHavibontasban (@tanevId INT, @osztalyId INT, @FelevVege DATE, @BelepesDatum DATETIME, @KilepesDatum DATETIME)
  RETURNS TABLE
RETURN (
	SELECT 
		 tte.C_TANULOID AS TanuloId
		,d.C_NAME AS Tipus
		,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) =  9 THEN 1 ELSE 0 END) AS Szeptember
		,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 10 THEN 1 ELSE 0 END) AS Oktober
		,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 11 THEN 1 ELSE 0 END) AS November
		,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 12 THEN 1 ELSE 0 END) AS December
		,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) =  1 THEN 1 ELSE 0 END) AS Januar
		,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) =  2 THEN 1 ELSE 0 END) AS Februar
		,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) =  3 THEN 1 ELSE 0 END) AS Marcius
		,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) =  4 THEN 1 ELSE 0 END) AS Aprilis
		,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) =  5 THEN 1 ELSE 0 END) AS Majus
		,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) =  6 THEN 1 ELSE 0 END) AS Junius
		,SUM(CASE WHEN te.C_ROGZITESDATUMA <= @FelevVege THEN 1 ELSE 0 END) AS Felev
		,SUM(1) AS Ossz
	FROM T_TANULO_TANULOESEMENY tte
		INNER JOIN T_TANULOESEMENY_OSSZES te ON te.ID = tte.C_TANULOESEMENYID AND te.TOROLT = 'F'
		INNER JOIN T_DICTIONARYITEMBASE_OSSZES d ON d.ID = te.C_TIPUS AND d.TOROLT = 'F'
	WHERE
		tte.C_TANULOID IN (SELECT TanuloId FROM fnGetDokumentumOsztalyokCsoportokTanuloi(@tanevId, @osztalyId, 'T'))
		AND te.TOROLT = 'F'
		AND @BelepesDatum <= te.C_ROGZITESDATUMA
		AND (@KilepesDatum IS NULL OR @KilepesDatum >= te.C_ROGZITESDATUMA)
	GROUP BY tte.C_TANULOID, d.C_NAME
)