111 lines
4.3 KiB
Transact-SQL
111 lines
4.3 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGetHianyzokEsJelenlevokSzama
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetHianyzokEsJelenlevokSzama
|
|
@pTanevId INT
|
|
,@pMinOraszam INT
|
|
AS
|
|
|
|
BEGIN
|
|
SET NOCOUNT ON
|
|
CREATE TABLE #NaptariNap (
|
|
Datum datetime
|
|
,OsztalyId int
|
|
,IsTanitasiNap char(1)
|
|
,Nap int
|
|
,Honap nvarchar(20)
|
|
,PRIMARY KEY (Datum, OsztalyId)
|
|
)
|
|
DECLARE @DateFrom datetime, @DateTo datetime
|
|
|
|
SELECT @DateFrom = tv.C_KEZDONAP, @DateTo = tv.C_UTOLSONAP FROM T_TANEV_OSSZES tv WHERE ID = @pTanevId
|
|
|
|
INSERT INTO #NaptariNap
|
|
SELECT
|
|
tr.Datum
|
|
,tr.OsztalyId
|
|
,ISNULL(tro.IsTanitasiNap, tr.IsTanitasiNap) AS IsTanitasiNap
|
|
,tr.Nap
|
|
,tr.Honap
|
|
FROM (
|
|
SELECT
|
|
nn.C_NAPDATUMA AS Datum
|
|
,o.ID AS OsztalyId
|
|
,IIF(nt.C_ISTANORAI = 'F' AND nt.C_ISTANORANKIVULI = 'F', 'F', 'T') AS IsTanitasiNap
|
|
,DAY(C_NAPDATUMA) AS Nap
|
|
,FORMAT(nn.C_NAPDATUMA, 'MMMM', 'HU-hu') as Honap
|
|
FROM T_NAPTARINAP_OSSZES nn
|
|
INNER JOIN T_OSZTALY_OSSZES o ON o.TOROLT = 'F' AND o.C_ALTANEVID = @pTanevId
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = o.ID AND (ocs.C_FELADATKATEGORIAID = (SELECT tv.C_DEFAULTFELADATKATEGORIAID FROM T_TANEV_OSSZES tv WHERE tv.ID = @pTanevId AND tv.TOROLT = 'F'))
|
|
INNER JOIN T_NAPTIPUS_OSSZES nt ON nt.ID = nn.C_NAPTIPUSA AND nt.C_ALTANEVID = nn.C_TANEVID AND nn.C_INTEZMENYID = nt.C_ALINTEZMENYID AND nt.TOROLT = 'F'
|
|
WHERE nn.C_NAPDATUMA BETWEEN @DateFrom AND @DateTo AND nn.C_TANEVID = @pTanevId
|
|
AND nn.TOROLT = 'F'
|
|
) tr
|
|
LEFT JOIN (
|
|
SELECT
|
|
nn.C_NAPDATUMA AS Datum
|
|
,o.ID AS OsztalyId
|
|
,IIF(nt.C_ISTANORAI = 'F' AND nt.C_ISTANORANKIVULI = 'F', 'F', 'T') AS IsTanitasiNap
|
|
FROM T_NAPTARINAP_OSSZES nn
|
|
INNER JOIN T_TANEVRENDJE_OSSZES tr ON tr.C_DATUM = nn.C_NAPDATUMA AND tr.C_OSSZESCSOPORTRAVONATKOZIK = 'F' AND tr.TOROLT = 'F'
|
|
INNER JOIN T_OSZTALYCSOPORT_TANEVRENDJE ocstr ON ocstr.C_TANEVRENDJEID = tr.ID
|
|
INNER JOIN T_OSZTALY_OSSZES o ON ocstr.C_OSZTALYCSOPORTID = o.ID
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = o.ID AND (ocs.C_FELADATKATEGORIAID = (SELECT tv.C_DEFAULTFELADATKATEGORIAID FROM T_TANEV_OSSZES tv WHERE tv.ID = @pTanevId AND tv.TOROLT = 'F'))
|
|
INNER JOIN T_NAPTIPUS_OSSZES nt ON nt.ID = tr.C_NAPTIPUSA AND nt.C_ALTANEVID = tr.C_TANEVID AND nt.TOROLT = 'F'
|
|
WHERE nn.C_TANEVID = @pTanevId
|
|
AND nn.TOROLT = 'F'
|
|
) tro ON tr.Datum = tro.Datum AND tr.OsztalyId = tro.OsztalyId
|
|
|
|
SELECT
|
|
nn.Datum
|
|
,nn.Honap AS Honap
|
|
,nn.Nap
|
|
,nn.OsztalyId
|
|
,ocs.C_NEV AS OsztalyNev
|
|
,nn.IsTanitasiNap
|
|
,lsz.Letszam
|
|
,IIF(h.Hianyzas IS NULL AND IsTanitasiNap = 'T', 0, h.Hianyzas) AS Hianyzas
|
|
FROM #NaptariNap nn
|
|
LEFT JOIN (
|
|
SELECT nn.Datum, nn.OsztalyId, COUNT(1) AS Letszam
|
|
FROM #NaptariNap nn
|
|
INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.C_OSZTALYCSOPORTID = nn.OsztalyId
|
|
AND nn.Datum >= tcs.C_BELEPESDATUM
|
|
AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM < nn.Datum)
|
|
AND tcs.C_TANEVID = @pTanevId
|
|
AND tcs.TOROLT = 'F'
|
|
WHERE nn.IsTanitasiNap = 'T'
|
|
GROUP BY nn.Datum, nn.OsztalyId
|
|
) lsz ON lsz.Datum = nn.Datum AND lsz.OsztalyId = nn.OsztalyId
|
|
LEFT JOIN (
|
|
SELECT x.C_DATUM AS Datum, tcs.C_OSZTALYCSOPORTID AS OsztalyId, SUM(CNT) AS Hianyzas
|
|
FROM (
|
|
SELECT tn.C_DATUM, mu.C_ORATANULOIID, COUNT(DISTINCT C_ORATANULOIID) AS CNT
|
|
FROM T_TANULOMULASZTAS_OSSZES mu
|
|
INNER JOIN T_TANITASIORA_OSSZES tn ON tn.ID = mu.C_TANITASIORAKID
|
|
WHERE mu.C_TIPUS = 1500
|
|
AND mu.C_TANEVID = @pTanevId
|
|
AND tn.C_TANEVID = @pTanevId
|
|
AND mu.TOROLT = 'F'
|
|
AND tn.TOROLT = 'F'
|
|
GROUP BY tn.C_DATUM, mu.C_ORATANULOIID
|
|
HAVING COUNT(1) >= @pMinOraszam
|
|
) x
|
|
INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.C_TANULOID = x.C_ORATANULOIID
|
|
AND x.C_DATUM >= tcs.C_BELEPESDATUM
|
|
AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM < x.C_DATUM)
|
|
AND tcs.C_TANEVID = @pTanevId
|
|
AND tcs.TOROLT = 'F'
|
|
INNER JOIN T_OSZTALY_OSSZES o ON o.ID = tcs.C_OSZTALYCSOPORTID
|
|
AND o.C_ALTANEVID = @pTanevId
|
|
AND o.TOROLT = 'F'
|
|
GROUP BY x.C_DATUM, tcs.C_OSZTALYCSOPORTID
|
|
) h ON h.Datum = nn.Datum AND h.OsztalyId = nn.OsztalyId
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = nn.OsztalyId AND ocs.C_TANEVID = @pTanevId AND ocs.TOROLT = 'F'
|
|
ORDER BY nn.Datum, nn.OsztalyId
|
|
|
|
END
|
|
GO
|
|
|
|
|