SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query for Emp Time Logging


Query for Emp Time Logging

Author
Message
VSSGeorge
VSSGeorge
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1026 Visits: 1494
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
WolfgangE
WolfgangE
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 793
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'


Jan Van der Eecken
Jan Van der Eecken
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2458 Visits: 6494
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)
WolfgangE
WolfgangE
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 793
You are right. I did not even recognise this mistake. So the question is where the 54 minutes come from?
Jan Van der Eecken
Jan Van der Eecken
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2458 Visits: 6494
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)
VSSGeorge
VSSGeorge
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1026 Visits: 1494
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
Jan Van der Eecken
Jan Van der Eecken
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2458 Visits: 6494
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search