Burning out a brain cell... there has to be a simple way to do this.
I've inherited the following tables:
approval_path
approval_path_steps
applications
application_roles
requests
role_approvals
A user requests
an application role
for an application
, which must go through an approval path
, the steps of which are defined in approval_path_steps
. The approval history for each step of the approval path is stored in role_approvals
. So:
approval_path:
-> (p)approval_path_id
|
-------------------------
|
approval_path_steps: |
(p)approval_path_id --|
--> (p)sequence_nbr |
| approver |
| |
| |
| applications: |
| -> (p)application_id |
| | approval_path_id --
| |
| -------------------------
| |
| application_roles: |
| -> (p)role_id |
| | application_id ---
| |
| -------------------------
| |
| requests: |
| -> (p)request_nbr |
| | role_id ---
| | requestor
| |
| -------------------------
| |
| role_approvals: |
| (p)request_nbr ---
---- (p)sequence_nbr (NOT ACTUALLY KEYED!!! ENTERED MANUALLY!!)
approver
status
where (p)
indicates the primary key. Fields not immediately relevant have been omitted. (btw, this was not my design)
The problem: Approval path steps have changed over time for a given approval path; steps have been added, removed, or changed from one approver to another. Therefore, the approval_path_steps
that were actually taken for a request don't match the approval_path_steps
that are currently defined for the requested role's approval_path
.
What I need: I need to query the role_approvals
table in such a way that I can list the distinct paths that were used. So:
role_approvals
--------------
1000 role1 1 manager approved
1000 role1 2 hr_mgr approved
1000 role1 3 app_owner approved
1001 role1 1 manager approved
1001 role1 2 hr_mgr approved
1001 role1 3 app_owner approved
1002 role1 1 app_owner approved
1002 role1 2 manager approved
The results I want:
id seq_nbr approver
-- ------- --------
1 1 manager
1 2 hr_mgr
1 3 app_owner
2 1 app_owner
2 2 manager
where 'id' can be calculated in some identifying way, it doesn't matter how, to identify that unique approval path that was taken.
Any ideas?
Thanks in advance! James