SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('[dbo].sp_GetDokumentumTanitasiNapokSorszama') IS NOT NULL
BEGIN
  DROP PROCEDURE [dbo].sp_GetDokumentumTanitasiNapokSorszama
END  
GO

CREATE PROCEDURE [dbo].sp_GetDokumentumTanitasiNapokSorszama
	@tanevId					INT,
	@osztalyId					INT
AS
BEGIN
  SET NOCOUNT ON;

DECLARE @TanitasiNapokNaptariNapTable TABLE (Datum DATE)
DECLARE @TanitasiNapokTanevRendjeTable TABLE (Datum DATE)

INSERT INTO @TanitasiNapokTanevRendjeTable
SELECT 
	C_DATUM 
FROM T_TANEVRENDJE_OSSZES TanevRendje
WHERE
	C_TANEVID = @tanevId
	AND TOROLT = 'F'
	AND C_OSSZESCSOPORTRAVONATKOZIK = 'T'
	AND C_NAPTIPUSA IN (1385, 1393, 1394, 1395, 1400, 1402, 1403, 1404)

UNION

SELECT 
	C_DATUM 
FROM T_TANEVRENDJE_OSSZES TanevRendje
	INNER JOIN T_OSZTALYCSOPORT_TANEVRENDJE OsztalyCsoportTanevRendje ON OsztalyCsoportTanevRendje.C_TANEVRENDJEID = TanevRendje.ID
WHERE
	C_TANEVID = @tanevId
	AND TOROLT = 'F'
	AND C_OSSZESCSOPORTRAVONATKOZIK = 'F'
	AND C_NAPTIPUSA IN (1385, 1393, 1394, 1395, 1400, 1402, 1403, 1404)
	AND C_OSZTALYCSOPORTID IN (SELECT ID FROM fnGetDokumentumKapcsolodoOsztalycsoportok(@osztalyId, @tanevId))

INSERT INTO @TanitasiNapokNaptariNapTable
SELECT
	C_NAPDATUMA
FROM T_NAPTARINAP_OSSZES NaptariNap
WHERE
	NaptariNap.TOROLT = 'F'
	AND C_TANEVID = @tanevId
	AND C_NAPTIPUSA IN (1385, 1393, 1394, 1395, 1400, 1402, 1403, 1404)
	AND NOT EXISTS (SELECT Datum FROM @TanitasiNapokTanevRendjeTable TanitasiNapokTanevRendjeTable WHERE TanitasiNapokTanevRendjeTable.Datum = NaptariNap.C_NAPDATUMA)

SELECT Datum FROM @TanitasiNapokTanevRendjeTable
UNION
SELECT Datum FROM @TanitasiNapokNaptariNapTable
ORDER BY Datum

END