37 lines
1.8 KiB
Transact-SQL
37 lines
1.8 KiB
Transact-SQL
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
|
|
)
|