Count every time status changes

[EZiamslow]

Hi,
I need help with calculation every time tool status changes. I have many tool IDs set in the same table similar to the one below. What would be the best way to count where every time Availability column changes.

Sample Data Set:

Tool	DateTime	Availability	Count
AA1	8/20/22 11:49 PM	D	1
AA1	8/20/22 10:08 PM	U
AA1	8/20/22 2:08 PM	U
AA1	8/19/22 7:11 PM	D	1
AA1	8/18/22 9:06 PM	D
AA1	8/18/22 8:15 PM	U
AA1	8/18/22 6:15 PM	D	1
BB1	8/20/22 11:08 PM	D	1
BB1	8/20/22 10:08 PM	D
BB1	8/20/22 6:45 PM	U
BB1	8/20/22 5:12 PM	D	1
BB1	8/20/22 2:08 PM	U
BB1	8/19/22 7:11 PM	D	1
BB1	8/18/22 9:06 PM	U
BB1	8/18/22 8:15 PM	U
BB1	8/18/22 6:15 PM	D	1

[Ahmedx]

pls try this

[EZiamslow]

It works but it's taking a long time to load 800k+ rows for the index column. Is there an alternative way to work on this?

[Ahmedx · Accepted solution]

And try this

[Ahmedx]

pls trythis

[Ahmedx]

write what result you expect

[EZiamslow]

@Ahmedx 

Tool	DateTime	Availability	Count
AA1	8/20/22 11:49 PM	D	1
AA1	8/20/22 10:08 PM	U
AA1	8/20/22 2:08 PM	U
AA1	8/19/22 7:11 PM	D	1
AA1	8/18/22 9:06 PM	D
AA1	8/18/22 8:15 PM	U
AA1	8/18/22 6:15 PM	D	1
BB1	8/20/22 11:08 PM	D	1
BB1	8/20/22 10:08 PM	D
BB1	8/20/22 6:45 PM	U
BB1	8/20/22 5:12 PM	D	1
BB1	8/20/22 2:08 PM	U
BB1	8/19/22 7:11 PM	D	1
BB1	8/18/22 9:06 PM	U
BB1	8/18/22 8:15 PM	U
BB1	8/18/22 6:15 PM	D	1

Based on the sample above, I'd like to see AA1 = 3 and BB1 = 4.

[Ahmedx]

The logic of the calculation is not yet clear to me.
tell me why these changes were not counted

[EZiamslow]

This is related to tool status up and down.

D = Down

U = Up

From the sample data I provided.

Tool	DateTime	Availability	Count
AA1	8/20/22 11:49 PM	D	1
AA1	8/20/22 10:08 PM	U
AA1	8/20/22 2:08 PM	U
AA1	8/19/22 7:11 PM	D	1
AA1	8/18/22 9:06 PM	D
AA1	8/18/22 8:15 PM	U
AA1	8/18/22 6:15 PM	D	1
BB1	8/20/22 11:08 PM	D	1
BB1	8/20/22 10:08 PM	D
BB1	8/20/22 6:45 PM	U
BB1	8/20/22 5:12 PM	D	1
BB1	8/20/22 2:08 PM	U
BB1	8/19/22 7:11 PM	D	1
BB1	8/18/22 9:06 PM	U
BB1	8/18/22 8:15 PM	U
BB1	8/18/22 6:15 PM	D	1

Any "D" that are together only count as one same for "U".

If I'm looking at AA1, I have 3 times tool down and 2 times tool up.

As for BB1, I have 4 times tool down and 3 times tool up.

Data is usually added when people give updates so that's why we have multiple rows with D and U together.

It doesn't matter which one I'm counting within the group. I just need to count one if they are together.