How to sum and calculate time?

[#1]

Hi all, I have this data, and would like to sum the time worked. P_TYPE = 1 is for when You sign in, and P_TYPE = 2 is for signing out for breaks or end of day.  so my question is hos to do this?

Thanks in advance!

[Gysbert_Wassenaar]

As long as you have the same number of check ins and check outs per day you can use rangesum(sum({<P_TYPE={2}>}P_TIME),-sum({<P_TYPE={1}>}P_TIME))

[MK_QSL]

Use Below Script.

Temp:

Load

  Time#(P_TIME,'hh:mm') as P_TIME,

  P_TYPE,

  If(P_TYPE = 1 , 'Sign In', 'Sign Out') as Flag,

  Date(Date#(%Key_date,'YYYY-MM-DD')) as %Key_date

Inline

[

  P_TIME, P_TYPE, %Key_date

  05:52, 1, 2014-10-29

  09:00, 2, 2014-10-29

  09:32, 1, 2014-10-29

  12:02, 2, 2014-10-29

  12:29, 1, 2014-10-29

  15:00, 2, 2014-10-29

];

Final:

Load

  P_TIME,

  If(P_TYPE = 1 and Previous(P_TYPE) = 2, Interval(P_TIME - Previous(P_TIME),'hh:mm')) as Diff,

  P_TYPE,

  Flag,

  %Key_date

Resident Temp

Order By P_TIME;

Drop table Temp;

Use below expression to get the Net Working Hours

=Interval(Interval(Max(P_TIME)-Min(P_TIME),'hh:mm') - SUM(Diff),'hh:mm')

[simondachstr]

Without a unique identifier e.g. worksessionid, how are you identifying the right P_TYPE=2 to be subtracted by the respective P_TYPE=1 values?

e.g. 15:00 (which is the last row in the table).. will you be subtracting 5:52, 9:32 or 12:29?

[Gysbert_Wassenaar]

Example data:

check-in, check-out

1,     2,

3,     4

2-1=1

4-3=1

total = 1+1 = 2

or

2-3=-1

4-1=3

total = -2+3 =2

or sum of check-outs minus sum of check-ins:

4+2=6

3+1=4

total = 6-4 = 2

Conclusion: it does not matter as long as the number of check-ins is the same as the number of check-outs.

[simondachstr]

I agree the totals will be the same, but if you want to sum (or calculate) the hours worked between check-in and check-out for each working session (e.g. You want to flag the people who work more than 4 hours) then your example will not work. But this, of course, depends on the requirement.

[#7]

I have this field NR also. so you could basicly say, that nr 2-1 and nr 4-3 and 6-5 and so on. if that helps?

[its_anandrjs]

Hi Rickard,

If you want to calculate the total duration was attempted by the employee so with the help of Interval we can calculated the In and Out time difference and then sum total hour attempted. Have a look the script of that

Source:

LOAD Time#(P_TIME,'hh:mm') as P_TIME,P_TYPE,%Key_date,RowNo() as Rowid;

LOAD * Inline

[

P_TIME,P_TYPE,%Key_date

05:52,1,2014-10-29

09:00,2,2014-10-29

09:32,1,2014-10-29

12:02,2,2014-10-29

12:29,1,2014-10-29

15:00,2,2014-10-29

];

Final:

LOAD

%Key_date,Rowid, P_TIME as [P Time],

if(P_TYPE=1,P_TIME) AS InTime,

if(P_TYPE=2,P_TIME) AS OutTime,

If(Previous(P_TYPE) = 1 and P_TYPE = 2, Interval(P_TIME - Previous(P_TIME),'hh:mm'))  AS DIFF

Resident Source;

DROP Table Source;

And then in the front end in straight table take

Dimension:-  %Key_date

Expression:- Sum(DIFF)

And you get chart as

The total hours for date 214-10-29 is 08:09 is it correct

Regards

Anand

[#9]

as you can see my DIFF isnt correct, but everything else seems to be, what do I need to change?

[MK_QSL]

have you tried my solution?