Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

rows to columns - creating view for SSRS Expand / Collapse
Author
Message
Posted Friday, March 15, 2013 9:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 18, 2013 5:05 AM
Points: 7, Visits: 24
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

wfId created logType activityName agentSource agentName outcome details

C7 2013-03-14 310 Technical Fred John NULL Pre-Sales - Check Technical Details
C7 2013-03-15 316 Technical John Fred Reject Performed By John
C7 2013-03-15 310 TechReject John Fred NULL Originator - Technical Rejection
C7 2013-03-16 316 TechReject Fred John NULL Performed By Fred
C7 2013-03-16 310 Technical Fred John NULL Pre-Sales - Check Technical Details
C7 2013-03-16 316 Technical John Fred Checked Performed By John
C7 2013-03-16 310 SalesDirector John Steve NULL Sales Director - Please Approve Proposal

C8 2013-03-14 310 Technical Fred John NULL Pre-Sales - Check Technical Details
C8 2013-03-15 316 Technical John Fred Checked Performed By John
C8 2013-03-15 310 SalesDirector John 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
Post #1431578
Posted Friday, March 15, 2013 9:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 13,007, Visits: 12,426
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1431595
Posted Friday, March 15, 2013 1:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 18, 2013 5:05 AM
Points: 7, Visits: 24
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
Post #1431692
Posted Friday, March 15, 2013 1:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 13,007, Visits: 12,426
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1431704
Posted Friday, March 15, 2013 3:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 18, 2013 5:05 AM
Points: 7, Visits: 24
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.
Post #1431789
Posted Friday, March 15, 2013 3:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 13,007, Visits: 12,426
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1431797
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse