42 lines
1.4 KiB
Transact-SQL
42 lines
1.4 KiB
Transact-SQL
-- =============================================
|
|
-- Description: <Előszedjük a házi feladatokat a megadott paraméterek alapján>
|
|
-- =============================================
|
|
DROP PROCEDURE IF EXISTS uspGetHaziFeladatokByDateRange
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetHaziFeladatokByDateRange
|
|
@pIntezmenyId int
|
|
,@pTanevId int
|
|
,@pDatumTol datetime = NULL
|
|
,@pDatumIg datetime = NULL
|
|
AS BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT
|
|
hf.ID AS ID
|
|
,hf.C_FELADASDATUMA AS FeladasDatuma
|
|
,ISNULL(hf.C_ORARENDIORAID, oo.ID) AS OrarendiOraId
|
|
,hf.C_TANITASIORAID AS TanitasiOraId
|
|
,hf.C_HATARIDO AS Hatarido
|
|
FROM T_HAZIFELADATOK hf
|
|
INNER JOIN T_TANITASIORA t on t.ID = hf.C_TANITASIORAID
|
|
LEFT JOIN T_ORARENDIORA oo on oo.C_ORARENDIORAGROUPID = t.C_ORARENDIORAGROUPID and t.C_DATUM BETWEEN oo.C_ORAERVENYESSEGKEZDETE and oo.C_ORAERVENYESSEGVEGE
|
|
WHERE hf.C_FELADASDATUMA BETWEEN @pDatumtol AND @pDatumig
|
|
AND hf.C_INTEZMENYID = @pIntezmenyId
|
|
AND hf.C_TANEVID = @pTanevId
|
|
UNION ALL
|
|
SELECT
|
|
hf.ID AS ID
|
|
,hf.C_FELADASDATUMA AS FeladasDatuma
|
|
,hf.C_ORARENDIORAID AS OrarendiOraId
|
|
,hf.C_TANITASIORAID AS TanitasiOraId
|
|
,hf.C_HATARIDO AS Hatarido
|
|
FROM T_HAZIFELADATOK hf
|
|
WHERE hf.C_FELADASDATUMA BETWEEN @pDatumtol AND @pDatumig
|
|
AND hf.C_INTEZMENYID = @pIntezmenyId
|
|
AND hf.C_TANEVID = @pTanevId
|
|
AND hf.C_ORARENDIORAID IS NOT NULL
|
|
|
|
END
|
|
GO
|