count break from 2 hours

[Andrew]

Hi,
by a query I get these results:

check in check out Employee nr.
16/12/08 08.00 16/12/08 13.00 A111
16/12/08 14.00 16/12/08 18.00 A111
16/12/08 08.00 16/12/08 13.30 C123
16/12/08 15.00 16/12/08 19.00 C123
16/12/08 08.00 16/12/08 13.30 C555
16/12/08 16.00 16/12/08 18.00 C555


I'm looking for get out the break time of each employee, so:
A111= 1 hour
C123= 1.30 hour
C555= 2.30 hours

have u got any idea?
please, help me.

 

[Stefan Hoffmann]

hi Andrew,

I'm looking for get out the break time of each employee, so:
A111= 1 hour
C123= 1.30 hour
C555= 2.30 hours

have u got any idea?
please, help me.

Use DateDiff() and a GROUP BY, e.g.

SELECT
Sum(DateDiff("m", [CheckIn], [CheckOut])),
[Employee]
FROM
yourQuery
GROUP BY
[Employee]



mfG
--> stefan <--

 

[Ken Sheridan]

Assuming one break, no more no less, per day, try this:

SELECT [Employee nr],
DATEVALUE([check in]) AS [Date Worked],
FORMAT(MAX([check in]) – MIN([check out]),"hh:nn") AS [Break time]
FROM [YourTable]
GROUP BY [Employee nr], DATEVALUE([check in]);

Ken Sheridan
Stafford, England

 

[Andrew]

thanks a lot Ken. and Stefan.
Tomorrow, i'm going to try it at work and i'll let u know about.
thanks again!!
--
BBB

Assuming one break, no more no less, per day, try this:

SELECT [Employee nr],
DATEVALUE([check in]) AS [Date Worked],
FORMAT(MAX([check in]) – MIN([check out]),"hh:nn") AS [Break time]
FROM [YourTable]
GROUP BY [Employee nr], DATEVALUE([check in]);

Ken Sheridan
Stafford, England

Hi,
by a query I get these results:

check in check out Employee nr.
16/12/08 08.00 16/12/08 13.00 A111
16/12/08 14.00 16/12/08 18.00 A111
16/12/08 08.00 16/12/08 13.30 C123
16/12/08 15.00 16/12/08 19.00 C123
16/12/08 08.00 16/12/08 13.30 C555
16/12/08 16.00 16/12/08 18.00 C555


I'm looking for get out the break time of each employee, so:
A111= 1 hour
C123= 1.30 hour
C555= 2.30 hours

have u got any idea?
please, help me.