27 lines
1.1 KiB
Transact-SQL
27 lines
1.1 KiB
Transact-SQL
IF OBJECT_ID('dbo.fnGetOsztalyVegzosTanitasiNap') IS NOT NULL BEGIN
|
|
DROP FUNCTION dbo.fnGetOsztalyVegzosTanitasiNap
|
|
END
|
|
GO
|
|
|
|
CREATE FUNCTION dbo.fnGetOsztalyVegzosTanitasiNap (@osztalyId INT, @intezmenyId INT, @tanevId INT)
|
|
RETURNS datetime
|
|
BEGIN
|
|
|
|
DECLARE @Global DATETIME = (SELECT MIN(C_DATUM) FROM T_TANEVRENDJE_OSSZES WHERE C_INTEZMENYID = @intezmenyId AND C_TANEVID = @tanevId
|
|
AND C_OSSZESCSOPORTRAVONATKOZIK = 'T' AND C_NAPTIPUSA IN (1402, 7600, 7601, 7602, 7603, 1395) AND TOROLT = 'F')
|
|
DECLARE @isVegzos CHAR(1) = (SELECT C_VEGZOSEVFOLYAM FROM T_OSZTALYCSOPORT_OSSZES WHERE ID = @osztalyId AND TOROLT = 'F' AND C_TANEVID = @tanevId)
|
|
DECLARE @result DATETIME
|
|
|
|
IF(@isVegzos = 'T')
|
|
BEGIN
|
|
SET @result =
|
|
(SELECT TOP(1) C_DATUM FROM (
|
|
SELECT C_DATUM FROM T_TANEVRENDJE_OSSZES tr
|
|
INNER JOIN T_OSZTALYCSOPORT_TANEVRENDJE otr ON tr.ID = otr.C_TANEVRENDJEID AND otr.C_OSZTALYCSOPORTID = @osztalyId
|
|
WHERE
|
|
C_NAPTIPUSA IN (1402, 7600, 7601, 7602, 7603) AND C_INTEZMENYID = @intezmenyId AND C_TANEVID = @tanevId AND tr.TOROLT = 'F'
|
|
) x ORDER BY C_DATUM)
|
|
END
|
|
RETURN ISNULL(@result, @Global)
|
|
END
|
|
GO |