38 lines
1.1 KiB
Transact-SQL
38 lines
1.1 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGetMulasztasokSzama
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetMulasztasokSzama
|
|
@pTanevId int
|
|
,@pTanarId int
|
|
,@pDarab int
|
|
,@pMulasztasTipusKeses int
|
|
,@pMulasztasTipusHianyzas int
|
|
AS BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT TOP (@pDarab)
|
|
tn.C_DATUM AS MulasztasDatuma
|
|
,FORMAT(tn.C_DATUM, 'dddd', 'hu-HU') AS MulasztasNapja
|
|
,COUNT(tm.ID) AS MulasztasokSzama
|
|
FROM T_TANULOMULASZTAS_OSSZES tm
|
|
INNER JOIN T_TANITASIORA_OSSZES tn ON tn.ID = tm.C_TANITASIORAKID
|
|
AND tn.TOROLT = 'F'
|
|
LEFT JOIN T_OSZTALY_OSSZES o ON o.ID = tn.C_OSZTALYCSOPORTID
|
|
AND o.TOROLT = 'F'
|
|
AND (o.C_OSZTALYFONOKID = @pTanarId OR o.C_OFOHELYETTESID = @pTanarId)
|
|
LEFT JOIN T_CSOPORT_OSSZES cs ON cs.ID = tn.C_OSZTALYCSOPORTID
|
|
AND cs.TOROLT = 'F'
|
|
AND cs.C_CSOPORTVEZETOID = @pTanarId
|
|
WHERE tm.C_TANEVID = @pTanevId
|
|
AND tm.TOROLT = 'F'
|
|
AND tm.C_IGAZOLT IS NULL
|
|
AND tm.C_TIPUS BETWEEN @pMulasztasTipusKeses AND @pMulasztasTipusHianyzas
|
|
AND COALESCE(o.ID, cs.ID) IS NOT NULL
|
|
GROUP BY
|
|
tn.C_DATUM
|
|
ORDER BY
|
|
tn.C_DATUM DESC
|
|
|
|
END
|
|
GO
|