June 14, 2013 at 9:04 pm
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
June 15, 2013 at 10:07 am
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'
June 15, 2013 at 4:17 pm
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.
June 16, 2013 at 12:51 am
You are right. I did not even recognise this mistake. So the question is where the 54 minutes come from?
June 16, 2013 at 4:09 am
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
June 16, 2013 at 4:37 am
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:-P
June 16, 2013 at 5:01 am
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply