status of held Data has to complete with in the day itself.

[Anud]

HI Team,

when the status is H and it has to complete within the day itself.
expected output for below sample data is count 2 completed overall within the day.

Thanks in Advance!

Sample output below:

_time

OVERAL

DT

NUM

STAT

FM

WLM

CS

OB

EM

RC

ER

ST

2024-03-07T01:50:00.000-05:00

X

20240307

5

C

C

C

H

X

X

X

X

X

2024-03-07T03:30:10.000-05:00

X

20240307

5

C

C

C

P

X

X

X

X

X

2024-03-07T03:40:07.000-05:00

X

20240307

5

C

C

H

H

H

H

H

H

H

2024-03-07T06:10:14.000-05:00

X

20240307

5

C

C

C

I

X

X

X

X

X

2024-03-07T07:10:16.000-05:00

X

20240307

5

C

C

C

H

X

X

X

X

X

2024-03-07T07:30:17.000-05:00

X

20240307

5

C

C

C

I

X

X

X

X

X

2024-03-07T08:20:18.000-05:00

X

20240307

5

C

C

C

C

I

C

I

C

C

2024-03-07T08:30:22.000-05:00

C

20240307

5

C

C

C

C

C

C

C

C

C

2024-03-07T02:20:01.000-05:00

X

20240307

5

C

C

C

X

X

X

X

X

X

2024-03-07T03:30:10.000-05:00

X

20240307

5

C

C

C

P

X

X

X

X

X

2024-03-07T03:40:07.000-05:00

X

20240307

5

C

C

H

H

H

H

H

H

H

2024-03-07T07:10:16.000-05:00

X

20240307

5

C

C

C

H

X

X

X

X

X

2024-03-07T07:30:17.000-05:00

X

20240307

5

C

C

C

I

X

X

X

X

X

2024-03-07T08:20:18.000-05:00

X

20240307

5

C

C

C

C

I

C

I

C

C

2024-03-07T08:30:22.000-05:00

C

20240307

5

C

C

C

C

C

C

C

C

C

2024-03-07T010:30:10.000-05:00

X

20240307

5

C

C

C

P

X

X

X

X

X

2024-03-07T22:40:07.000-05:00

X

20240307

5

C

C

H

H

H

H

H

H

H

2024-03-07T22:10:16.000-05:00

X

20240307

5

C

C

C

H

X

X

X

X

X

2024-03-07T23:30:17.000-05:00

X

20240308

5

C

C

C

I

X

X

X

X

X

2024-03-07T00:20:18.000-05:00

X

20240308

5

C

C

C

C

I

C

I

C

C

2024-03-08T08:30:22.000-05:00

C

20240308

5

C

C

C

C

C

C

C

C

C

[ITWhisperer]

"when the status is H and it has to complete within the day itself." - how is this determined from the data?

[Anud]

comparing both _time and DT and the NUM (different num will be there). In the sample data i have same NUM.

[ITWhisperer]

You haven't really explained how you get to a count of 2 given your sample data. Please can you explain your process?

[Anud]

H status at 

2024-03-07T01:50:00.000-05:00

X

20240307

2024-03-07T03:40:07.000-05:00

X

20240307

C status at 

2024-03-07T08:30:22.000-05:00

C

20240307

2024-03-07T08:30:22.000-05:00

C

20240307

So here count 2 

One more H status at 

2024-03-07T22:40:07.000-05:00

X

20240307

But its not completed within the day

2024-03-08T08:30:22.000-05:00

C

20240308

[ITWhisperer]

So why not just count the C's in one day?

[Anud]

We need to know particularly about how many H status were coming to C within the day(12AM to11:59PM).

[ITWhisperer]

How do you determine what the day is because in your example DT doesn't always equate to the date shown in _time?

[ITWhisperer]

Assuming DT is the date you want to use and you already have your data in this format, try this

| untable DT category state
| where state="H" or (category="OVERAL" and state="C")
| streamstats window=1 current=f values(state) as previous by DT
| where state="C" and previous="H"
| stats count

[Anud]

Thanks for your query!
I have applied logic along with query, it working as expected.
please let me know earliest and latest logic for 12:00 AM to 11:59PM.

[ITWhisperer]

I am glad it works - what does your query about earliest and latest mean?