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

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 (
        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 (0, 1)
    AND I.FILEKEY IS NOT NULL  -- This replaces the EXISTS condition

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