Search code examples
sql-serverworkflowpowerquerysummary

Summary of Workflow History over Time

I am collecting data using a web formular and a workflow with four different status.

  1. Started
  2. Review by Admin
  3. Back to User
  4. Finished

Status 2 and 3 are repeated as long as it is needed. The application logs the history of this process and I would like to be able so see the development of the workflow over time.

The history is stored in an MS SQL Table and I can rearange it to look like this.

enter image description here

In the example there are two workflows (id 1 and 2) and over time the formular goes back and forth untill it is finished. I can order this by time enter image description here

to derive the result I would like to have.

enter image description here

I dont need to have this result in wide format as long as the counting in long is correct.

I am struggling to find a way to avoid double counting status 2 and 3 and also I dont want to count status 1 later on.

I am rather looking for a strategy to obtain my desired results either in SQL or Power Query. I tried different grouping, dense_rank and other transformations in Power Query.

Solution

  • Try this in PowerQuery, assumes already date sorted

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Datum", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "zStarted", each if [Status]="Started" then 1 else if [Status]="Review by Admin" then -1 else 0),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "zReview", each if [Status]="Review by Admin" then 1 else if [Status]="Back to User" then -1 else if [Status]="Finished" then -1 else 0),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "zBackA", each if [Status]="Back to User" then 1 else null),
#"Filled Down" = Table.FillDown(#"Added Custom2",{"zBackA"}),
#"Added Custom3" = Table.AddColumn(#"Filled Down", "zBackB", each if [zBackA]=null then 0 else if [Status]="Back to User" then 1 else if [Status]="Review by Admin" then -1 else 0),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "zFinished", each if [Status]="Finished" then 1 else 0),
#"Added Index" = Table.AddIndexColumn(#"Added Custom4", "Index", 0, 1, Int64.Type),
// cumulative sum, converting to zero if <0
#"Cum1" = Table.AddColumn(#"Added Index", "Started", each List.Max(List.Combine({{0}, {List.Sum(List.FirstN(#"Added Index"[zStarted],[Index]+1))}}))),
#"Cum2" = Table.AddColumn(#"Cum1", "Review By Admin", each List.Max(List.Combine({{0}, {List.Sum(List.FirstN(#"Added Index"[zReview],[Index]+1))}}))),
#"Cum3" = Table.AddColumn(#"Cum2", "Back to User", each List.Max(List.Combine({{0}, {List.Sum(List.FirstN(#"Added Index"[zBackB],[Index]+1))}}))),
#"Cum4" = Table.AddColumn(#"Cum3", "Finished", each List.Max(List.Combine({{0}, {List.Sum(List.FirstN(#"Added Index"[zFinished],[Index]+1))}}))),
#"Removed Columns" = Table.RemoveColumns(Cum4,{"ID","zStarted", "zReview", "zBackA", "zBackB", "zFinished", "Index","Status"})
in  #"Removed Columns"

    enter image description here

    ID Datum Status
    1 06/24/22 Started
    2 06/25/22 Started
    1 06/26/22 Review by Admin
    2 06/27/22 Review by Admin
    1 06/28/22 Back to User
    1 06/29/22 Review by Admin
    2 06/30/22 Back to User
    1 07/01/22 Back to User
    1 07/02/22 Review by Admin
    2 07/03/22 Review by Admin
    1 07/04/22 Finished
    2 07/05/22 Back to User
    2 07/06/22 Review by Admin
    2 07/07/22 Finished