GO
/****** Object:  StoredProcedure [dbo].[sp_GetTanevRendjeCsengetesiRend]    Script Date: 2016.04.26. 11:41:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Zsiga Attila
-- Create date: 2016.04.22.
-- Description:	Tanévrendi események szerinti csengetési rendek
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetTanevRendjeCsengetesiRend]
	@pKezdoDatum DATETIME,
	@pVegDatum DATETIME,
	@pOsztalyCsoportID INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


SELECT
	Datum,
	CsengetesiRendID,
	LastChangedMax
INTO
	#tmpMindenOsztalyraVonatkozo
FROM
	(
		SELECT
			TANEVRENDJE.C_DATUM Datum,
			MAX(LASTCHANGED) LastChangedMax
		FROM
			(SELECT * FROM T_TANEVRENDJE WHERE T_TANEVRENDJE.TOROLT = 'F' AND T_TANEVRENDJE.C_DATUM BETWEEN @pKezdoDatum AND @pVegDatum AND T_TANEVRENDJE.C_OSSZESCSOPORTRAVONATKOZIK = 'T') TANEVRENDJE
		GROUP BY
			TANEVRENDJE.C_DATUM
	) MindenOsztalyra
	LEFT JOIN (SELECT C_CSENGETESIRENDID CsengetesiRendID, C_DATUM, LASTCHANGED FROM T_TANEVRENDJE WHERE T_TANEVRENDJE.TOROLT = 'F' AND T_TANEVRENDJE.C_DATUM BETWEEN @pKezdoDatum AND @pVegDatum AND T_TANEVRENDJE.C_OSSZESCSOPORTRAVONATKOZIK = 'T') TANEVRENDJE ON TANEVRENDJE.C_DATUM = MindenOsztalyra.Datum AND TANEVRENDJE.LASTCHANGED = MindenOsztalyra.LastChangedMax



CREATE TABLE #tmpAdottOsztalyraVonatkozo
(
	Datum DATETIME,
	CsengetesiRendID INT,
	LastChangedMax DATETIME
)

IF @pOsztalyCsoportID IS NULL
	BEGIN
		INSERT INTO #tmpAdottOsztalyraVonatkozo
		SELECT
			NULL Datum,
			NULL CsengetesiRendID,
			NULL LastChangedMax
		FROM
			(SELECT NULL Datum, NULL CsengetesiRendID FROM T_TANEVRENDJE WHERE 1=2) AdottOsztalyra
	END
ELSE
	BEGIN
		INSERT INTO #tmpAdottOsztalyraVonatkozo
		SELECT
			Datum,
			CsengetesiRendID,
			LastChangedMax
		FROM
			(
				SELECT
					TANEVRENDJE.C_DATUM Datum,
					MAX(LASTCHANGED) LastChangedMax
				FROM
					(SELECT * FROM T_TANEVRENDJE WHERE T_TANEVRENDJE.TOROLT = 'F' AND T_TANEVRENDJE.C_DATUM BETWEEN @pKezdoDatum AND @pVegDatum AND T_TANEVRENDJE.C_OSSZESCSOPORTRAVONATKOZIK = 'F') TANEVRENDJE
					INNER JOIN 
					(SELECT * FROM T_OSZTALYCSOPORT_TANEVRENDJE WHERE C_OSZTALYCSOPORTID = @pOsztalyCsoportID) OSZTALYCSOPORT_TANEVRENDJE ON OSZTALYCSOPORT_TANEVRENDJE.C_TANEVRENDJEID = TANEVRENDJE.ID
				GROUP BY
					TANEVRENDJE.C_DATUM
			) AdottOsztalyra
			LEFT JOIN (SELECT C_CSENGETESIRENDID CsengetesiRendID, C_DATUM, LASTCHANGED FROM T_TANEVRENDJE WHERE T_TANEVRENDJE.TOROLT = 'F' AND T_TANEVRENDJE.C_DATUM BETWEEN @pKezdoDatum AND @pVegDatum AND T_TANEVRENDJE.C_OSSZESCSOPORTRAVONATKOZIK = 'F') TANEVRENDJE ON TANEVRENDJE.C_DATUM = AdottOsztalyra.Datum AND TANEVRENDJE.LASTCHANGED = AdottOsztalyra.LastChangedMax
	END


SELECT
	CASE
		WHEN
			AdottOsztalyraVonatkozo.CsengetesiRendID IS NOT NULL
		THEN
			AdottOsztalyraVonatkozo.Datum
		ELSE
			MindenOsztalyraVonatkozo.Datum
	END Datum,
	CASE
		WHEN
			AdottOsztalyraVonatkozo.CsengetesiRendID IS NOT NULL
		THEN
			AdottOsztalyraVonatkozo.CsengetesiRendID
		ELSE
			MindenOsztalyraVonatkozo.CsengetesiRendID
	END CsengetesiRendID
FROM
	#tmpMindenOsztalyraVonatkozo AS MindenOsztalyraVonatkozo
	FULL OUTER JOIN	
	#tmpAdottOsztalyraVonatkozo 
	AS AdottOsztalyraVonatkozo ON 
	AdottOsztalyraVonatkozo.Datum = MindenOsztalyraVonatkozo.Datum

DROP TABLE #tmpMindenOsztalyraVonatkozo
DROP TABLE #tmpAdottOsztalyraVonatkozo

END