mysql - Select max date for user having sum(worktime) < x -

mysql - Select max date for user having sum(worktime) < x -

need help query - i'm using both sql server , mysql.

i have table of working hours of users looks this:

user worktime date user1 08:00:00 2015-03-01 user1 08:00:00 2015-03-02 user1 08:00:00 2015-03-03 user2 08:30:00 2015-03-11 user2 10:00:00 2015-03-13 user3 05:00:00 2015-03-11 user3 05:00:00 2015-03-12 user3 05:00:00 2015-03-14

what need lastly date every user when sum of worktime less or equal x - illustration let's it's 18 - plus sum of worktime date - result should be:

user worktime date user1 16:00:00 2015-03-02 user2 08:30:00 2015-03-11 user3 15:00:00 2015-03-14

when using simple query this:

select user, sum(worktime), max(date) worktime grouping user having sum(worktime) <= 18

i users total of hours <= 18, illustration user3.

i know how 2 separate queries , excel functions, wondering if it's possible within single statement.

if need both databases, suggest correlated subquery cumulative amount of time. can value want:

select user, max(date) (select wk.*, (select sum(wk2.worktime) worktime wk2 wk2.user = wk.user , wk2.date <= wk.date ) cume_worktime worktime wk ) t cume_worktime <= 18 grouping user;

this ansi standard sql , should work in both databases.

mysql sql sql-server