Link to home
Start Free TrialLog in
Avatar of Peter Kiprop
Peter KipropFlag for Kenya

asked on

Clock in and Out

Hi Experts,

I have raw table that stores users clocking. i need a summary of the data as shown below. each In must have an out and where the user doesn't clock out it is given a dash.

UserID      AttDate      Type      Time      
10      01/11/2012      I      07:30      
10      01/11/2012      O      12:30      
10      01/11/2012      I      13:50      
10      01/11/2012      O      17:30      
11      01/11/2012      I      07:01      
11      01/11/2012      O      12:32      
11      01/11/2012      I      14:02      
                        
Expected output                        
USerID      AttDate           ClockIn      ClockOut      Hours
10             01/11/2012       07:30      12:30      5
10             01/11/2012      13:50      17:30      5hrs 40 mins
11             01/11/2012      07:01      12:32      5 hrs 31 mins
11            01/11/2012      14:02        -               0

Any help will be appreciated.

Thanks in advance
ASKER CERTIFIED SOLUTION
Avatar of Paul_Harris_Fusion
Paul_Harris_Fusion
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Peter Kiprop

ASKER

Paul,

Many thanks for the solution, it works well however i am having issues where the user clocks in say 19:20 hrs and clocks out the next day at 00:05hrs. Is there remedy for this?

Regards,
Pthepebble
Think on what's the quality of your inputtable and how it will impact on your result

Previous solution looks to do what you ask but check if that's what you want if following scenario's are possible.

UserID      AttDate      Type      Time      
10      01/11/2012      I      07:30      
10      01/11/2012      O      12:30      
10      01/11/2012      I      13:50      
10      01/11/2012      O      17:30      
10      02/11/2012      I      07:30      -- missing clocking out -> also match with 17:30?
10      02/11/2012      I      13:50      
10      02/11/2012      O      17:30      
10      03/11/2012      I      07:30      
10      03/11/2012      O      12:30      
10      03/11/2012      O      17:30     -- missing clocking in -> never being used
11      04/11/2012      I      07:01      
11      04/11/2012      O      12:32      
11      04/11/2012      I      21:02
11      05/11/2012      I      01:15     ---- clocking out next day
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Jogos,

Basing on the sample given i would appreciate if you could give me a full script just like Paul has done including the solution for the remedy.

I would really appreciate.

Regards,
Pthepebble
We have to combine the date and time as in the following SQL:

This should give you the answer you want as long as the data follows the rules!

If someone simply fails to check out,  you will get some strange answers.

This SQL does no checking for such cases.   To do that,  you would have to check that the next entry (by date order) is the appropriate type I or O.

So still some work for you,  but I think your initial query is answered.

Select UserId
, Convert (varchar(30), ClockIn, 100) as AttDate
, Convert (varchar(30), ClockOut, 100) as AttDate      
, Substring(Convert(Varchar(30), Clockout-Clockin, 114), 1,5) as Difference
From
(
      Select I.UserId, I.AttDate + I."Time" as ClockIn
      , ClockOut =
      (
       Select min (AttDate+"Time") from CheckTest O
       where "Type"='O'
       and I.UserId = O.UserId
       and I.AttDate>= O.AttDate
       and O.Time>I.Time
      )
      FROM CheckTest I
      where "Type"='I'
) Q
I repeat:  If you can post the create-script of your table a solution can be given.
Paul,

I am having a lot of trouble with the solution given above. Kindly recheck

Can the the first solution not be slightly amended.

The points are almost yours.

Many thanks,
Pthepebble.
<<I am having a lot of trouble with the solution given above. Kindly recheck
>>
Define trouble .... and could be because we don't have correct datatypes


And is 01/11/2012    january  11 or november 1?
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Experts. You guys are stars. Much appreciated
?? The Accepted "solution" does not consider a missing CheckOut at all ??
@ScottPletcher - re your last "comment" - neither does the original question
??

From the original q:
"
each In must have an out and where the user doesn't clock out it is given a dash.
"

[emphasis added]