Count first occurence of order when state is equal to a value

[HansBaeten]

Hi,

I have a dataset like this (only the first 4 columns, last column is only to indicate if I need the row in my final result or not):

Date	Time	Order	Status		To count
4/11/2019	21:00	1	Started		Yes
4/11/2019	22:00	1	Hold
4/11/2019	23:00	1	Started
5/11/2019	5:00	1	Started
5/11/2019	5:30	1	Started
5/11/2019	6:45	1	Stopped
3/11/2019	22:30	2	Started		Yes
3/11/2019	23:00	2	Started
4/11/2019	1:30	2	Started
4/11/2019	3:00	2	Started
3/11/2019	23:00	3	Started		Yes
4/11/2019	1:30	3	Started
4/11/2019	21:00	4	Started		Yes
4/11/2019	22:00	4	Hold
4/11/2019	23:00	4	Started
5/11/2019	5:00	4	Started
5/11/2019	5:30	4	Started
5/11/2019	6:45	4	Stopped

I want to be able to count the number of orders by day, but only the first occurence of the order + when the state = Started. The desire result should look like:

Date	Count
3/11/2019	2
4/11/2019	2
5/11/2019	0

Who can help me?

Thanks.

Regards,

Hans

[v-yuta-msft]

@HansBaeten ,

Could you please explain the logic of "first occurence of the order"?

Regards,

Jimmy Tao

[HansBaeten]

@v-yuta-msft,

I only want to count records where the state "started" appeared for the first time on an order level.

In my example, state "started" appeared for the first time at 4/11/2019 21:00 for order 1. From this moment on, I'm no longer interested in the other states "started" of the same order.

Actually, I want to see when an order was actually started. The order keeps this state until there is a change of state.

In case of order 1: if I do a simple count by day, I will have a result on 4/11 and 5/11 (both days are having a state started). But an order can be started only once.

Hope this clarifies my requirement?

Thanks in advance for your help.

Regards,

Hans