Agency PTO Scheduled

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,
    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 INCIDENTS I ON E.FILEKEY = I.FILEKEY
        AND PD.EVENTDATE = I.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 (2,3,4,5,6,7,8,9,10,11,12,13)
    AND I.FILEKEY IS NULL

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