Summing Hours from Date Field by Last date and on

[ilikebirds]

I have the following table make up:

Employee | Signin| Task|
Bob | 10:01| Build|
Bob | 10:44 | Fix|
Bob | 11:23| Answered calls|


Now How can I automate that build took 43 seconds, Fix took 79secconds, and Answered calls is still open.

[NeoPa]

Start with some SQL on these lines :

SELECT [Employee],
       [Task],
       [SignIn],
       (SELECT Min([SignIn])
        FROM   [tblTask] AS tTi
        WHERE  (tTi.Employee=tTo.Employee)
          AND  (tTi.SignIn>tTo.SignIn)) AS [Finished]

FROM [tblTask] AS tTo

From there it should be easy enough to tidy up the results to give you what you want.

[ilikebirds]

Does this code look corredt to you?

SELECT [emp],[process],[timemodified],(select Min([timemodified)])
FROM [Dtime] as tTi
WHERE (tTi.Emp=tTo.Emp)
AND (tTi.timemodified>tTi.timemodified)) AS  [FINISHED]
From [Dtime] as tTo;

[NeoPa]

No. The parentheses () and brackets [] on your first line are not matched up correctly. Other than the closing parenthesis ) after timemodified though, it looks as if it could be the start that I left you with. That is of course, just the basic data you need to work from though. Not the whole job. Fix that. Try it out to see what data you're working with. See what needs doing to get your eventual required results.

BTW Indentation is not random. It is an important part of making SQL understandable (to humans - The system doesn't worry of course).

PS. You don't say if you've tried that SQL or what results you got.
PPS. Congratulations on reaching full member status.

[ilikebirds]

Thanks it worked out. Still some complications with Hour and Time calculations but we were able to find the information needed!

[NeoPa]

8-)

Glad that got you going :)