79 lines
2.7 KiB
Transact-SQL
79 lines
2.7 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGetBukasokSzama
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetBukasokSzama
|
|
@pTanevId int
|
|
,@pTanarId int
|
|
,@pDarab int
|
|
,@pBukasAtlag float
|
|
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_KEZDONAP END)
|
|
,@pUtolsoTanitasDatum = tanev.C_UTOLSOTANITASINAP
|
|
FROM T_TANEV_OSSZES tanev
|
|
WHERE tanev.TOROLT = 'F'
|
|
AND tanev.ID = @pTanevId
|
|
|
|
SELECT TOP (@pDarab)
|
|
fh.C_NYOMTATASINEV AS Nev
|
|
,ocs.C_NEV AS Osztaly
|
|
,COUNT(ertekeles.C_TANTARGYID) AS TantargyakSzama
|
|
FROM T_FOGLALKOZAS_OSSZES f
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON f.C_OSZTALYCSOPORTID = ocs.ID
|
|
AND ocs.C_TANEVID = f.C_TANEVID
|
|
AND ocs.TOROLT = 'F'
|
|
INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON ocs.ID = tcs.C_OSZTALYCSOPORTID
|
|
AND tcs.C_BELEPESDATUM <= @pBelepesDatum
|
|
AND (tcs.C_KILEPESDATUM > @pKilepesDatum OR tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM >= @pUtolsoTanitasDatum)
|
|
AND tcs.C_TANEVID = f.C_TANEVID
|
|
AND tcs.TOROLT = 'F'
|
|
INNER JOIN T_FELHASZNALO_OSSZES fh ON tcs.C_TANULOID = fh.ID
|
|
AND fh.C_TANEVID = f.C_TANEVID
|
|
AND fh.TOROLT = 'F'
|
|
INNER JOIN (
|
|
SELECT
|
|
te.C_TANULOID
|
|
,te.C_OSZTALYCSOPORTID
|
|
,te.C_TANTARGYID
|
|
FROM T_TANULOERTEKELES_OSSZES te
|
|
INNER JOIN T_OSZTALYZATTIPUS_OSSZES ot ON te.C_ERTEKELESOSZTALYZATID = ot.ID
|
|
AND ot.C_ALTANEVID = te.C_TANEVID
|
|
AND ot.TOROLT = 'F'
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES d ON ot.ID = d.ID
|
|
AND d.C_TANEVID = te.C_TANEVID
|
|
AND d.TOROLT = 'F'
|
|
LEFT JOIN T_TANARIATLAGSULY_OSSZES ta ON te.C_ERTEKELESMODID = ta.C_ERTEKELESMODID
|
|
AND ta.C_TANEVID = te.C_TANEVID
|
|
AND ta.TOROLT = 'F'
|
|
AND te.C_ERTEKELESOSZTALYZATID IS NOT NULL
|
|
WHERE te.C_TANEVID = @pTanevId
|
|
AND te.TOROLT = 'F'
|
|
GROUP BY
|
|
te.C_TANULOID
|
|
,te.C_OSZTALYCSOPORTID
|
|
,te.C_TANTARGYID
|
|
HAVING ROUND(SUM(CAST(d.C_VALUE AS float) * CAST(ISNULL(ta.C_SULY, 100) AS float) / 100) / SUM(CAST(ISNULL(ta.C_SULY, 100) AS float) / 100), 2) < @pBukasAtlag
|
|
) ertekeles ON ertekeles.C_TANULOID = fh.ID
|
|
AND ertekeles.C_OSZTALYCSOPORTID = f.C_OSZTALYCSOPORTID
|
|
AND ertekeles.C_TANTARGYID = f.C_TANTARGYID
|
|
WHERE f.C_TANEVID = @pTanevId
|
|
AND f.TOROLT = 'F'
|
|
AND f.C_TANARID = @pTanarId
|
|
GROUP BY
|
|
fh.C_NYOMTATASINEV
|
|
,ocs.C_NEV
|
|
ORDER BY
|
|
fh.C_NYOMTATASINEV
|
|
,ocs.C_NEV
|
|
|
|
END
|
|
GO
|