Calculate Tasks Where State Reverted

[jacobspacek]

How would you calculate how many stories went back into draft once progressed to a state of Approved by PO?

Example

1) story created with state = "Draft"

2) story state updated = "Awaiting Approval"

3) story state updated = "Approved by PO"

4) story state updated = "Draft"

Goal = report on how many stories went back into draft once progressed to a state of Approved by PO?

While this example is specific my question is aimed at understanding if it is possible to track any tasks that progressed from state1 -> state2 -> state1.

[Dr Atul G- LNG]

DB view will help you to get the stories number and other filed on story table.

[jacobspacek]

I have mocked up an example below from my database view.

NUMBER		DEFINITION		VALUE			DURATION
STRY#######	Story State Duration	DRAFT			5 min
STRY#######	Story State Duration	AWAITING APPROVAL	5 min
STRY#######	Story State Duration	APPROVED BY PO		5 min
STRY#######	Story State Duration	DRAFT

This example is for one single story.

I do not understand how Service Now will allow me to use this view to count "how many stories went back into draft once progressed to a state of Approved by PO?"

The database view will contain many different stories.

Not all stories will satisfy this criteria, I am struggling to figure out how to filter or slice this data to identify only those stories that satisfy the criteria?

In theory this criteria would provide the correct results I am struggling to find  a way to implement this logic.

SELECT ALL stories from db_view WHERE
- for a single story... count of value = "draft" > 1
- for a single story... count of value = "APPROVED BY PO" >= 1

Would I need to create an additional database view on top of my other database view?