45 Days Worked

--Uses daily hours custom field for determining how many hour are in a day to multiply by 45 to get the target hours.

SELECT 
    E.FILEKEY,
    E.LASTNAME,
    E.FIRSTNAME,
    E.IDNUM,
    F.AsFloat AS DailyHours,
    F.AsFloat * 45.0 AS TargetHours,
    ISNULL(WorkTotals.TotalWorkedHours, 0) AS TotalWorkedHours,

    -- Earliest date reached, or 1900-01-01 if not reached
    CONVERT(date, ISNULL(
        (
            SELECT MIN(C.WorkDate)
            FROM (
                SELECT 
                    P.FILEKEY,
                    CAST(P.EVENTDATE AS date) AS WorkDate,
                    SUM(P.HOURS) AS DailyHours,
                    SUM(SUM(P.HOURS)) OVER (
                        PARTITION BY P.FILEKEY
                        ORDER BY CAST(P.EVENTDATE AS date)
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                    ) AS CumulativeHours
                FROM PAYDESHIST AS P
                WHERE P.FILEKEY = E.FILEKEY
                    AND P.PAYDESNUM IN (1, 2)
                    AND P.EVENTDATE IS NOT NULL
                GROUP BY P.FILEKEY, CAST(P.EVENTDATE AS date)
            ) AS C
            WHERE C.CumulativeHours >= (F.AsFloat * 45.0)
        ), '1900-01-01')
    ) AS DateReached

FROM EMPLOYEES AS E
LEFT JOIN (
    SELECT 
        FILEKEY,
        MAX(ISNULL(AsFloat, 0)) AS AsFloat  -- daily hours
    FROM EMPLOYEEFIELDS
    WHERE FIELDID = 2
    GROUP BY FILEKEY
) AS F
    ON E.FILEKEY = F.FILEKEY
LEFT JOIN (
    SELECT 
        FILEKEY,
        SUM(HOURS) AS TotalWorkedHours
    FROM PAYDESHIST
    WHERE PAYDESNUM IN (1, 2)
    GROUP BY FILEKEY
) AS WorkTotals
    ON E.FILEKEY = WorkTotals.FILEKEY
ORDER BY E.LASTNAME, E.FIRSTNAME;