To solve the current problem at hand I tried to apply the ranking idea to it, this sort of worked however it does not handle certain cases which I am having trouble coding for. So any help will be appreciated. Here's the problem, I have an old table that we use for project approvals, I am in the process of converting this table to a more robust and scalable version. Here's the vital old portion. workflow - project_id - user_id - step - action - date_processed
- next_state - prev_stateThe new version is these essential columns (there are about 15 other columns in the old table that are used for the application to track the workflow. In the new one this tracking is eliminated and combined. Here's some sample data from the old one.project_id user_id step action date_processed next_state prev_state---------- ------- ---- ------ ------------------- ---------- ----------1 1 1 a 04/01/2002 05:35:00 2 NULL1 2 2 a 04/01/2002 06:00:00 3 11 3 3 a 04/01/2002 06:05:00 NULL 2
It repeats like this for almost every project, has a user, a step level for the user's interaction and a date they either approved or rejected the project. We are just dealing with the approved projects right now. The ranking works well for this (you ask why use ranking algorithm to get the step when the step is already in order, well not in all of them. The step may start at 5 then jump to 7 then to 50 and such. (Bad programming on creators part) this is why I used the ranking algortihm. Now however there are instances where users may be grouped into the same step. Also users may request more information and the step goes back to the first step and after first step approves it it goes back to the step that requested more info. example:project_id user_id step action date_processed next_state prev_state---------- ------- ---- ------ ------------------- ---------- ----------1 1 1 a 04/01/2002 05:35:00 2 NULL1 2 2 rmi 04/01/2002 06:00:00 3 11 1 1 a 04/01/2002 06:15:00 2 NULL1 2 2 a 04/01/2002 06:20:00 3 11 3 3 a 04/01/2002 06:35:00 NULL 2
The step goes back to 1 and then continues on so say step 7 requested more info ... after that would be step 1 then step 7 again and continue on. The did the rankings by date_processed and this works fine except for the part where there are groups of users in a single step. example:project_id user_id step action date_processed next_state prev_state---------- ------- ---- ------ ------------------- ---------- ----------1 1 1 a 04/01/2002 05:35:00 2 NULL1 2 2 a 04/01/2002 06:00:00 3 11 3 2 a 04/01/2002 06:15:00 3 11 4 3 a 04/01/2002 06:20:00 4 21 2 4 a 04/01/2002 06:35:00 NULL 3
Here is the query I'm working with right now.select (select count(*) + 1 from STARS.stars.dbo.tworkflow as wf where wf.ckproject = tworkflow.ckproject and wf.cdefault = 0 and wf.cdtprocessed < tworkflow.cdtnotified) as ranked_step, tproject.ckproject as project_id, users.user_id, tworkflow.ckstate as workflow_step, project_approval_actions.project_approval_action_id as action_id, tworkflow.ccomments as comment, tworkflow.cdtnotified as date_notified, tworkflow.cdtprocessed as date_processed, tworkflow.cdtcreate as date_created, creator.user_id as creator, tworkflow.cdtupdate as date_updated, updator.user_id as updator from STARS.stars.dbo.tproject as tproject inner join STARS.stars.dbo.tworkflow as tworkflow on tproject.ckproject = tworkflow.ckproject inner join users on tworkflow.cactor = users.user_name inner join STARS.stars.dbo.taction as taction on tworkflow.ckaction = taction.ckaction inner join project_approval_actions on taction.cname = project_approval_actions.name left join users as creator on tworkflow.ccreateby = creator.user_name left join users as updator on tworkflow.cupdateby = updator.user_name where tproject.clstatus in (11, 12, 47, 56) -- the approved workflows and tworkflow.cdefault = 0 -- only the actual workflows order by tworkflow.cdtprocessed
This code can get as ugly as it wants since it will only run once to import all the workflows. After that it will not be used again. If you need more information or a more detailed table schema just let me know.Thanks!
Ranking Problem
Posted - 2002-04-01 : 09:37:41
