set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: <Sélectionne la liste des absences en fonction de l'EMPLOYEE_KEY et de la periode de traitement>
-- =============================================
ALTER PROCEDURE [dbo].[PS_Select_Absence_fct_EmployeeKey_Periode]
-- Add the parameters for the stored procedure here
@date_debut as datetime,
@date_fin as datetime,
@EMPLOYEE_KEY int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- cas ou la periode de recherche est comprise dans la periode d'absence
SELECT
EMPLOYEE_KEY,
EMPLOYEE_SURNAME,
EMPLOYEE_FIRST_NAME,
ABSENCE_TYPE_ABBREVIATION,
START_DATE,
END_DATE,
START_IN_THE_MORNING,
ENDING_THE_AFTERNOON,
DURATION_IN_DAYS,
DURATION_IN_HOURS
FROM dbo.ABSENCE_FILE
WHERE EMPLOYEE_KEY = @EMPLOYEE_KEY
AND START_DATE < @date_debut
AND @date_fin < END_DATE
UNION
-- cas ou la periode d'absence est comprise dans la periode de recherche
SELECT
EMPLOYEE_KEY,
EMPLOYEE_SURNAME,
EMPLOYEE_FIRST_NAME,
ABSENCE_TYPE_ABBREVIATION,
START_DATE,
END_DATE,
START_IN_THE_MORNING,
ENDING_THE_AFTERNOON,
DURATION_IN_DAYS,
DURATION_IN_HOURS
FROM dbo.ABSENCE_FILE
WHERE EMPLOYEE_KEY = @EMPLOYEE_KEY
AND @date_debut < START_DATE
AND END_DATE < @date_fin
UNION
-- cas ou les absences commencent pendant la période de recherche et se
-- terminent après la periode de recherche
SELECT
EMPLOYEE_KEY,
EMPLOYEE_SURNAME,
EMPLOYEE_FIRST_NAME,
ABSENCE_TYPE_ABBREVIATION,
START_DATE,
END_DATE,
START_IN_THE_MORNING,
ENDING_THE_AFTERNOON,
DURATION_IN_DAYS,
DURATION_IN_HOURS
FROM dbo.ABSENCE_FILE
WHERE EMPLOYEE_KEY = @EMPLOYEE_KEY
AND @date_debut<START_DATE
AND @date_fin<END_DATE
AND START_DATE<@date_fin
UNION
-- cas ou les absences commencent avant la période de recherche et se
-- terminent pendant la période de recherche
SELECT
EMPLOYEE_KEY,
EMPLOYEE_SURNAME,
EMPLOYEE_FIRST_NAME,
ABSENCE_TYPE_ABBREVIATION,
START_DATE,
END_DATE,
START_IN_THE_MORNING,
ENDING_THE_AFTERNOON,
DURATION_IN_DAYS,
DURATION_IN_HOURS
FROM dbo.ABSENCE_FILE
WHERE EMPLOYEE_KEY = @EMPLOYEE_KEY
AND @date_debut>START_DATE
AND @date_debut<END_DATE
AND END_DATE < @date_fin
ORDER BY START_DATE ASC
END
Partager