using t-sql to roll-up aggregated info.
-
I think that what I want is impossible to achieve with T-SQL alone. . . please tell me if it *is* possible.
DDL
CREATE table AllData
(ID int, OrgName varchar(15) null, ProjName varchar(15) null, Ticket# int null)
INSERT INTO AllData
VALUES
(1, 'MANUF', 'GOFISHIE', 67872),
(2, 'MANUF', 'GOFISHIE', 56125),
(3, 'MANUF', 'CLUEMON', 743366),
(4, 'MANUF', 'CLUEMON', 82563),
(5, 'MANUF', 'CLUEMON', 44321),
(6, 'MANUF', 'HYPERVEGAN', 33971),
(7, 'MANUF', 'HYPERVEGAN', 98567),
(8, 'CUSTCARE', 'SIXVISION', 65665),
(9, 'CUSTCARE', 'SIXVISION', 12111),
(8, 'CUSTCARE', 'SIXVISION', 43892),
(8, 'CUSTCARE', 'SIXVISION', 76761),
(8, 'CUSTCARE', 'GLEEPANTS', 87654),
(8, 'CUSTCARE', 'GLEEPANTS', 98203);
---I need to combine the results of the following two queries and present in a drill-down fashion using T-SQL
select ProjName, count(ticket#) as TicketsByProj
from AllData
group by ProjName;
select OrgName, count(ProjName) as #ofProjectsPerOrg, count(ticket#) as #ofTicketsPerProj
from AllData
group by OrgName;
---The report I am to produce needs to drill down as follows:
ORG Name | Total # of Tickets per Org<
Project Name per Org | Count of Tickets by Project| tickets listed by project
I would like to unite these two queries showing respective expansion and contraction. Is this possible to do with T_SQL? Can the queries at least be combined into one query (programmatically, ie. with loop) that can be imported to Excel and then aggregated accordingly?
-
THanks for posting the DDL and sample data; one last thing, can you please show us the expected result out of your sample data?
-
Can you give me tips on how to do here? It's very hard to simulate columns and rows with straight text. I have not had success inserting or attaching snapshots. Any advice on this would be appreciated.
-
If you have excel, just create a visual representation of columns and the values you want to be in the rows (based off of your sample data) , copy the grid, paste them here by wrapping them using code="plain" /code tags.
Else, the same excel, take a screenshot, upload it to some photosharing site, grab the HTML/BBCode link from the image, paste it her wrapping them in URL /URL tags
Else, create a series of slect statments like SELECT col1 , colb union all select col1, colb
-
So, you can ask you question like this!
Hi here are is my sample data and sample table structure
IF OBJECT_ID('tempdb..#Temp') IS NOT NULLDROP TABLE #Temp
-- Sample Table
CREATE TABLE #Temp
(
Team VARCHAR(20)
,Gender VARCHAR(20)
,Age INT
)
-- Sample Data
INSERT INTO #Temp(Team,Gender,Age)
SELECT 'Team1' , 'Male' , 45
UNION ALL SELECT 'Team1' ,'Male' , 10
UNION ALL SELECT 'Team2' ,'Male' , 13
UNION ALL SELECT 'Team1','Female' , 10
UNION ALL SELECT 'Team2','Female' , 30
UNION ALL SELECT 'Team2','Female' , 20
UNION ALL SELECT 'Team2','[Not Specified]' , 10
Here are my expected results
--Expected Result
SELECT 'Team1' AS 'Team',10 AS 'Male' , 20 AS 'Female' , 30 AS '[Not Specified]'
UNION ALL SELECT 'Team2' AS 'Team',10 AS 'Male' , 20 AS 'Female' , 30 AS '[Not Specified]'
Rules/Requirements for my expected results
1. Create a report based of the Team and Gender column
2. Gender should be pivoted based off Team and should contain AVERAGE of team-gender pair
3. So values in Gender will become columns of the new report, values in Team will be rows, and AVERAGE age of each team-GEnder will be in the rows!
This is how we must post!
-
Well I came back to the forum to post the excel spreadsheet and see you've already got a solution. Wow.
OK, so here goes anyway. Here's what I'm after and I'll take a look at your answer now. Thanks
ORGNAME#ofWorkItemsPerOrgPROJNAME#ofWorkItemsPerProjTicket#
MANUF7GOFISHIE267872
""""56125
""CLUEMON374366
""""82563
""""44321
""HYPERVEGAN233971
""""98567
CUSTCARE6SIXVISION465665
""""12111
""""43892
""""76761
""GLEEPANTS287654
""""98203
but I see it's getting distorted anyway.
I also editted my DDL.
-
Yes, unfortunately, I think my data has one more tier than your sample.
Also, unfortunately, the excel layout wasn't preserved when I used code="other" tags.
-
ColdCoffee,
Hey look, this will work to show you how I want layout.
Select * from alldata;
It works because if you look at my revised DDL, you'll see the INSERT is formatted the way I want result set to look in the end (but with total columns missing :-(). Next to Org needs to be a column summarizing total Ticket count by Org and Total ProjName count by Org, Next to ProjName is to be a column summarizing total Ticket count by ProjName, and next to that the tickets themselves.
-
Your sample data has a flaw; All the iDs after 9 are 8 only. You have to increment them. I did that, and here is the query that produces the expected result u showed us
; WITH 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 AllData AD
--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'
,C.Ticket#
FROM CTE C
ORDER BY C.ID
And the output this produces is
{Edit1 - Result shown as image}
-
I can't believe it ColdCoffee. I thank you....I want to work where you work 🙂
T-SQL is amazing.
-
hxkresl (4/16/2012)
I can't believe it ColdCoffee. I thank you....I want to work where you work 🙂T-SQL is amazing.
Lol, they dont even want me at the office :-D:w00t: (that was just a joke :cool:)
Anyways, thanks for the feedback hxkresl.
-
Two more things I'd like to accomplish with this.
1. rename the Org field, which in real life is only the name of a column, and it's values, are numbers.
case when Org = 1 then 'HumanResources'
when Org = 89 then 'Legal'
end as Org
2. retrieve only the ticket# having the max(revision_number) as each ticket may be revised many times, each time generating a row.
select row_number() over (partition by ticket# order by revision_number desc) row
....
) stus
where row = 1
how do I accomplish. Do I create another CTE to do this scrubbing? Do I wedge this code into the existing solution?
-
OK, I got the case statement integrated but the retrieval of tickets by max(revision_number) is still eluding me.
The result set for the above query includes multiple entries per ticket#. I need only the enter of the ticket# having the highest revision_number.
I have posted this questions in a new thread (including DDL), as it could be new problem.
http://www.sqlservercentral.com/Forums/Topic1284483-392-1.aspx?Update=1
-
per Capn.Hector's advice, I am to stick to this thread.
I have a working query. It was given to me by ColdCoffee ( http://www.sqlservercentral.com/Forums/Topic1283875-392-1.aspx ) to solve a major headache problem.
I need to modify this query (below) to retrieve only distinct ticket# values. This table being queried will have multiple rows for any given ticket# because of multiple revisions. Only the ticket# having the highest revision_number in the Revision_ number column should be retrieved by query.
DDL
CREATE table AllData
(ID int, OrgName varchar(15) null, ProjName varchar(15) null, Ticket# int null, Revision_num int)
INSERT INTO AllData
VALUES
(1, 'MANUF', 'GOFISHIE', 67872, 1),
(2, 'MANUF', 'GOFISHIE', 67872, 2),
(3, 'MANUF', 'GOFISHIE', 67872, 3),
(4, 'MANUF', 'GOFISHIE', 67872, 4),
(5, 'MANUF', 'GOFISHIE', 56125, 1),
(6, 'MANUF', 'GOFISHIE', 56125, 2),
(7, 'MANUF', 'GOFISHIE', 56125, 3),
(8, 'MANUF', 'GOFISHIE', 56125, 4),
(9, 'MANUF', 'GOFISHIE', 56125, 5),
(10, 'MANUF', 'CLUEMON', 74336, 1),
(11, 'MANUF', 'CLUEMON', 82563, 1),
(12, 'MANUF', 'CLUEMON', 82563, 2),
(13, 'MANUF', 'CLUEMON', 82563, 3),
(14, 'MANUF', 'CLUEMON', 82563, 4),
(15, 'MANUF', 'CLUEMON', 82563, 5),
(16, 'MANUF', 'CLUEMON', 82563, 6),
(17, 'MANUF', 'CLUEMON', 44321, 1),
(18, 'MANUF', 'HYPERVEGAN', 33971, 1),
(19, 'MANUF', 'HYPERVEGAN', 98567, 1),
(20, 'CUSTCARE', 'SIXVISION', 65665, 1),
(21, 'CUSTCARE', 'SIXVISION', 12111, 1),
(22, 'CUSTCARE', 'SIXVISION', 43892, 1),
(23, 'CUSTCARE', 'SIXVISION', 76761, 1),
(24, 'CUSTCARE', 'GLEEPANTS', 87654, 1),
(25, 'CUSTCARE', 'GLEEPANTS', 98203, 1);
---the query is very unique because it orders results by OrgName, #ofTicketsbyOrg, ProjName, #ofTicketsbyProj, Ticket#s. (it's a nice drill down query)
; WITH 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 AllData AD
--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'
,C.Ticket#
FROM CTE C
ORDER BY C.ID
---where would you integrate the max(revision_number) logic?
-
; 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 revisionFROM 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 C.ProjName ELSE '' END AS 'ProjName'
,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
i added a second cte to generate the ticket id we want (max revision). other than that i left the code alone.
For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.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]
[/url]Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply