Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Query for Emp Time Logging Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, June 14, 2013 9:04 PM
 Say Hey Kid Group: General Forum Members Last Login: Yesterday @ 4:37 AM Points: 673, Visits: 1,387
 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 I002 Alice 9:43 I001 Alen 9:49 O003 Bitchel 9:54 I002 Alice 12:03 O003 Bitchel 12:04 OThe required result is like this.ID Name Duration001 Alen 0:54 002 Alice 2: 20 003 Bitchel 2:10
Post #1463807
 Posted Saturday, June 15, 2013 10:07 AM
 SSC Veteran Group: General Forum Members Last Login: Monday, November 21, 2016 5:59 AM Points: 202, Visits: 776
 ID Name LoggedTime In/Out---- -------- ---------------- ---------001 Alen 9:00 I002 Alice 9:43 I001 Alen 9:49 O003 Bitchel 9:54 I002 Alice 12:03 O003 Bitchel 12:04 OThe required result is like this.ID Name Duration001 Alen 0:54002 Alice 2: 20003 Bitchel 2:10It'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 Group: General Forum Members Last Login: Thursday, November 24, 2016 7:13 AM Points: 2,192, Visits: 6,490
 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 Veteran Group: General Forum Members Last Login: Monday, November 21, 2016 5:59 AM Points: 202, Visits: 776
 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 Group: General Forum Members Last Login: Thursday, November 24, 2016 7:13 AM Points: 2,192, Visits: 6,490
Post #1463934
 Posted Sunday, June 16, 2013 4:37 AM
 Say Hey Kid Group: General Forum Members Last Login: Yesterday @ 4:37 AM Points: 673, Visits: 1,387
 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 Group: General Forum Members Last Login: Thursday, November 24, 2016 7:13 AM Points: 2,192, Visits: 6,490
 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

 Permissions