DROP PROCEDURE IF EXISTS [dbo].[uspGetElozoOranHianyzottTanulo]
GO

CREATE PROCEDURE [dbo].[uspGetElozoOranHianyzottTanulo]
   @pIntezmenyId      INT
  ,@pTanevId        INT
  ,@pTantargyId      INT
  ,@pOsztalycsoportId    INT
  ,@pDatum        DATETIME
AS
BEGIN
SET NOCOUNT ON;
  SELECT
    tm.C_ORATANULOIID AS tanuloId
  FROM T_TANULOMULASZTAS_OSSZES tm
  WHERE
    tm.C_TIPUS = 1500 /*Hiányzás*/
    AND tm.C_TANEVID = @pTanevId
    AND tm.C_INTEZMENYID = @pIntezmenyId
    AND tm.TOROLT = 'F'
    AND tm.C_TANITASIORAKID = (
      SELECT TOP(1) t.ID FROM T_TANITASIORA_OSSZES t
      WHERE
        t.C_TANTARGYID = @pTantargyId
        AND t.C_OSZTALYCSOPORTID = @pOsztalycsoportId
        AND t.C_ORAKEZDETE < @pDatum
        AND t.C_TANEVID = @pTanevId
        AND t.TOROLT = 'F'
      ORDER BY t.C_ORAKEZDETE DESC
    )

END
GO