7 Replies Latest reply: Jun 23, 2015 6:43 AM by jagan mohan rao appala

# Calculating Employees Available at Interval

Hi All ,

Requirement is need to get the count of Employee based on the shift start , End TIme and Break Time.

Input:

 PS ID Date [Log Type] [Start Time] [End Time] 123 6/1/2015 LogIn/LogOff 04/01/2015 05:00:00:000 04/01/2015 15:00:00:000 145 6/1/2015 LogIn/LogOff 04/01/2015 09:00:00:000 04/01/2015 18:30:00:000 178 6/1/2015 LogIn/LogOff 04/01/2015 13:00:00:000 04/01/2015 21:00:00:000 123 6/1/2015 Break 04/01/2015 08:00:00:000 04/01/2015 8:30:00:000 145 6/1/2015 Break 04/01/2015 11:00:00:000 04/01/2015 11:15:00:000 178 6/1/2015 Break 04/01/2015 17:00:00:000 04/01/2015 17:30:00:000

Login/Logoff - > Shift start and end of each employee

Break  - > Break should be subtracted based on the start and End Time.

Expected Output :

 Interval Count 0 -1 0 1 -2 0 2-3 0 3-4 0 4-5 0 5-6 1 6-7 1 7-8 1 8-9 0.5 9-10 2 10-11 2 11-12 1.75 12-13 2 13-14 3 14-15 3 15-16 2 16-17 2 17-18 1.5 18-19 1.5 19-20 1 20-21 1 21-22 0 22-23 0 23-24 0
• ###### Re: Calculating Employees Available at Interval

Looks like you are in need of the IntervalMatch() function.  This blog post by HIC explains it well.

You just need to have the interval as Hours.

• ###### Re: Calculating Employees Available at Interval

I think No. Interval Match wont work for it.

I need to find the count of employees who were available during that particular slot

• ###### Re: Calculating Employees Available at Interval

I use the IntervalMatch() function to do just that and it works perfectly.

• ###### Re: Calculating Employees Available at Interval

Can you share the qvw file?

• ###### Re: Calculating Employees Available at Interval

Hi,

Try this script

Temp:

*,

Num(Hour(StartTime)) AS StartHour,

Num(Hour(EndTime)) AS EndHour,

Minute(StartTime) AS StartMinute,

Minute(EndTime) AS EndMinute;

[PS ID],Date([Date]) AS Date, [Log Type], TimeStamp(TimeStamp#([Start Time], 'MM/DD/YYYY hh:mm:ss:fff'), 'MM/DD/YYYY hh:mm:ss') AS StartTime,

TimeStamp(TimeStamp#([End Time], 'MM/DD/YYYY hh:mm:ss:fff'), 'MM/DD/YYYY hh:mm:ss') AS EndTime

INLINE [

PS ID, Date, Log Type, Start Time, End Time

123, 6/1/2015, LogIn/LogOff, 04/01/2015 05:00:00:000, 04/01/2015 15:00:00:000

145, 6/1/2015, LogIn/LogOff, 04/01/2015 09:00:00:000, 04/01/2015 18:30:00:000

178, 6/1/2015, LogIn/LogOff, 04/01/2015 13:00:00:000, 04/01/2015 21:00:00:000

123, 6/1/2015, Break, 04/01/2015 08:00:00:000, 04/01/2015 8:30:00:000

145, 6/1/2015, Break, 04/01/2015 11:00:00:000, 04/01/2015 11:15:00:000

178, 6/1/2015, Break, 04/01/2015 17:00:00:000, 04/01/2015 17:30:00:000];

[PS ID], Date,

[Log Type],

StartHour + IterNo() - 1  AS Hour,

If([Log Type] = 'Break',

If(EndMinute > 0 AND StartHour + IterNo() - 1 = EndHour, -EndMinute/60, -1),

If(StartMinute > 0 AND IterNo() = 1, StartMinute/60, 1)) AS Value

RESIDENT Temp

WHILE StartHour + IterNo() - 1 <= EndHour;

Regards,

Jagan.

• ###### Re: Calculating Employees Available at Interval

HI Jagan

Can you share your file

• ###### Re: Calculating Employees Available at Interval

Hi,

Please find attached file for solution.

Regards,

Jagan.