we have a data with employee numbers who enter the office during different times in the day.
We want to categorize employees coming between 6:30 to 12:30 as Shift1 and 12:31 to 5 PM as Shift 2 and display stats of number of employees coming in Shif1 and Shift2
How to write a query for the same,
Sample Data:
Emp No Time
100 6:30
101 8:00
102 14:00
Regards,
Jyothi
Like this:
Your Base Search Here
| eval hourmin=Time
| rex field=hourmin mode=sed "s/://"
| eval Shift=case((hourmin>=630 AND hourmin<1230), "Shift1",
(hourmin>=1230 AND hourmin<1700), "Shift2",
true(), "N/A")
| stats dc(Emp) BY Shift
Like this:
Your Base Search Here
| eval hourmin=Time
| rex field=hourmin mode=sed "s/://"
| eval Shift=case((hourmin>=630 AND hourmin<1230), "Shift1",
(hourmin>=1230 AND hourmin<1700), "Shift2",
true(), "N/A")
| stats dc(Emp) BY Shift
thank you woodcock