Time Comparaison

[mario-sarkis]

Dear,

1- I have a time fields for example

Punch IN: 07:35

I need to create a field when the field is less then 07:45 in to assign the 07:45 instead of 07:35

Out Put:

Punch IN: 07:45

Punch IN : 10:00

I need to create a field when the field is grater then 09:00 in to assign the 09:00 instead of 10:00

Out Put:

Punch IN: 07:45

2- I need subtract two time fields Example:

08:30 - 07:45 = 45 minutes

08:30 - 09:00 = -30 minutes

Hope you can help

Thanks

[Anonymous]

Sample:

LOAD * INLINE [

    IN, OUT

07:35, 17:00

07:45, 17:00

07:55, 17:00

10:00, 17:50

10:50, 17:50

09:00, 17:50

];

ProcessSample:

Load *

,interval(Reference830-PunchIN745,'hh:mm') as Field745

,interval(Reference830-PunchIN900,'hh:mm')as Field900

;

Load *

, if(PunchDec  < Reference745, Reference745,

PunchDec) as PunchIN745

,if(PunchDec  > Reference900, Reference900,PunchDec) as PunchIN900

,if(PunchDec  < Reference745, Reference745,

if(PunchDec  > Reference900, Reference900,PunchDec)) as PunchIN

;

Load

*

,

Interval(IN,'hh:mm') AS PunchDec

,Interval(Interval#('08:30','hh:mm'),'hh:mm') AS Reference830

,Interval(Interval#('07:45','hh:mm'),'hh:mm') AS Reference745

,Interval(Interval#('09:00','hh:mm'),'hh:mm') AS Reference900

Resident Sample;

Drop Table Sample;