I am struggling to identify a way to report on stories from this database view where the state went back into draft once progressed to a state of Approved by PO?
NUMBER DEFINITION VALUE DURATION
STRY0000001 Story State Duration DRAFT 05 min
STRY0000001 Story State Duration AWAITING APPROVAL 10 min
STRY0000001 Story State Duration APPROVED BY PO 15 min
STRY0000001 Story State Duration DRAFT
STRY0000002 Story State Duration DRAFT 05 min
STRY0000002 Story State Duration AWAITING APPROVAL 10 min
STRY0000002 Story State Duration APPROVED BY PO
Using the mock up data above based on the criteria provided....
- STRY0000001 = PASS (DRAFT > AWAITING APPROVAL > APPROVED BY PO > DRAFT)
- STRY0000002 = FAIL (DRAFT > AWAITING APPROVAL > APPROVED BY PO)
I have no issue creating the database view to match the mock up data format.
My struggle is identifying only those stories that satisfy the criteria using the database view as a source for a report?
The basic logic I need is...
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
Any thoughts?
