November 22, 2008 at 4:49 pm
I have a table that tracks the progress of steps in our sales process. Our process allows for sales reps to go backwards in the process and “reopen” previous closed steps because sometimes it is possible that they may have moved ahead in the process prematurely. We have several reports and functions that look at this data and determine the amount of time taken in each step. Up until now, the users were fine with just measuring the most recent time that a step has been open. Now, they want to change that and measure the full amount of time each step has been open when they go backwards.
Here is code to build a temporary table representative of the table we are using as well as five sets of sample data.
CREATE TABLE #op_milestones(
mileid int IDENTITY(1,1) NOT NULL,
opid int NOT NULL,
mile_code char(25) NOT NULL,
mile_status char(15) NULL,
seq int NULL,
first_open_date datetime NULL,
first_open_rep char(20) NULL,
open_date datetime NULL,
open_rep char(20) NULL,
first_Close_date datetime NULL,
first_Close_rep char(20) NULL,
Close_date datetime NULL,
Close_rep char(20) NULL,
pass int NULL,
CONSTRAINT PK__#op_milestones__3864608B PRIMARY KEY CLUSTERED
(
mileid ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT #op_milestones ON
GO
INSERT into #op_milestones (mileid, opid, mile_code, mile_status, seq, first_open_date, first_open_rep, open_date, open_rep, first_Close_date, first_Close_rep, Close_date, Close_rep, pass)
SELECT 398402, 84362, 'Approach', 'Completed', 1, '11/21/2008 10:31:51 AM', 'Coleman', '11/21/2008 10:31:51 AM', 'Coleman', '11/22/2008 11:16:54 AM', 'Kramer', '11/22/2008 11:16:54 AM', 'Kramer', 1 UNION ALL
SELECT 398403, 84362, 'Interview', 'Completed', 2, '11/22/2008 11:16:54 AM', 'Kramer', '11/29/2008 11:16:54 AM', 'Kramer', '11/27/2008 11:16:54 AM', 'Kramer', '11/30/2008 11:16:54 AM', 'Kramer', 1 UNION ALL
SELECT 398404, 84362, 'Demonstrate', 'Completed', 3, '11/27/2008 11:16:54 AM', 'Kramer', '11/30/2008 11:16:54 AM', 'Kramer', '12/02/2008 11:16:54 AM', 'Kramer', '12/02/2008 11:16:54 AM', 'Kramer', 1 UNION ALL
SELECT 398405, 84362, 'Negotiate', 'Completed', 4, '12/02/2008 11:16:54 AM', 'Kramer', '12/10/2008 11:16:54 AM', 'Kramer', '12/08/2008 11:16:54 AM', 'Kramer', '12/11/2008 11:16:54 AM', 'Kramer', 1 UNION ALL
SELECT 398406, 84362, 'Close', 'Completed', 5, '12/08/2008 11:16:54 AM', 'Kramer', '12/11/2008 11:16:54 AM', 'Kramer', '12/12/2008 11:17:36 AM', 'Kramer', '12/12/2008 11:17:36 AM', 'Kramer', 1 UNION ALL
SELECT 398432, 84375, 'Approach', 'Completed', 1, '10/21/2008 2:36:39 PM', 'DOLPHIN', '10/21/2008 2:36:39 PM', 'DOLPHIN', '10/25/2008 2:36:39 PM', 'DOLPHIN', '10/25/2008 2:36:39 PM', 'DOLPHIN', 1 UNION ALL
SELECT 398433, 84375, 'Interview', 'Completed', 2, '10/25/2008 2:36:39 PM', 'DOLPHIN', '10/25/2008 2:36:39 PM', 'DOLPHIN', '10/30/2008 2:36:39 PM', 'DOLPHIN' , '10/30/2008 2:36:39 PM', 'DOLPHIN', 1 UNION ALL
SELECT 398434, 84375, 'Demonstrate', 'Completed', 3, '10/30/2008 2:36:39 PM', 'DOLPHIN', '10/30/2008 2:36:39 PM', 'DOLPHIN', '11/01/2008 2:36:39 PM', 'DOLPHIN', '11/01/2008 2:36:39 PM', 'DOLPHIN', 1 UNION ALL
SELECT 398435, 84375, 'Negotiate', 'Completed', 4, '11/01/2008 2:36:39 PM', 'DOLPHIN', '11/01/2008 2:36:39 PM', 'DOLPHIN', '11/16/2008 2:36:39 PM', 'DOLPHIN', '11/16/2008 2:36:39 PM', 'DOLPHIN', 1 UNION ALL
SELECT 398436, 84375, 'Close', 'Completed', 5, '11/16/2008 2:36:39 PM', 'DOLPHIN', '11/16/2008 2:36:39 PM', 'DOLPHIN', '11/20/2008 2:38:31 PM', 'DOLPHIN', '11/20/2008 2:38:31 PM', 'DOLPHIN', 1 UNION ALL
SELECT 398437, 84376, 'Approach', 'Completed', 1, '9/21/2008 2:38:50 PM', 'DOLPHIN', '9/21/2008 2:38:50 PM', 'DOLPHIN', '9/21/2008 2:38:50 PM', 'DOLPHIN', '9/21/2008 2:38:50 PM', 'DOLPHIN', 1 UNION ALL
SELECT 398438, 84376, 'Interview', 'Completed', 2, '9/21/2008 2:38:50 PM', 'DOLPHIN', '10/13/2008 2:38:50 PM', 'DOLPHIN', '10/12/2008 2:38:50 PM', 'DOLPHIN', '10/15/2008 2:38:50 PM', 'DOLPHIN', 0 UNION ALL
SELECT 398439, 84376, 'Demonstrate', 'In Progress', 3, '10/12/2008 2:38:50 PM', 'DOLPHIN', '10/15/2008 2:38:50 PM', 'DOLPHIN', NULL, NULL, NULL, NULL, 0 UNION ALL
SELECT 398440, 84376, 'Negotiate', 'Pending', 4, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0 UNION ALL
SELECT 398441, 84376, 'Close', 'Pending', 5, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0 UNION ALL
SELECT 398442, 84377, 'Approach', 'Completed', 1, '8/21/2008 2:44:26 PM', 'DOLPHIN', '8/21/2008 2:44:26 PM', 'DOLPHIN', '8/28/2008 2:44:26 PM', 'DOLPHIN', '8/28/2008 2:44:26 PM', 'DOLPHIN', 1 UNION ALL
SELECT 398443, 84377, 'Interview', 'Completed', 2, '8/28/2008 2:44:26 PM', 'DOLPHIN', '8/28/2008 2:44:26 PM', 'DOLPHIN', '9/11/2008 2:44:26 PM', 'DOLPHIN', '9/11/2008 2:44:26 PM', 'DOLPHIN', 0 UNION ALL
SELECT 398444, 84377, 'Demonstrate', 'In Progress', 3, '9/11/2008 2:44:26 PM', 'DOLPHIN', '9/11/2008 2:44:26 PM', 'DOLPHIN', NULL, NULL, NULL, NULL, 0 UNION ALL
SELECT 398445, 84377, 'Negotiate', 'Pending', 4, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0 UNION ALL
SELECT 398446, 84377, 'Close', 'Pending', 5, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0
What I want to do is build a view that will return the dates in the new format and then all I’ll have to do is change the applications and functions to look at the view rather than the table. (I know, it should already be looking at a view.)
So, to accomplish this I think if I start with approach and always use the first_open_date of approach and then from there use the close_date I’ll have what I need for Approach. Then the open dates for Interview should be the close_date of Approach. The close date for Interview is just the close date. The open dates for Demonstrate would be the close_date of Interview. The close dates for interview are its close date. This should continue for the remaining steps.
Here is what I’d like the new view to return the data like (I’m keeping the structure the same so that I can easily replace the table with the view):
398402, 84362, 'Approach', 'Completed', 1, '11/21/2008 10:31:51 AM', 'Coleman', '11/21/2008 10:31:51 AM', 'Coleman', '11/22/2008 11:16:54 AM', 'Kramer', '11/22/2008 11:16:54 AM', 'Kramer', 1
398403, 84362, 'Interview', 'Completed', 2, '11/22/2008 11:16:54 AM', 'Kramer', '11/22/2008 11:16:54 AM', 'Kramer', '11/30/2008 11:16:54 AM', 'Kramer', '11/30/2008 11:16:54 AM', 'Kramer', 1
398404, 84362, 'Demonstrate', 'Completed', 3, '11/30/2008 11:16:54 AM', 'Kramer', '11/30/2008 11:16:54 AM', 'Kramer', '12/02/2008 11:16:54 AM', 'Kramer', '12/02/2008 11:16:54 AM', 'Kramer', 1
398405, 84362, 'Negotiate', 'Completed', 4, '12/02/2008 11:16:54 AM', 'Kramer', '12/02/2008 11:16:54 AM', 'Kramer', '12/11/2008 11:16:54 AM', 'Kramer', '12/11/2008 11:16:54 AM', 'Kramer', 1
398406, 84362, 'Close', 'Completed', 5, '12/11/2008 11:16:54 AM', 'Kramer', '12/11/2008 11:16:54 AM', 'Kramer', '12/12/2008 11:17:36 AM', 'Kramer', '12/12/2008 11:17:36 AM', 'Kramer', 1
398432, 84375, 'Approach', 'Completed', 1, '10/21/2008 2:36:39 PM', 'DOLPHIN', '10/21/2008 2:36:39 PM', 'DOLPHIN', '10/25/2008 2:36:39 PM', 'DOLPHIN', '10/25/2008 2:36:39 PM', 'DOLPHIN', 1
398433, 84375, 'Interview', 'Completed', 2, '10/25/2008 2:36:39 PM', 'DOLPHIN', '10/25/2008 2:36:39 PM', 'DOLPHIN', '10/30/2008 2:36:39 PM', 'DOLPHIN' , '10/30/2008 2:36:39 PM', 'DOLPHIN', 1
398434, 84375, 'Demonstrate', 'Completed', 3, '10/30/2008 2:36:39 PM', 'DOLPHIN', '10/30/2008 2:36:39 PM', 'DOLPHIN', '11/01/2008 2:36:39 PM', 'DOLPHIN', '11/01/2008 2:36:39 PM', 'DOLPHIN', 1
398435, 84375, 'Negotiate', 'Completed', 4, '11/01/2008 2:36:39 PM', 'DOLPHIN', '11/01/2008 2:36:39 PM', 'DOLPHIN', '11/16/2008 2:36:39 PM', 'DOLPHIN', '11/16/2008 2:36:39 PM', 'DOLPHIN', 1
398436, 84375, 'Close', 'Completed', 5, '11/16/2008 2:36:39 PM', 'DOLPHIN', '11/16/2008 2:36:39 PM', 'DOLPHIN', '11/20/2008 2:38:31 PM', 'DOLPHIN', '11/20/2008 2:38:31 PM', 'DOLPHIN', 1
398437, 84376, 'Approach', 'Completed', 1, '9/21/2008 2:38:50 PM', 'DOLPHIN', '9/21/2008 2:38:50 PM', 'DOLPHIN', '9/21/2008 2:38:50 PM', 'DOLPHIN', '9/21/2008 2:38:50 PM', 'DOLPHIN', 1
398438, 84376, 'Interview', 'Completed', 2, '9/21/2008 2:38:50 PM', 'DOLPHIN', '9/21/2008 2:38:50 PM', 'DOLPHIN', '10/15/2008 2:38:50 PM', 'DOLPHIN', '10/15/2008 2:38:50 PM', 'DOLPHIN', 0
398439, 84376, 'Demonstrate', 'In Progress', 3, '10/15/2008 2:38:50 PM', 'DOLPHIN', '10/15/2008 2:38:50 PM', 'DOLPHIN', NULL, NULL, NULL, NULL, 0
398440, 84376, 'Negotiate', 'Pending', 4, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0
398441, 84376, 'Close', 'Pending', 5, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0
398442, 84377, 'Approach', 'Completed', 1, '8/21/2008 2:44:26 PM', 'DOLPHIN', '8/21/2008 2:44:26 PM', 'DOLPHIN', '8/28/2008 2:44:26 PM', 'DOLPHIN', '8/28/2008 2:44:26 PM', 'DOLPHIN', 1
398443, 84377, 'Interview', 'Completed', 2, '8/28/2008 2:44:26 PM', 'DOLPHIN', '8/28/2008 2:44:26 PM', 'DOLPHIN', '9/11/2008 2:44:26 PM', 'DOLPHIN', '9/11/2008 2:44:26 PM', 'DOLPHIN', 0
398444, 84377, 'Demonstrate', 'In Progress', 3, '9/11/2008 2:44:26 PM', 'DOLPHIN', '9/11/2008 2:44:26 PM', 'DOLPHIN', NULL, NULL, NULL, NULL, 0
398445, 84377, 'Negotiate', 'Pending', 4, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0
398446, 84377, 'Close', 'Pending', 5, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0
What would be the best approach to get the new view to return values in this way?
November 23, 2008 at 10:05 am
You already have a sequence number in your table, so you can "look ahead" to see the open date for the next step using a left join as shown below. (Build an index over the [opid] and [seq] columns in production.) CASE expressions will handle situations where you have a subsequent open date and want to override the first open date.
select o1.*,o2.* -- o1 is the current row, o2 is the "next" row
from #op_milestones o1
left join #op_milestones o2 on o2.opid = o1.opid and o2.seq = o1.seq+1
If it is conceptually easier for you to "look back", you can just change the left join to subtract from the current seq column
left join #op_milestones o2 on o2.opid = o1.opid and o2.seq = o1.seq-1
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 23, 2008 at 9:53 pm
Thanks for your help. This isn't complete yet, but after some initial checks this appears to be returning what I need.
SELECT op_m1.mileid,
op_m1.opid,
op_m1.mile_code,
op_m1.mile_status,
op_m1.seq,
CASE WHEN op_m1.seq = 1 THEN op_m1.first_open_date ELSE op_m2.Close_date END AS [open_date],
op_m1.Close_date
FROM #op_milestones op_m1 LEFT JOIN #op_milestones op_m2 ON op_m2.opid = op_m1.opid and op_m2.seq = op_m1.seq-1
November 24, 2008 at 12:28 am
So.... what happens if a step is reopened more than twice? Does it still work? And, heh... you've just gotta know that will be the next request because they want total time of the step, right?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 24, 2008 at 8:27 am
Jeff's right. My company tracks turntimes for a number of different processes. Once you've identified which jobs are taking the most time, the next question will always be "why?". You'll probably end up wanting a detail log that will identify the exact sequence of steps (including those that are re-opened), total times per step, and parties responsible.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 24, 2008 at 9:07 am
If it is reopened more than once it still works because we start with the first seq and then calcualte all the way through the open and close dates based off of the closed dates.
You are right about what they want now and in the future. Today they claim they won't care how many times things are reopened and I don't have to worry about caclulating the times steps are "opened" as the total time they are open because if something was moved ahead and then had to be reopened then in actuallity the time should count for the previous step because it shouldn't have been advanced anyway.
Having said all of that, we have created a history table in addition to this that tracks all of the movement of these steps back and forth so when the questions comes that they "won't ever need," we'll have the information.
Thanks!
November 24, 2008 at 9:15 pm
Thanks, ehlinger ... I appreciate the detail. Just wanted to make sure you were "covered". Good "seeing" you again.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 24, 2008 at 11:48 pm
Thanks Jeff to you for all of your help and follow up to all of us on the forum! Your input and help is greatly appreciated!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply