31 lines
1.1 KiB
Transact-SQL
31 lines
1.1 KiB
Transact-SQL
-- ================================================================
|
|
-- A tanár által nem tanított osztálycsoportok
|
|
-- ================================================================
|
|
DROP PROCEDURE IF EXISTS uspGetTanarNemTanitottOsztalyaiByFoglalkozas
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetTanarNemTanitottOsztalyaiByFoglalkozas
|
|
@pTanarId int
|
|
,@pTanevId int
|
|
AS
|
|
BEGIN
|
|
SELECT DISTINCT
|
|
CASE WHEN ocs.C_NEV NOT LIKE '%[^0-9]%' THEN CAST(LEFT(ocs.C_NEV,9) AS INT) ELSE 2147483647 END NumberOrder
|
|
,CASE WHEN LEFT(ocs.C_NEV, 1) BETWEEN '0' AND '9' AND ocs.C_NEV LIKE '%[^0-9]%' THEN CAST(LEFT(SUBSTRING(ocs.C_NEV, 1, PATINDEX('%[^0-9]%', ocs.C_NEV) - 1),9) AS INT) ELSE 2147483647 END NumberAndTextOrder
|
|
,ocs.ID AS OCSID
|
|
,ocs.C_NEV As Nev
|
|
,ocs.C_EVFOLYAMTIPUSA AS Evfolyam
|
|
FROM T_OSZTALYCSOPORT_OSSZES ocs
|
|
WHERE ocs.TOROLT = 'F'
|
|
AND ocs.C_TANEVID = @pTanevId
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM T_FOGLALKOZAS_OSSZES f
|
|
WHERE f.C_TANARID = @pTanarId
|
|
AND f.TOROLT = 'F'
|
|
AND f.C_TANEVID = @pTanevId
|
|
AND f.C_OSZTALYCSOPORTID = ocs.ID
|
|
)
|
|
ORDER BY NumberOrder ASC, NumberAndTextOrder ASC, ocs.C_NEV ASC
|
|
END
|
|
GO |