March 14, 2012 at 2:12 pm
I'll kind of explain the details next however i figured i'd put up the issue and desired output first. Kind of hard to explain what i want but might be easier just to see the desired output and deduce from it what i want.
CREATE TABLE #temp
(
sort INT,
[InventoryItemId] VARCHAR(50),
[WebOptionName] VARCHAR(50),
[WebCategoryId] VARCHAR(50),
[WebSubCategoryId] VARCHAR(50)
)
INSERT INTO #temp
(sort,
[InventoryItemId],
[WebOptionName],
[WebCategoryId],
[WebSubCategoryId])
SELECT 0,
'20050282',
'4 Stabiizer Jacks w/Sand Pads',
'Chassis Option',
'Leveling Systems'
UNION
SELECT 0,
'20050282',
'Installed Door Step',
'Chassis Option',
'Steps'
UNION
SELECT 0,
'20050282',
'13" Radial Tires',
'Chassis Option',
'Wheels & Tires'
UNION
SELECT 0,
'20050282',
'Aluminum Wheels',
'Chassis Option',
'Wheels & Tires'
UNION
SELECT 0,
'20050282',
'Spare Tire & Mount',
'Chassis Option',
'Wheels & Tires'
UNION
SELECT 0,
'20050282',
'Aerodynamic Diamond Plate Front',
'Construction',
'Chassis Const'
UNION
SELECT 0,
'20050282',
'Completely Enclosed',
'Construction',
'Chassis Const'
UNION
SELECT 0,
'20050282',
'Fiberglass Exterior',
'Construction',
'Chassis Const'
UNION
SELECT 0,
'20050282',
'Fiberglass Laminated Roof',
'Construction',
'Chassis Const'
UNION
SELECT 0,
'20050282',
'Positive Latches & Catches on Door & Drawers',
'Construction',
'Doors'
UNION
SELECT 0,
'20050282',
'Large Tinted Skylight w/Shade',
'Construction',
'Windows'
UNION
SELECT 0,
'20050282',
'35 Amp Converter',
'Electrical',
'Converter'
UNION
SELECT 0,
'20050282',
'Courtesy Light on Front',
'Electrical',
'Lighting'
UNION
SELECT 0,
'20050282',
'Battery Box & Wiring',
'Electrical',
'Wiring'
UNION
SELECT 0,
'20050282',
'Cable TV Ready',
'Electrical',
'Wiring'
--desired output
--sort InventoryItemId WebOptionName WebCategoryId WebSubCategoryId
--2 20050282 4 Stabiizer Jacks w/Sand Pads Chassis Option Leveling Systems
--4 20050282 Installed Door Step Chassis Option Steps
--6 20050282 Spare Tire & Mount Chassis Option Wheels & Tires
--7 20050282 13" Radial Tires Chassis Option Wheels & Tires
--8 20050282 Aluminum Wheels Chassis Option Wheels & Tires
--2 20050282 Completely EnclosedConstruction Chassis Const
--3 20050282 Aerodynamic Diamond Plate Front Construction Chassis Const
--4 20050282 Fiberglass Exterior Construction Chassis Const
--5 20050282 Fiberglass Laminated Roof Construction Chassis Const
--2 20050282 Positive Latches & Catches on Door & Drawers Construction Doors
--2 20050282 Large Tinted Skylight w/Shade Construction Windows
--2 20050282 35 Amp Converter Electrical Converter
--2 20050282 Courtesy Light on Front Electrical Lighting
--2 20050282 Battery Box & Wiring Electrical Wiring
--3 20050282 Cable TV Ready Electrical Wiring
SELECT *
FROM #temp
ORDER BY [WebCategoryId],
[WebSubCategoryId]
--drop table #temp
March 14, 2012 at 2:17 pm
Nope, I don't see what you are trying to accomplish. Might help if the expected results were formatted to see what values are in what column.
March 14, 2012 at 2:18 pm
that didnt come out formatted like i had it. i need to fix it first.
March 14, 2012 at 2:31 pm
i've added the desired output as an excel. It appears not only was my formatting jacked up but my output was incorrect. The excel copy is correct. thanks guys
March 14, 2012 at 2:35 pm
going to try and verbally explain this so bare w/ me. For each WebCategoryId i have multiple WebSubCategoryIds who have multiple WebOptionNames.
When a new category and sub category is found i need the sort to start w/ two. If the next record has the same category and sub category then increment by 1 however if the next record has the same category and different subcategory then increment by 2. Lastly if the next record has a new category then stat at 2 again and repeat the process.
March 14, 2012 at 3:58 pm
ok...I can see what you are trying to achieve....but why the increment by 2?
what is the business case here...........??
just curious
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 14, 2012 at 6:03 pm
Thanks for setting up the data
Here is my 2-cents 🙂
; WITH CTE AS
(
SELECT
[sort]
,[InventoryItemId]
,[WebOptionName]
,[WebCategoryId]
,[WebSubCategoryId]
,AnchorRN = ( ROW_NUMBER() OVER ( PARTITION BY T.WebCategoryId ORDER BY T.WebCategoryId , T.WebSubCategoryId , T.WebOptionName ) ) * 2
,CategRN = ( ROW_NUMBER() OVER ( PARTITION BY T.WebCategoryId , T.WebSubCategoryId ORDER BY T.WebCategoryId , T.WebSubCategoryId ) )
,RN = ROW_NUMBER() OVER ( PARTITION BY T.WebCategoryId ORDER BY T.WebCategoryId , T.WebSubCategoryId , T.WebOptionName)
,GroupCt = COUNT(*) OVER ( PARTITION BY T.WebCategoryId , T.WebSubCategoryId)
FROM #temp T
)
SELECT *
, NewSort = CASE
WHEN C.GroupCt = 1 THEN AnchorRN
ELSE (AnchorRN - C.CategRN) + 1
END
- ISNULL (
CASE WHEN C.GroupCt = 1 THEN ( ( SELECT TOP 1 Subq .GroupCt
FROM CTE Subq
WHERE Subq.WebCategoryId = C.WebCategoryId
AND Subq.RN < C.RN
ORDER BY C.RN DESC ) - 1 )
ELSE 0
END , 0 )
FROM CTE C
ORDER BY C.WebCategoryId , C.WebSubCategoryId , C.WebOptionName
I guess this will suck over large dataset, but will serve as a good starting point i believe.
March 14, 2012 at 6:53 pm
Take 2! 😀
; with cte as
(
SELECT
[sort]
,[InventoryItemId]
,[WebOptionName]
,[WebCategoryId]
,[WebSubCategoryId]
,AnchorRN = ( ROW_NUMBER() OVER ( PARTITION BY T.WebCategoryId ORDER BY T.WebCategoryId , T.WebSubCategoryId , T.WebOptionName ) ) * 2
,CategRN = ( ROW_NUMBER() OVER ( PARTITION BY T.WebCategoryId , T.WebSubCategoryId ORDER BY T.WebCategoryId , T.WebSubCategoryId ) )
,GroupCt = COUNT(*) OVER ( PARTITION BY T.WebCategoryId , T.WebSubCategoryId)
FROM #temp T
)
SELECT --c.InventoryItemId , C.WebOptionName ,C.WebCategoryId , C.WebSubCategoryId
*
, NewSort = CASE
WHEN C.GroupCt = 1 THEN C.AnchorRN
ELSE (C.AnchorRN - C.CategRN) + 1
END
- ISNULL (
CASE WHEN C.GroupCt = 1 THEN ( ( SELECT TOP 1 Subq .GroupCt
FROM CTE Subq
WHERE Subq.WebCategoryId = C.WebCategoryId
AND (Subq.AnchorRN / 2) < (C.AnchorRN / 2)
AND Subq.GroupCt <> 1
ORDER BY (Subq.AnchorRN / 2) DESC ) - 1 )
ELSE 0
END , 0 )
FROM cte C
ORDER BY C.WebCategoryId , C.WebSubCategoryId , C.WebOptionName
I added extra scenarios to the test data and this query flops :w00t: Recursive CTE is the way to go for this then !
March 15, 2012 at 6:33 am
Cold Coffee. Nice work. I thought about this last night and had a fresh solution ready to bang out today. I wasnt going to use CTE however probably should have. I'm going to try and consume ur work today. Thanks for your help on this. I may work out my solution and do a comparison between the two for good measure and if i do i'll post it as food for thought.
Again, thanks...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply