how to find distinct duration

[#1]

hi experts

i have some data and did some calculation in script like

Data:

LOAD *,Date(Floor(TimeData)) as Date;

LOAD id,

     EmpID as [Employee id],

     EventID,

     TimeData

FROM

(qvd);

Temp:

LOAD *,

if([Employee id]=Previous([Employee id]) and EventID=0 and Date=Previous(Date),

Interval(TimeData-Previous(TimeData),'hh:mm')) as Break

Resident Data  Order by [Employee id],TimeData;

LOAD [Employee id],Date,

       Interval(Max(TimeData)-min(TimeData)-Sum(Break),'hh:mm') as Duration

Resident Temp Group By [Employee id],Date;

DROP Table Temp;

and i am getting duration as shown in screen shot

here in 6:00 and 6:30 values are repeating i want only distinct values how can i do this

[jonathandienst · Accepted solution]

They are repeating because they differ by small amounts (less than a minute). Interval does not round them off, it only formats - so change the load line to:

Interval(Round(Max(TimeData)-min(TimeData)-Sum(Break), 1/(24*60)),'hh:mm') as Duration

to round the results to the nearest minute. Or, for the nearest 10 minutes:

Interval(Round(Max(TimeData)-min(TimeData)-Sum(Break), 10/(24*60)),'hh:mm') as Duration