how to split the time into different shifts to find how many employees came in shift 1 and shift2

[Jyothik]

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

[woodcock · Accepted solution]

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

[Jyothik]

thank you woodcock