Query to calculate the total working hours per user and per Team

[sadim]

Hi all
I have a ms-access table with the users activity per day.
During the day, the user can be affected to more than one team, but only one team at same time.
I need to calculate the total time an user were available in that day with Team break done


Table example:

Nbr	Name	_Date	State	Team
4636	User99	02/06/2019 15:10:20	Affected	4820
4636	User99	02/06/2019 15:10:25	Withdraw	4820
4636	User99	02/06/2019 15:28:56	Affected	4820
4636	User99	02/06/2019 19:56:25	Withdraw	4820
4636	User99	02/06/2019 20:25:16	Affected	4820
4605	User1	02/06/2019 00:00:01	Affected	4823
4605	User1	02/06/2019 00:33:17	Affected	4820
4605	User1	02/06/2019 01:07:45	Withdraw	4820
4605	User1	02/06/2019 01:12:11	Affected	4820
4605	User1	02/06/2019 02:33:19	Withdraw	4820
4605	User1	02/06/2019 03:04:14	Affected	4820
4605	User1	02/06/2019 04:08:35	Withdraw	4820
4605	User1	02/06/2019 04:13:58	Affected	4820
4605	User1	02/06/2019 04:48:12	Withdraw	4820
4605	User1	02/06/2019 04:49:58	Affected	4820
4605	User1	02/06/2019 05:57:03	Withdraw	4820
4605	User1	02/06/2019 06:01:41	Affected	4820
4605	User1	02/06/2019 06:11:30	Withdraw	4820
4605	User1	02/06/2019 06:24:58	Affected	4820
4605	User1	02/06/2019 23:30:43	Affected	4823
4603	OtherUser	02/06/2019 06:01:38	Affected	4821

I supose that if for each user and for same Team, if i sum all the times for the State Affected and subtract with all the times the user the Withdraw state i will get the total, it should work
But there is a particularity. As can be seen for user User1, two consecutives Afftected state can occour, but with differents Teams. This means that the user was affected to the first Team, and
then makes a withdraw from the 1st team (no event on the table) and then was affected to the second Team
I have done the query below, but i am not sure that it works well and for sure does not works when there are two consecutives Affected states
Does anyone help me to make the ms-access query
My query:
SELECT Nbr, Name, Team, FORMAT(dateadd("s",SUM(dur),"1/1/70"),"hh:mm:ss") AS TOTAL_LogInTIME
FROM (SELECT DATEDIFF("s","1/1/70",[_Date]) * -1 AS dur, Nbr, Name, Team
FROM Eventos where State='Affected'
UNION ALL
SELECT DATEDIFF("s","1/1/70", [_Date]) AS dur, Nbr, Name, Team
FROM Eventos where State='Withdraw'
) AS t
GROUP BY Nbr, Name, Team;

[CJ_London]

I think what you are suggesting is if there is not a withdraw from a team use the next affected time?

So what happens if the last time of the day is 'affected' per your 'otheruser'? go to the next day? have a specific 'shift end' time?
what happens if the first record of the day is a withdraw? ignore it? go to previous day? have a specific 'shift start' time?

You need to be clear on what the business rules are around this before any sort of sensible suggestion can be made

[sadim]

Hi Ajax,
In 1st place, thanks for your help.
If the last event of the user is affected, then, we should consider that he was affected till the end of the day (24h00m00s). You can assume that is like a Withdraw that hapens at midnight
The Withdraw state can never be the first event. The first state is allways affected
BRegards

[CJ_London]

and in respect of ' if there is not a withdraw from a team use the next affected time?'

[sadim]

Hi Ajax,

Yes that is correct, if there is no withdraw from a team, then the time to use like the corresponding withdraw should be the time of the affected on another team.

BRegards

[sadim]

Hi,

I have double check the data on the table, and in fact i can see some few cases were the 1st event is a withdraw.
So, regarding the question "

what happens if the first record of the day is a withdraw? ignore it? go to previous day? have a specific 'shift start' time?

", in this case you should consider that at 00:00:00 the user was affected.

[CJ_London]

OK. First I would point out that 'Name' is a reserved word. Using it as a table, field or control name will cause you unexpected problems with misleading error messages at some point, suggest change it to something more meaningful such as userName. You will also get the same issues with starting a field name with anything other than a letter (such as _Date)

Since these are all with a single day your suggestion of summing affected and withdraw and then subtracting one from the other should work, but to mimic your existing query you just need

SELECT Nbr, Name, Team, datevalue([_Date]) as Date, format(sum(cdbl(timevalue([_Date]))*iif(State=Affected,-1,1),"hh:mm:ss") as dur
FROM Eventos
GROUP BY Nbr, Name, Team, datevalue([_Date])

try this before I continue, I want to make sure I've understood the calculation requirement correctly and it is producing the expected (but wrong until we address missing records) result

[sadim]

Hi
when i try to run your query, i get some errors. After some trys, i was able to run like this:
SELECT Nbr, Name, Team, datevalue([_Date]) , format(sum(cdbl(timevalue([_Date]))*iif(State='Affected',-1,1)),"hh:mm:ss") as dur
FROM Eventos
GROUP BY Nbr, Name, Team, datevalue([_Date])

Note: i remove as Date, add a ) after 1,) and change Affected to 'Affected'

[orange]

Please provide details/messages for i get some errors.

[CJ_London]

OK, my bad - does it provide the correct data per your union query?

[sadim]

The error is:
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."