April 16, 2012 at 5:05 pm
It's really helpful capn.Hector. Thanks for mentoring.
I have one last thing to do.
The report must have one additional level to it's hierarchy that doesn't even exist in the datawarehouse. It is to be called Portfolio_Name and it lies between OrgName and ProjName ie. One Org has many Portfolios, One Portfolio has many Projects.
I have created a CTE to control which Projects belong to which Portfolio:
;WITH Portfolio AS (
SELECT
ID
, CASE ProjName
WHEN 'GOFISHIE' then 'Finance'
WHEN 'CLUEMON' then 'Operations'
WHEN 'HYPERVEGAN' then 'Legal'
WHEN 'GLEEPANTS' then 'Legal'
WHEN 'SIXVISION' then 'Distribution'
ELSE 'Other'
END AS Portfolio_Name
, ProjName
FROM AllDate AD
)
Outcome will show a list of Portfolio names next to the OrgNames, and count of ticket#s by Portfolio, in addition to what it already does. How do I expand on ColdCoffee's code to add this (last) column?
April 16, 2012 at 7:07 pm
it would be adding a variation of your case statement you have in the cte to the final query, not using another cte. i can post the answer in the morning unless someone beats me to it.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 16, 2012 at 11:41 pm
1)
Would you also help me a bit with the definition of OVER transact sql clause. MSDN states
Determines the partitioning and ordering of a rowset before the associated window function is applied. That is, the OVER clause defines a window or user-specified set of rows within a query result set.". In this DDL, could you point out the parts for me that correspond to the statement?
In this DDL that ColdCoffee created, which is the window function that is being applied?
2) Regarding the final step to completing this query ie. adding another level to the hierarchy. I was going to go with a #temp table but since I was having trouble went with the CTE. If I told you that there will be up to 300-400 rows of CASE statements, would you agree that a CTE is better? I basically need to create another table of data for the data that is missing in the original table (AllData) to associate ProjNames with Portfolio names.
Also, don't I need to edit the main statement with an RN3 and CT3 statement, as well? It seems so to me but I can't get it right.
Would appreciate your post in the morning, if as you say, if no one else takes stab at it.
-
April 17, 2012 at 7:09 am
hxkresl (4/16/2012)
It's really helpful capn.Hector. Thanks for mentoring.I have one last thing to do.
The report must have one additional level to it's hierarchy that doesn't even exist in the datawarehouse. It is to be called Portfolio_Name and it lies between OrgName and ProjName ie. One Org has many Portfolios, One Portfolio has many Projects.
...
Outcome will show a list of Portfolio names next to the OrgNames, and count of ticket#s by Portfolio, in addition to what it already does. How do I expand on ColdCoffee's code to add this (last) column?
xenophilia (4/16/2012)
1)Would you also help me a bit with the definition of OVER transact sql clause. MSDN states
...
In this DDL that ColdCoffee created, which is the window function that is being applied?
2) Regarding the final step to completing this query ie. adding another level to the hierarchy. ...
Also, don't I need to edit the main statement with an RN3 and CT3 statement, as well? It seems so to me but I can't get it right.
Would appreciate your post in the morning, if as you say, if no one else takes stab at it.
ok first question is are we dealing with homework and you 2 are from the same class? or same work project that you both are working on?? just trying to get every one strait in my head.
to xenophilia 1:) the windowing functions are the ROW_NUMBER () OVER and the COUNT (*) OVER the over resets the count and row_number for based on the partition by clause and if its a row_number () OVER the order by specifies the order to count in.
and now for the code with the additional case statement:
; WITH Maxticket AS (SELECT AD.ID, AD.OrgName, AD.ProjName, AD.Ticket#, ROW_NUMBER() OVER (PARTITION BY AD.Ticket# ORDER BY Revision_num DESC) AS revision
FROM AllData AD),
CTE AS
(
SELECT AD.ID , AD.OrgName , AD.ProjName , AD.Ticket#
, RN1 = ROW_NUMBER() OVER (PARTITION BY AD.OrgName ORDER BY AD.ID)
, RN2 = ROW_NUMBER() OVER (PARTITION BY AD.OrgName, AD.ProjName ORDER BY AD.ID)
, CT1 = COUNT(*) OVER (PARTITION BY AD.OrgName )
, CT2 = COUNT(*) OVER (PARTITION BY AD.OrgName, AD.ProjName)
FROM Maxticket AD
WHERE AD.revision = 1
--ORDER BY ID
)
SELECT CASE WHEN C.RN1 = 1 THEN C.OrgName ELSE '' END AS 'OrgName'
,CASE WHEN C.RN1 = 1 THEN CAST( C.CT1 AS VARCHAR(19)) ELSE '' END AS '#ofWorkItemsPerOrg'
,CASE WHEN C.RN2 = 1 THEN CASE C.ProjName
WHEN 'GOFISHIE' then 'Finance'
WHEN 'CLUEMON' then 'Operations'
WHEN 'HYPERVEGAN' then 'Legal'
WHEN 'GLEEPANTS' then 'Legal'
WHEN 'SIXVISION' then 'Distribution'
ELSE 'Other'
END ELSE '' END AS 'Portfolio_Name'
,CASE WHEN C.RN2 = 1 THEN CAST( C.CT2 AS VARCHAR(19)) ELSE '' END AS '#ofWorkItemsPerProj'
,C.Ticket#
FROM CTE C
ORDER BY C.ID
we just add your case statement as a second level in the case statement for project name. so we evaluate the project row number and if we would print something we replace it with the case statement to print what we want.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 17, 2012 at 8:39 am
My secretary reads my mail and takes dictation...
looking over now. Thank you! Main thing is progress!!!!
April 17, 2012 at 8:52 am
Oh no, capn.hector. The problem is much harder than that.
I don't want to rename an existing column, I need to create a brand new column and wedge *in between* #ofWorkItemsPerOrg and ProgName. This column needs to be called Portfolio_Name and is a child of Org and a parent of Project.
The case statement is refering to the ProgNames that will fall under the Portfolio_Names (Finance, Operations, Legal, Distribution).
I will return in a few minutes and edit this and provide deatils. I will show you a #temp table that represents the hierarchical relationship between OrgName, Portfolio, ProjName that I will need to draw upon to chink that in there.
April 17, 2012 at 9:43 am
--I need to join earlier statement with this temp table on ProjName column and outcome
Here' the temp table I'd like to use:
select
ID
,Portfolio =
CASE ProjName
WHEN 'GOFISHIE' then 'Finance'
WHEN 'CLUEMON' then 'Operations'
WHEN 'HYPERVEGAN' then 'Operations'
WHEN 'GLEEPANTS' then 'CustomerCare'
WHEN 'SIXVISION' then 'CustomerCare'
ELSE 'Other'
END
, ProjName
into #temp_Portfolio
from AllData
select * from #temp_Portfolio
---should look like this. ColdCoffee uses hyperlink to a flickr photostream to insert image nicely. Will try to do now to get it to look right. For now it's at this link:
http://www.flickr.com/photos/54066173@N08/6941550928/sizes/c/in/photostream/
April 17, 2012 at 11:33 am
ok so we put the renaming of the column into the first CTE as follows:
WITH Maxticket AS (SELECT AD.ID, AD.OrgName,
CASE ProjName
WHEN 'GOFISHIE' then 'Finance'
WHEN 'CLUEMON' then 'Operations'
WHEN 'HYPERVEGAN' then 'Operations'
WHEN 'GLEEPANTS' then 'CustomerCare'
WHEN 'SIXVISION' then 'CustomerCare'
ELSE 'Other'
END AS Portfolio,
AD.ProjName,
AD.Ticket#,
ROW_NUMBER() OVER (PARTITION BY AD.Ticket# ORDER BY Revision_num DESC) AS revision
FROM AllData AD),
From here all you have to do is add to the second cte and the select. i got it to work and have the code but why don't you take a shot at building on cold coffee's code to get the out put you want. it comes out exactly like the photo. once you post what you have ill post my code so you can compare.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 17, 2012 at 11:39 am
Fair enough. Will definitely do. I appreciate it!!!!!!!!;-)
April 17, 2012 at 12:15 pm
stuck on an error in the SELECT statement
Msg 207, Level 16, State 1, Line 32
Invalid column name 'Portfolio'.
WITH Maxticket AS (SELECT AD.ID, AD.OrgName,
CASE ProjName
WHEN 'GOFISHIE' then 'Finance'
WHEN 'CLUEMON' then 'Operations'
WHEN 'HYPERVEGAN' then 'Operations'
WHEN 'GLEEPANTS' then 'CustomerCare'
WHEN 'SIXVISION' then 'CustomerCare'
ELSE 'Other'
END AS Portfolio,
AD.ProjName,
AD.Ticket#,
ROW_NUMBER() OVER (PARTITION BY AD.Ticket# ORDER BY Revision_num DESC) AS revision
FROM AllData AD),
CTE AS
(
SELECT AD.ID , AD.OrgName , AD.ProjName , AD.Ticket#
, RN1 = ROW_NUMBER() OVER (PARTITION BY AD.OrgName ORDER BY AD.ID)
, RN2 = ROW_NUMBER() OVER (PARTITION BY AD.OrgName, AD.ProjName ORDER BY AD.ID)
, RN3 = ROW_NUMBER() OVER (PARTITION BY AD.OrgName, AD.ProjName, AD.Portfolio ORDER BY AD.ID)
, CT1 = COUNT(*) OVER (PARTITION BY AD.OrgName )
, CT2 = COUNT(*) OVER (PARTITION BY AD.OrgName, AD.ProjName)
, CT3 = COUNT(*) OVER (PARTITION BY AD.OrgName, AD.ProjName, AD.Portfolio ORDER BY AD.ID)
FROM Maxticket AD
WHERE AD.revision = 1
--ORDER BY ID
)
SELECT
CASE WHEN C.RN1 = 1 THEN C.OrgName ELSE '' END AS 'OrgName'
,CASE WHEN C.RN1 = 1 THEN CAST( C.CT1 AS VARCHAR(19)) ELSE '' END AS '#ofWorkItemsPerOrg'
,CASE WHEN C.RN2 = 1 THEN C.ProjName ELSE '' END AS 'ProjName'
,CASE WHEN C.RN2 = 1 THEN CAST( C.CT2 AS VARCHAR(19)) ELSE '' END AS '#ofWorkItemsPerProj'
,CASE WHEN C.RN3 = 1 THEN C.Portfolio ELSE '' END AS 'Portfolio'
,CASE WHEN C.RN3 = 1 THEN CAST( C.CT3 AS VARCHAR(19)) ELSE '' END AS '#ofWorkItemsPerPortfolio'
,C.Ticket#
FROM CTE C
ORDER BY C.ID
additional questions. Why do the aggregated (count) columns have to be cast as VARCHAR?
I know the select statement is pulling from the CTE C but where is the association of the CTE with 'C' being made? It appears inferred.
April 17, 2012 at 1:21 pm
hxkresl (4/17/2012)
stuck on an error in the SELECT statementMsg 207, Level 16, State 1, Line 32
Invalid column name 'Portfolio'.
WITH Maxticket AS (SELECT AD.ID, AD.OrgName,
CASE ProjName
WHEN 'GOFISHIE' then 'Finance'
WHEN 'CLUEMON' then 'Operations'
WHEN 'HYPERVEGAN' then 'Operations'
WHEN 'GLEEPANTS' then 'CustomerCare'
WHEN 'SIXVISION' then 'CustomerCare'
ELSE 'Other'
END AS Portfolio,
AD.ProjName,
AD.Ticket#,
ROW_NUMBER() OVER (PARTITION BY AD.Ticket# ORDER BY Revision_num DESC) AS revision
FROM AllData AD),
CTE AS
(
SELECT AD.ID , AD.OrgName , AD.ProjName , AD.Ticket#
, RN1 = ROW_NUMBER() OVER (PARTITION BY AD.OrgName ORDER BY AD.ID)
, RN2 = ROW_NUMBER() OVER (PARTITION BY AD.OrgName, AD.ProjName ORDER BY AD.ID)
, RN3 = ROW_NUMBER() OVER (PARTITION BY AD.OrgName, AD.ProjName, AD.Portfolio ORDER BY AD.ID)
, CT1 = COUNT(*) OVER (PARTITION BY AD.OrgName )
, CT2 = COUNT(*) OVER (PARTITION BY AD.OrgName, AD.ProjName)
, CT3 = COUNT(*) OVER (PARTITION BY AD.OrgName, AD.ProjName, AD.Portfolio ORDER BY AD.ID)
FROM Maxticket AD
WHERE AD.revision = 1
--ORDER BY ID
)
SELECT
CASE WHEN C.RN1 = 1 THEN C.OrgName ELSE '' END AS 'OrgName'
,CASE WHEN C.RN1 = 1 THEN CAST( C.CT1 AS VARCHAR(19)) ELSE '' END AS '#ofWorkItemsPerOrg'
,CASE WHEN C.RN2 = 1 THEN C.ProjName ELSE '' END AS 'ProjName'
,CASE WHEN C.RN2 = 1 THEN CAST( C.CT2 AS VARCHAR(19)) ELSE '' END AS '#ofWorkItemsPerProj'
,CASE WHEN C.RN3 = 1 THEN C.Portfolio ELSE '' END AS 'Portfolio'
,CASE WHEN C.RN3 = 1 THEN CAST( C.CT3 AS VARCHAR(19)) ELSE '' END AS '#ofWorkItemsPerPortfolio'
,C.Ticket#
FROM CTE C
ORDER BY C.ID
additional questions. Why do the aggregated (count) columns have to be cast as VARCHAR?
I know the select statement is pulling from the CTE C but where is the association of the CTE with 'C' being made? It appears inferred.
here is the code i came up with, i have added comments to it where it differs from yours and why it works.
WITH Maxticket AS (SELECT AD.ID, AD.OrgName,
CASE ProjName
WHEN 'GOFISHIE' then 'Finance'
WHEN 'CLUEMON' then 'Operations'
WHEN 'HYPERVEGAN' then 'Operations'
WHEN 'GLEEPANTS' then 'CustomerCare'
WHEN 'SIXVISION' then 'CustomerCare'
ELSE 'Other'
END AS Portfolio,
AD.ProjName,
AD.Ticket#,
ROW_NUMBER() OVER (PARTITION BY AD.Ticket# ORDER BY Revision_num DESC) AS revision
FROM AllData AD),
CTE AS
(
SELECT MT.ID , MT.OrgName, MT.Portfolio, MT.ProjName , MT.Ticket# -- have to add Portfolio here in the select list
, RN1 = ROW_NUMBER() OVER (PARTITION BY MT.OrgName ORDER BY MT.ID)
--\/- these next 2 need to be adding columns as you move left to right in your display notice the difference
, RN2 = ROW_NUMBER() OVER (PARTITION BY MT.OrgName, MT.Portfolio ORDER BY MT.ID)
, RN3 = ROW_NUMBER() OVER (PARTITION BY MT.OrgName, MT.Portfolio, MT.ProjName ORDER BY MT.ID)
, CT1 = COUNT(*) OVER (PARTITION BY MT.OrgName )
--\/ Same here with the columns moving left to right
, CT2 = COUNT(*) OVER (PARTITION BY MT.OrgName, MT.Portfolio)
, CT3 = COUNT(*) OVER (PARTITION BY MT.OrgName, MT.Portfolio, MT.ProjName)
FROM Maxticket MT -- changed alias so it makes sense instead of me taking a short cut as before
WHERE MT.revision = 1
--ORDER BY ID
)
SELECT CASE WHEN C.RN1 = 1 THEN C.OrgName ELSE '' END AS 'OrgName'
,CASE WHEN C.RN1 = 1 THEN CAST( C.CT1 AS VARCHAR(19)) ELSE '' END AS '#ofWorkItemsPerOrg'
,CASE WHEN C.RN2 = 1 THEN C.Portfolio ELSE '' END AS Portfolio
,CASE WHEN C.RN2 = 1 THEN CAST( C.CT2 AS VARCHAR(19)) ELSE '' END AS '#ofWorkItemsPerPortfolio'
,CASE WHEN C.RN3 = 1 THEN C.ProjName ELSE '' END AS 'ProjName'
,CASE WHEN C.RN3 = 1 THEN CAST( C.CT3 AS VARCHAR(19)) ELSE '' END AS '#ofWorkItemsPerProj'
,C.Ticket#
FROM CTE C
ORDER BY C.ID
the C in the query is the table alias which could also be written as "FROM CTE AS C" when you see it like that it makes more sense. look through the differences and see where you ended up goint wrong. for the reason to cast the count, the empty string that we print in the else would show up as a 0 not an empty string causing the blank you want. by casting it to a string data type we dont have that issue.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 17, 2012 at 2:18 pm
Yes, first embarrassed that I didn't change the alias for the Maxticket CTE or flip/flop the order of the columns and statements to reflect the hierarchy I was imposing. :blush:
Thanks for explaining the CAST. It is much more aesthetically pleasing not to have zeroes everywhere.
I understand that CTE C is aka CTE AS C but does that mean that the CASE statements in the CTE are self-referential? I thought they were referring to prior CTE (the one with PARTITION statements). That one isn't given any name or alias so I don't see where the hand off is from the middle CTE to the last one.
April 17, 2012 at 3:00 pm
hxkresl (4/17/2012)
Yes, first embarrassed that I didn't change the alias for the Maxticket CTE or flip/flop the order of the columns and statements to reflect the hierarchy I was imposing. :blush:Thanks for explaining the CAST. It is much more aesthetically pleasing not to have zeroes everywhere.
I understand that CTE C is aka CTE AS C but does that mean that the CASE statements in the CTE are self-referential? I thought they were referring to prior CTE (the one with PARTITION statements). That one isn't given any name or alias so I don't see where the hand off is from the middle CTE to the last one.
I think i know what you are asking, Where does the FROM CTE C get its data?
Here is stripped down commented code (i replaced the common table expression CTE with the name Something to make it a little clearer
WITH Maxticket AS (SELECT ...
FROM AllData AD), -- selecting from the table AllData
Something AS (SELECT ...
FROM Maxticket MT) -- Selecting from the first Common Table Expression (Maxticket)
SELECT ...
FROM Something C -- selects from the second Common Table Expression
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 17, 2012 at 4:58 pm
SELECT ...
FROM Something C -- selects from the second Common Table Expression
But how does the middle CTE come to be recognized as 'C' as it isn't named explicitly. The third CTE refers to C, but middle CTE doesn't call itself that, Nevertheless the script follows through, but I don't see how the relay really gets made....still.
April 17, 2012 at 5:46 pm
hxkresl (4/17/2012)
SELECT ...
FROM Something C -- selects from the second Common Table Expression
But how does the middle CTE come to be recognized as 'C' as it isn't named explicitly. The third CTE refers to C, but middle CTE doesn't call itself that, Nevertheless the script follows through, but I don't see how the relay really gets made....still.
ok there are only 2 common table expressions the third select is a regular select statement.
the whole thing can be rewritten as follows:
SELECT CASE WHEN C.RN1 = 1 THEN C.OrgName ELSE '' END AS 'OrgName',
CASE WHEN C.RN1 = 1 THEN CAST( C.CT1 AS VARCHAR(19)) ELSE '' END AS '#ofWorkItemsPerOrg',
CASE WHEN C.RN2 = 1 THEN C.Portfolio ELSE '' END AS Portfolio,
CASE WHEN C.RN2 = 1 THEN CAST( C.CT2 AS VARCHAR(19)) ELSE '' END AS '#ofWorkItemsPerPortfolio',
CASE WHEN C.RN3 = 1 THEN C.ProjName ELSE '' END AS 'ProjName',
CASE WHEN C.RN3 = 1 THEN CAST( C.CT3 AS VARCHAR(19)) ELSE '' END AS '#ofWorkItemsPerProj',
C.Ticket#
FROM (
SELECT MT.ID , MT.OrgName, MT.Portfolio, MT.ProjName , MT.Ticket#,
RN1 = ROW_NUMBER() OVER (PARTITION BY MT.OrgName ORDER BY MT.ID),
RN2 = ROW_NUMBER() OVER (PARTITION BY MT.OrgName, MT.Portfolio ORDER BY MT.ID),
RN3 = ROW_NUMBER() OVER (PARTITION BY MT.OrgName, MT.Portfolio, MT.ProjName ORDER BY MT.ID),
CT1 = COUNT(*) OVER (PARTITION BY MT.OrgName ),
CT2 = COUNT(*) OVER (PARTITION BY MT.OrgName, MT.Portfolio),
CT3 = COUNT(*) OVER (PARTITION BY MT.OrgName, MT.Portfolio, MT.ProjName)
FROM (
SELECT AD.ID, AD.OrgName,
CASE ProjName
WHEN 'GOFISHIE' then 'Finance'
WHEN 'CLUEMON' then 'Operations'
WHEN 'HYPERVEGAN' then 'Operations'
WHEN 'GLEEPANTS' then 'CustomerCare'
WHEN 'SIXVISION' then 'CustomerCare'
ELSE 'Other'
END AS Portfolio,
AD.ProjName,
AD.Ticket#,
ROW_NUMBER() OVER (PARTITION BY AD.Ticket# ORDER BY Revision_num DESC) AS revision
FROM AllData AD) MT -- changed alias so it makes sense instead of me taking a short cut as before
WHERE MT.revision = 1) C
ORDER BY C.ID
As you can see CTE's make every thing much easier to read but it can be rewritten as one complete query using sub selects.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply