41 lines
1.4 KiB
Transact-SQL
41 lines
1.4 KiB
Transact-SQL
IF OBJECT_ID('fnGetNapirendUtkozesByTanar') IS NOT NULL BEGIN
|
|
DROP FUNCTION fnGetNapirendUtkozesByTanar
|
|
END
|
|
GO
|
|
|
|
CREATE FUNCTION fnGetNapirendUtkozesByTanar (
|
|
@startTime datetime,
|
|
@endTime datetime,
|
|
@tanarId int,
|
|
@intezmenyId int,
|
|
@tanevId int
|
|
) RETURNS INT
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM T_NAPTARINAP_OSSZES n
|
|
LEFT JOIN T_TANEVRENDJE_OSSZES tr ON tr.C_DATUM = n.C_NAPDATUMA
|
|
INNER JOIN T_ORARENDIORA_OSSZES o ON ISNULL(tr.C_HETIREND, n.C_HETIREND) = o.C_HETIREND
|
|
AND ISNULL(tr.C_HETNAPJA, n.C_HETNAPJA) = o.C_HETNAPJA
|
|
AND n.C_INTEZMENYID = o.C_INTEZMENYID
|
|
AND n.C_TANEVID = o.C_TANEVID
|
|
WHERE CAST(CAST(@startTime AS DATE) AS DATETIME) + CAST(CAST(o.C_ORAKEZDETE AS TIME) AS DATETIME) <= @endTime
|
|
AND CAST(CAST(@endTime AS DATE) AS DATETIME) + CAST(CAST(o.C_ORAVEGE AS TIME) AS DATETIME) >= @startTime
|
|
AND o.C_TANARID = @tanarId
|
|
AND n.C_TANEVID = @tanevId AND n.C_INTEZMENYID = @intezmenyId
|
|
AND n.C_NAPDATUMA BETWEEN CAST(@startTime AS DATE) AND CAST(@endTime AS DATE)
|
|
UNION ALL
|
|
SELECT 1 FROM T_TANITASIORA_OSSZES o
|
|
WHERE o.C_ORAKEZDETE <= @endTime
|
|
AND o.C_ORAVEGE >= @startTime
|
|
AND ISNULL(o.C_HELYETTESITOTANARID, o.C_ORATULAJDONOSID) = @tanarId
|
|
AND o.C_TANEVID = @tanevId
|
|
AND o.C_INTEZMENYID = @intezmenyId
|
|
)
|
|
BEGIN
|
|
RETURN 1
|
|
END
|
|
RETURN 0
|
|
END
|
|
GO
|
|
|