37 lines
1.4 KiB
Transact-SQL
37 lines
1.4 KiB
Transact-SQL
DROP FUNCTION IF EXISTS fnGetNapirendUtkozesByOsztalycsoport
|
|
GO
|
|
|
|
CREATE FUNCTION fnGetNapirendUtkozesByOsztalycsoport (
|
|
@startTime datetime
|
|
,@endTime datetime
|
|
,@osztalyCsoportId 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_OSZTALYCSOPORTID IN (SELECT ID FROM dbo.fnGetKapcsolodoOsztalycsoportok(@osztalyCsoportId))
|
|
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 o.C_OSZTALYCSOPORTID IN (SELECT ID FROM dbo.fnGetKapcsolodoOsztalycsoportok(@osztalyCsoportId))
|
|
)
|
|
BEGIN
|
|
RETURN 1
|
|
END
|
|
RETURN 0
|
|
END
|
|
GO
|