PTO Scheduled

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;

DECLARE @MIN DATE, @MAX DATE;

SET @MIN = {mindate};
SET @MAX = {maxdate};
/*
SET @MIN = '10/31/2021';
SET @MAX = '11/6/2021';
*/

SELECT
    'GPT' + E.IDNUM AS PositionID,
    CF.AsString AS TimeOffPolicyName,
    'Taken' AS TransactionType,
    PDS.NAME AS ReasonCodes,
    PD.EVENTDATE AS TransactionStartDate,
    '8:00 AM' AS TransactionStartTime,
    SUM(PD.Hours) AS TransactionAmount,
    'hours' AS TransactionUnit,
    'N' AS SendToPayroll,
    E.lastname,
    E.firstname
FROM Employees E
    INNER JOIN EMPLOYEEFIELDS CF ON E.FILEKEY = CF.FILEKEY
    INNER JOIN PAYDESHIST PD ON E.FILEKEY = PD.FILEKEY
    INNER JOIN PAYDESIGNATIONS PDS ON PD.PAYDESNUM = PDS.PAYDESNUM
    LEFT JOIN INCIDENTS I ON E.FILEKEY = I.FILEKEY
        AND I.EVENTDATE = PD.EVENTDATE
        AND I.RULESOURCE IN (39,40,41,42,43,44,45,46,47,48,58,59,60,61,62,63,64,65,66,67,72,73)
WHERE 
    CF.FIELDID = 16
    AND PD.EVENTDATE BETWEEN @MIN AND @MAX
    AND PD.PAYDESNUM = 6
    AND E.status IN (0, 1)
    AND I.FILEKEY IS NULL
-- Exclude records where incidents exist
GROUP BY 
    E.IDNUM, 
    CF.ASSTRING, 
    PDS.NAME, 
    PD.EVENTDATE, 
    E.lastname, 
    E.firstname;