Agency PTO Incidents

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @MIN DATE, @MAX DATE

SET @MIN = {mindate}
SET @MAX = {maxdate}

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,
    I.INCIDENTLABEL AS Comments,
    E.Lastname,
    E.firstname,
    ST.NAME AS STATUS

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 STATUSTYPES ST ON E.status = ST.NUM
    LEFT JOIN (
        SELECT
        FILEKEY,
        EVENTDATE,
        INCIDENTLABEL,
        ROW_NUMBER() OVER (PARTITION BY FILEKEY, EVENTDATE ORDER BY INCIDENTLABEL) AS rn
    FROM INCIDENTS
    WHERE RULESOURCE IN (39,40,41,42,43,44,45,46,47,48,58,59,60,61,62,63,64,65,66,67,72,73)
    ) I ON E.FILEKEY = I.FILEKEY
        AND PD.EVENTDATE = I.EVENTDATE
        AND I.rn = 1

WHERE CF.FIELDID = 16
    AND PD.EVENTDATE BETWEEN @MIN AND @MAX
    AND PD.PAYDESNUM = 6
    AND E.status IN (9)
    AND I.FILEKEY IS NOT NULL

GROUP BY E.IDNUM, CF.ASSTRING, PDS.NAME, PD.EVENTDATE, E.FILEKEY, E.lastname, E.firstname, ST.NAME, I.INCIDENTLABEL