CODEWITHSUNDEEP
sqldate
walangala
walangala

Reputation: 241

Get the last Date record from a date range if falls below a certain value

I have query where it gets a list of employees and the most recent date where they fall below a 30 hour work week.

SELECT T1.Employee, T1.Date, T1.Hours
FROM EmployeeHours T1 
INNER JOIN (SELECT Employee, MAX(Date) AS MAXDATE
            FROM EmployeeHours WHERE Hours < 30 GROUP BY Employee) T2
 ON T1.Employee = T2.Employee AND T1.Date = T2.MAXDATE
 GROUP BY T1.Employee, T1.Date, T1.Hours

What I would like is to see if I can get the the most recent date in a date period if the employee falls below 30 hours in a 12 week period

I guess my biggest issue here is a 12 week period from what? I guess a start date would be best, but I'm not sure how to implement that with a DATEADD() here

Upvotes: 0

Views: 46

Answers (1)

Jan
Jan

Reputation: 421

Is this what you're looking for?

    SELECT T1.Employee, T1.Date, T1.Hours
    FROM Employee T1 
    INNER JOIN (SELECT Employee, MAX(Date) AS MAXDATE
                FROM Employee 
                WHERE DATEADD(Day,-12, GETDATE()) <= Date AND DATEADD(Day,0,GETDATE()) > Date
                GROUP BY Employee) T2
     ON T1.Employee = T2.Employee AND T1.Date = T2.MAXDATE
             GROUP BY T1.Employee, T1.Date, T1.Hours

Upvotes: 1

Related Questions