IF OBJECT_ID('sp_GetOrarendiOrakForOraosszevonas') IS NOT NULL BEGIN
  DROP PROCEDURE sp_GetOrarendiOrakForOraosszevonas
END
GO

CREATE PROCEDURE [sp_GetOrarendiOrakForOraosszevonas]
	@IntezmenyId int,
	@TanevId int,
	@Date datetime,
	@TanarId int,
  	@OrarendioraId int
AS
BEGIN
	SET NOCOUNT ON;

  DECLARE
     @HetNapja int
    ,@Hetirend int
    ,@Oraszam int

  SELECT @HetNapja = ora.C_HETNAPJA
		,@Hetirend = ora.C_HETIREND
		,@Oraszam = ora.C_ORASZAM
  FROM T_ORARENDIORA ora
  WHERE ora.ID = @orarendioraId

  SELECT ora.ID					AS OrarendiOraId
		,tantargy.C_NEV			AS Tantargy
		,osztalycsoport.C_NEV	AS Osztalycsoport
  FROM T_ORARENDIORA ora
  INNER JOIN T_TANTARGY tantargy ON ora.C_TANTARGYID = tantargy.ID
  INNER JOIN T_OSZTALYCSOPORT osztalycsoport ON ora.C_OSZTALYCSOPORTID = osztalycsoport.ID
  WHERE @HetNapja = ora.C_HETNAPJA
   AND (@Hetirend = ora.C_HETIREND OR ora.C_HETIREND = 1554)
   AND @Oraszam = ora.C_ORASZAM
   AND ora.C_INTEZMENYID = @IntezmenyId
   AND ora.C_TANEVID = @TanevId
   AND ora.C_ORAERVENYESSEGKEZDETE <= @Date
   AND ora.C_ORAERVENYESSEGVEGE > @Date
   AND ora.C_TANARID = @TanarId
   AND ora.TOROLT = 'F'
END
GO