I have some data where a user submits change requests and an approver approves or sends back for more information. The table doesn't have any parent/child relationships set up so it can get a bit messy visually and hard to keep track of that "parent" request vs the "children" because a parent request can involve several child submissions depending on if the approver sent the request back for more information.
Here is a sample of the data. Sample Data. What I'd like to see is a parent submission ID(status = SUBMITTED), parent action ID (status = REQUESTING MORE INFORMATION or 'PENDING ACTIVATION') and the child requests (all the requests and actions inside the parent)
There are instances where a submitter will request multiple changes to a group and the approver will approve multiple requests. There is no way to tie the approval to the original request in this instance so I'd like to see the last action as it will be treated as a single request.
This is what I have tried so far.
with test as (
select
request_id
, request_date_time
, group_id
, status
, sum(case when status IN ('ACTIVE','PENDING ACTIVATION') then 1 else 0 end) over (partition by group_id order by request_date_time) as group_id
, sum(case when status IN ('SUBMITTED') then 1 else 0 end) over (partition by group_id order by request_date_time) as submission_id
, min(case when status IN ('SUBMITTED') then request_id end) over (partition by group_id order by request_date_time) as submission_request_id
from requests
order by group_id, request_date_time
)
, test2 as (
select request_id
, request_date_time
, group_id
, status
, row_number() over (partition by group_id, group_id order by group_id asc, request_date_time) as group_rank
, row_number() over (partition by group_id, submission_id order by request_date_time) as request_rank
FROM test
order by group_id, request_date_time
select *
from test2 a
order by a.group_id, a.request_id
Group rank is close but it restarts on ACTIVE and I would like it to restart on the status directly after that.
request rank is also close but I run into issues with PENDING SUBMISSION as it should be lumped in with the SUBMISSION status but doesn't always appear in the workflow when a requester submits.
What I would really like is to see the parent and child request IDs not really the ranks but I don't really know how to accomplish that.
Little background on how the flow works.
SUBMITTED or PENDING_SUBMISSION start the flow
Approver can either approve or request more information
if approver requests more info the status will appear REQUESTING MORE INFORMATION and will queue the requester to submit again with correct info. This can happen several times during a single request.
if approver approves the status will appear as PENDING ACTIVATION or sometimes just ACTIVE. either one would indicate an ending status but PENDING ACTIVATION is the preferred request id as it indicates the approver hit the approve button.
PENDING ERP SEND is an automated status that doesn't always get triggered. It would be lumped in with the request but wouldn't indicate an ending status even though it sometimes is triggered after the ending status of ACTIVE or PENDING ACTIVATION.
