April 16, 2012 at 1:57 pm
I have a working query. It was given to me by ColdCoffee ( http://qa.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?
April 16, 2012 at 2:31 pm
it would probably be best to continue the discussion in the old thread.
http://qa.sqlservercentral.com/Forums/Topic1283875-392-1.aspx
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 2:35 pm
Add the following to your CTE Where clause:
WHERE Revision_num IN(
SELECT MAX(Revision_num) FROM AllData
GROUP BY Ticket#)
April 16, 2012 at 2:45 pm
Hi stevro.
Like this? No workie.
; 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
WHERE Revision_num IN(
SELECT MAX(Revision_num) FROM AllData
GROUP BY Ticket#)
)
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;
April 16, 2012 at 3:41 pm
see the original thread i have posted an answer there.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply