55 lines
1.8 KiB
Transact-SQL
55 lines
1.8 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGetMulasztasokSzama
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetMulasztasokSzama
|
|
@pTanevId int
|
|
,@pTanarId int
|
|
,@pDarab int
|
|
AS BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE
|
|
@pBelepesDatum date
|
|
,@pKilepesDatum date
|
|
,@pUtolsoTanitasDatum date
|
|
|
|
SELECT
|
|
@pBelepesDatum = (CASE WHEN tanev.C_AKTIV = 'T' THEN GETDATE() ELSE tanev.C_UTOLSOTANITASINAP END)
|
|
,@pKilepesDatum = (CASE WHEN tanev.C_AKTIV = 'T' THEN GETDATE() ELSE tanev.C_ELSOTANITASINAP END)
|
|
,@pUtolsoTanitasDatum = tanev.C_UTOLSOTANITASINAP
|
|
FROM T_TANEV_OSSZES tanev
|
|
WHERE tanev.TOROLT = 'F'
|
|
AND tanev.ID = @pTanevId
|
|
|
|
SELECT TOP (@pDarab)
|
|
tn.C_DATUM AS MulasztasDatuma
|
|
,FORMAT(tn.C_DATUM, 'dddd','hu-HU') AS MulasztasNapja
|
|
,COUNT(tm.C_ORATANULOIID) AS MulasztasokSzama
|
|
FROM T_TANITASIORA_OSSZES tn
|
|
INNER JOIN T_TANULOMULASZTAS_OSSZES tm ON tm.C_TANITASIORAKID = tn.ID
|
|
AND tm.TOROLT = 'F'
|
|
AND tm.C_TANEVID = tn.C_TANEVID
|
|
INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.C_TANULOID = tm.C_ORATANULOIID
|
|
AND tcs.TOROLT = 'F'
|
|
AND tcs.C_TANEVID = tn.C_TANEVID
|
|
AND tcs.C_BELEPESDATUM <= @pBelepesDatum
|
|
AND (tcs.C_KILEPESDATUM > @pKilepesDatum OR tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM >= @pUtolsoTanitasDatum)
|
|
LEFT JOIN T_OSZTALY_OSSZES o ON o.ID = tcs.C_OSZTALYCSOPORTID
|
|
AND o.TOROLT = 'F'
|
|
AND o.C_ALTANEVID = tn.C_TANEVID
|
|
AND (o.C_OSZTALYFONOKID = @pTanarId OR o.C_OFOHELYETTESID = @pTanarId)
|
|
LEFT JOIN T_CSOPORT_OSSZES cs ON cs.ID = tcs.C_OSZTALYCSOPORTID
|
|
AND cs.TOROLT = 'F'
|
|
AND cs.C_ALTANEVID = tn.C_TANEVID
|
|
AND cs.C_CSOPORTVEZETOID = @pTanarId
|
|
WHERE tn.TOROLT = 'F'
|
|
AND tn.C_TANEVID = @pTanevId
|
|
AND COALESCE(o.ID, cs.ID) IS NOT NULL
|
|
GROUP BY
|
|
tn.C_DATUM
|
|
ORDER BY
|
|
tn.C_DATUM DESC
|
|
|
|
END
|
|
GO
|