Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query for Emp Time Logging Expand / Collapse
Author
Message
Posted Friday, June 14, 2013 9:04 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 5:07 AM
Points: 609, Visits: 1,135
This is a question asked in a test.

There is a EmpWorkTime table having the columns and data as shown.

ID Name LoggedTime In/Out
---- -------- ---------------- ---------
001 Alen 9:00 I
002 Alice 9:43 I
001 Alen 9:49 O
003 Bitchel 9:54 I
002 Alice 12:03 O
003 Bitchel 12:04 O

The required result is like this.

ID Name Duration
001 Alen 0:54
002 Alice 2: 20
003 Bitchel 2:10
Post #1463807
Posted Saturday, June 15, 2013 10:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:55 PM
Points: 199, Visits: 735
ID Name LoggedTime In/Out
---- -------- ---------------- ---------
001 Alen 9:00 I
002 Alice 9:43 I
001 Alen 9:49 O
003 Bitchel 9:54 I
002 Alice 12:03 O
003 Bitchel 12:04 O

The required result is like this.

ID Name Duration
001 Alen 0:54
002 Alice 2: 20
003 Bitchel 2:10


It's not very much information we have. Let's say there is a uniqueness of Id and In/Out, you could do something like this:
select
i.ID,
i.Name,
Duration = datediff( minute, i.LoggedTime, o.LoggedTime )
from EmpWorkTime i
inner join EmpWorkTime o on i.ID = o.ID and o.[In/Out] = 'O'
where [In/Out] = 'I'

Post #1463868
Posted Saturday, June 15, 2013 4:17 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:23 PM
Points: 2,116, Visits: 6,439
Looking (or rather guessing) at the problem statement, Wolfgang's query should do the right thing, but it wont give the right result for Alen since it will show 49 minutes and not the 54 as required.


--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1463911
Posted Sunday, June 16, 2013 12:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:55 PM
Points: 199, Visits: 735
You are right. I did not even recognise this mistake. So the question is where the 54 minutes come from?
Post #1463928
Posted Sunday, June 16, 2013 4:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:23 PM
Points: 2,116, Visits: 6,439
That could be easily fudged with a CASE around the selection of the Duration column. But I don't think that's the OP's intention.

So, George, you say this is a question from a test. Can you show us what you have come up with so far? Give us a set of table creation statements, test data insert statements (for both read the link in my signature), the EXACT results you expect from the query, plus of course what you have tried so far. We'll gladly help you on the way, but remember we're not here to solve your exam questions, but rather to help you learn how you can get on the way to solve your issue by yourself.

Regards,
Jan


--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1463934
Posted Sunday, June 16, 2013 4:37 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 5:07 AM
Points: 609, Visits: 1,135
You are right..Jan Van der Eecken, Its my mistake.
What I really wanted is the query written by Wolfgang.
The duration I have written there is wrong, its only an indicator anyway.
Thank You Wolfgang for that query and Jan Van der Eecken for correcting me
Post #1463938
Posted Sunday, June 16, 2013 5:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:23 PM
Points: 2,116, Visits: 6,439
No problem. Glad to hear Wolfgang's query does the thing you want it to do. Just a few more issues. Do your time intervals ever run past midnight? Or is it possible that there is more than one check-in/check-out time per employee per day? That would complicate matters.


--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1463942
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse