March 15, 2013 at 9:01 am
I think this issue is complex (at least to me) but will try to explain correctly and put some meaningful info in.
I am trying to create a view to access from BIDS.
The originating table goes like this
wfIdcreated logType activityName agentSourceagentName outcome details
C72013-03-14 310 TechnicalFred John NULL Pre-Sales - Check Technical Details
C72013-03-15 316 TechnicalJohn Fred Reject Performed By John
C72013-03-15 310 TechRejectJohn Fred NULL Originator - Technical Rejection
C72013-03-16 316 TechRejectFred John NULL Performed By Fred
C72013-03-16 310 TechnicalFred John NULL Pre-Sales - Check Technical Details
C72013-03-16 316 TechnicalJohn Fred Checked Performed By John
C72013-03-16 310 SalesDirectorJohn Steve NULL Sales Director - Please Approve Proposal
C82013-03-14 310 TechnicalFred John NULL Pre-Sales - Check Technical Details
C82013-03-15 316 TechnicalJohn Fred Checked Performed By John
C82013-03-15 310 SalesDirectorJohn Steve NULL Sales Director - Please Approve Proposal
I am trying to return the following in the view
workflowId TechDt TechAgent TechOut TechOutDt TechDur ReSubDt ReSubAgent ReSubOut ReSubOutDt ResubDur SDAgent SDOut SDDur
C7 2013-03-14 John Reject 2013-03-15 1 2013-03-16 John Checked 2013-03-16 0 Steve NULL NULL
C8 2013-03-14 John Checked 2013-03-15 1 NULL NULL NULL NULL NULL Steve NULL NULL
Where Dt = Date, Out = Outcome, Dur = Duration
For this example, assume there can only be one re-submit and the only outcome for SalesDirector is Accept or Reject with no resubmit n the SalesDirector outcome.
In words, I am trying to get the duration of each step of the workflow, in the real database there are many possible legs to a workflow and many possible routes but they all follow the same basic principal. I would like to be able to see where the current WorkflowID is, for example C8 and C7 is currently with the Sales Director.
I have tried unions and tried inner joins to get the data onto one line by doing an inner join then using "where" in each join to separate out the different steps and get them into columns, my biggest issue is that the second workflow submission is the same as the original submission and I need to identify it as a different one. The secondary issue is I don't know which method would run fastest. The actual database has around 40 000 records per year and around 8 lines per unique workflowId.
If this is not explained well, please tell me and I will attempt to make it more clear
March 15, 2013 at 9:36 am
It is pretty tough to offer much help because the details are not very clear. It would help greatly if you could post ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. This way we can work on helping you code a query for your problem. Please take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 15, 2013 at 1:00 pm
I have used script table as, hope this is okay.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE mytable(
[workflowId] [uniqueidentifier] NOT NULL,
[created] [datetime] NOT NULL,
[logType] [smallint] NOT NULL,
[activityName] [nvarchar](50) NULL,
[agentType] [int] NULL,
[agentSource] [sql_variant] NULL,
[agentKey] [sql_variant] NULL,
[agentName] [nvarchar](70) NULL,
[outcome] [nvarchar](50) NULL,
[details] [nvarchar](max) NULL
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable
(workflowId,created,logType,activityName,agentType,agentSource,agentName,outcome,details)
SELECT 'B316','Mar 15 2013 12:45PM','310','SalesDirector',2,AgentServic,'Steve','NULL','Sales Director - Please Approve Proposal' UNION ALL
SELECT 'B316','Mar 15 2013 12:45PM','316','Technical',2,AgentServic,'John','Checked','Performed By System Administrator Account' UNION ALL
SELECT 'B316','Mar 15 2013 12:45PM','310','Technical',2,AgentServic,'John','NULL','Pre-Sales - Check Technical Details' UNION ALL
SELECT 'B316','Mar 15 2013 12:45PM','316','TechReject',2,AgentServic,'Fred','Re Submit','Performed By System Administrator Account' UNION ALL
SELECT 'B316','Mar 15 2013 12:45PM','310','TechReject',2,AgentServic,'Fred','NULL','Originator - Technical Rejection' UNION ALL
SELECT 'B316','Mar 15 2013 12:45PM','316','Technical',2,AgentServic,'John','Reject','Performed By System Administrator Account' UNION ALL
SELECT 'B316','Mar 14 2013 11:46AM','310','Technical',2,AgentServic,'John','NULL','Pre-Sales - Check Technical Details' UNION ALL
SELECT 'B317','Mar 15 2013 12:45PM','310','SalesDirector',2,AgentServic,'Steve','NULL','Sales Director - Please Approve Proposal' UNION ALL
SELECT 'B317','Mar 15 2013 12:45PM','316','Technical',2,AgentServic,'John','Checked','Performed By System Administrator Account' UNION ALL
SELECT 'B317','Mar 13 2013 12:45PM','310','Technical',2,AgentServic,'John','NULL','Pre-Sales - Check Technical Details'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable ON
Okay hopefully that has got things in a little more workable format
Little Explanation then.
Take B316 as an example going from earliest date, task is started and a line created by sending to John, John then rejects - this adds a new line and sends it to Fred, Fred Resubmits - this adds a new line and sends to John, John marks it as checked - this creates a new line and sends it to Steve and creates another line for Steve's task. In my "live" example, the Re-Submit task is not differentiated from the original task so would not say re-submit, it would just be null.
The result I want is
WorkflowID |DateStarted|PreSalesAgent|PreSalesDate|PreSalesOutcome|RejectAgent|ResubmitDate|ResubmitAgent|ResubmitOutcome|SalesDirector|SDOutcome|SDDate
B316 | 4-03-2013 | John | 15-03-2013 | Reject | Fred | 15-03-2013 | John | Checked | Steve | Null| Null
B317 | 3-03-2013 | John | 15-03-2013 | Checked | Null | Null | Null | Null | Steve | Null| Null
Hope that is now a bit clearer
March 15, 2013 at 1:11 pm
You got the basic gist of what we need but you certainly didn't test it. There were a lot of issues here.
WorkflowId defined a uniqueidentifier but the values are all char(4).
Setting IDENTITY_INSERT ON/OFF - there is no identity on the table.
AgentServic was not wrapped as string.
Not really sure why you are using sql_variant but that is a topic for another day.
I cleaned this up and it will now at least run. Can you check this to see if this will work?
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE #mytable(
[workflowId] char(4) NOT NULL,
[created] [datetime] NOT NULL,
[logType] [smallint] NOT NULL,
[activityName] [nvarchar](50) NULL,
[agentType] [int] NULL,
[agentSource] [sql_variant] NULL,
[agentKey] [sql_variant] NULL,
[agentName] [nvarchar](70) NULL,
[outcome] [nvarchar](50) NULL,
[details] [nvarchar](max) NULL
)
--===== All Inserts into the IDENTITY column
--SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable
(workflowId,created,logType,activityName,agentType,agentSource,agentName,outcome,details)
SELECT 'B316','Mar 15 2013 12:45PM','310','SalesDirector',2,'AgentServic','Steve','NULL','Sales Director - Please Approve Proposal' UNION ALL
SELECT 'B316','Mar 15 2013 12:45PM','316','Technical',2,'AgentServic','John','Checked','Performed By System Administrator Account' UNION ALL
SELECT 'B316','Mar 15 2013 12:45PM','310','Technical',2,'AgentServic','John','NULL','Pre-Sales - Check Technical Details' UNION ALL
SELECT 'B316','Mar 15 2013 12:45PM','316','TechReject',2,'AgentServic','Fred','Re Submit','Performed By System Administrator Account' UNION ALL
SELECT 'B316','Mar 15 2013 12:45PM','310','TechReject',2,'AgentServic','Fred','NULL','Originator - Technical Rejection' UNION ALL
SELECT 'B316','Mar 15 2013 12:45PM','316','Technical',2,'AgentServic','John','Reject','Performed By System Administrator Account' UNION ALL
SELECT 'B316','Mar 14 2013 11:46AM','310','Technical',2,'AgentServic','John','NULL','Pre-Sales - Check Technical Details' UNION ALL
SELECT 'B317','Mar 15 2013 12:45PM','310','SalesDirector',2,'AgentServic','Steve','NULL','Sales Director - Please Approve Proposal' UNION ALL
SELECT 'B317','Mar 15 2013 12:45PM','316','Technical',2,'AgentServic','John','Checked','Performed By System Administrator Account' UNION ALL
SELECT 'B317','Mar 13 2013 12:45PM','310','Technical',2,'AgentServic','John','NULL','Pre-Sales - Check Technical Details'
--===== Set the identity insert back to normal
--SET IDENTITY_INSERT #mytable ON
select * from #mytable
Now I realize it may seem like I am being a bit anal but the real issue is that your desired output doesn't seem to match up with the sample data.
B316 | 4-03-2013
You have that listed as the DateStarted but regardless of dateformat (mdy or dmy) there is no date in the table for B316 with that date. Where does it come from?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 15, 2013 at 3:17 pm
Hi. Thank you so much for your effort.
I was trying to rush things before getting removed from my office, Fridays tend to be nuts at knock off time and not much appetite for waiting around while i get things finished.
The start date is the same as the created date on first line for each Workflow id, sorry for not noting that.
I do not spend enough time working on sql and only get to work on it just a few times a year, hopefully this will be the start of a bit of a run and make some more reading worthwhile.
I'll hopefully be able to get in remotely and will be able to test the script out as well as drop the db onto my laptop.
March 15, 2013 at 3:27 pm
steven.n.rhodes (3/15/2013)
Hi. Thank you so much for your effort.I was trying to rush things before getting removed from my office, Fridays tend to be nuts at knock off time and not much appetite for waiting around while i get things finished.
The start date is the same as the created date on first line for each Workflow id, sorry for not noting that.
I do not spend enough time working on sql and only get to work on it just a few times a year, hopefully this will be the start of a bit of a run and make some more reading worthwhile.
I'll hopefully be able to get in remotely and will be able to test the script out as well as drop the db onto my laptop.
No worries. The first two columns are easy enough but it is extremely vague from there what the business rules might be.
Here is the first couple columns.
select WorkflowID, min(created) as DateStarted
from #mytable
group by WorkflowID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy