Showing Percentage and Ordering by SUM(Hierarchy)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Showing Percentage and Ordering by SUM(Hierarchy)

Author

Topic

rythm123us
Starting Member

27 Posts

Posted - 2005-09-17 : 12:35:35

I am sorry if the Subject is not very clear. Let me explain what I am trying.

1.I am trying to show Hierarchial Data.
2.I am also trying to order it such that the sum of a 2 items take precendece in the order.
3.I am also trying to show the percentage for each category and sub-catgeory (NO CLUE ON HOW TO DO THIS ONE).

I have managed to Item 1 and 2. However, I want to to check if there is a better way of getting the results for Item 2. My current query shows the results just fine.

I just want to see if there is a better way of doing Item 2 and have no idea how to show the percentages(Item 3). I want to show the first Percentage as part of the entire Sale, the second percentage is based on the Catgeory.

So the output should look like: (SalesID 20934 gives best explanantion)
SalesID,CategoryID,ParentID,CategoryText,SortOrder,Quantity,Price,Cost,Percentage,Sub-Percentage
20936,1,0,All,0,11,97.89,77.16,100%,100%
20936,37,1,Wine,3800,11,97.89,77.16,100%,100%
20936,38,37,Domestic,3900,11,97.89,77.16,100%,100%
20936,39,38,California,4000,11,97.89,77.16,100%,100%
20936,-1,39,"Woodbridge Chardonnay, 750 ml",4001,4,23.96,19.64,36%,36%
20936,-1,39,"Woodbridge Chardonnay, 750 ml",4001,2,13.98,9.82,18%,18%
20936,-1,39,"Woodbridge Chardonnay, 1.5 L",4001,5,59.95,47.7,45%,45%
20935,1,0,All,0,2,17.98,12.91,100%,100%
20935,37,1,Wine,3800,2,17.98,12.91,100%,100%
20935,38,37,Domestic,3900,2,17.98,12.91,100%,100%
20935,-1,38,"SHARPE HILL RED SERAPH, 750 ml",3901,1,10.99,8,50%,50%
20935,39,38,California,4000,1,6.99,4.91,50%,50%
20935,-1,39,"Woodbridge Chardonnay, 750 ml",4001,1,6.99,4.91,50%,100%
20934,1,0,All,0,6,58.94,42.96,100%,100%
20934,2,1,Beer,100,3,29.97,22.05,50%,50%
20934,3,2,Domestic,200,3,29.97,22.05,50%,100%
20934,-1,3,"BUD , 12 oz 12 pack Bottle(s)",201,3,29.97,22.05,50%,100%
20934,37,1,Wine,3800,3,28.97,20.91,50%,50%
20934,38,37,Domestic,3900,3,28.97,20.91,50%,100%
20934,-1,38,"SHARPE HILL RED SERAPH, 750 ml",3901,2,21.98,16,33%,67%
20934,39,38,California,4000,1,6.99,4.91,17%,33%
20934,-1,39,"Woodbridge Chardonnay, 750 ml",4001,1,6.99,4.91,17%,100%
20933,1,0,All,0,2,12.98,9.82,100%,100%
20933,37,1,Wine,3800,2,12.98,9.82,100%,100%
20933,38,37,Domestic,3900,2,12.98,9.82,100%,100%
20933,39,38,California,4000,2,12.98,9.82,100%,100%
20933,-1,39,"Woodbridge Chardonnay, 750 ml",4001,1,5.99,4.91,50%,50%
20933,-1,39,"Woodbridge Chardonnay, 750 ml",4001,1,6.99,4.91,50%,50%

RELATED TABLES AND QUERY:
DECLARE @Category TABLE(CategoryID INT, ParentID INT, CategoryText VARCHAR(55), Lineage VARCHAR(55), SortOrder INT)

INSERT @Category(CategoryID, ParentID, CategoryText, Lineage, SortOrder)
SELECT 1, 0, 'All', '1', 0 UNION ALL
SELECT 2, 1, 'Beer', '1.1', 1 UNION ALL
SELECT 3, 2, 'Domestic', '1.1.1', 2 UNION ALL
SELECT 37, 1, 'Wine', '1.4', 38 UNION ALL
SELECT 38, 37, 'Domestic', '1.4.1', 39 UNION ALL
SELECT 39, 38, 'California', '1.4.1.1', 40

DECLARE @Sales TABLE(SalesID INT, ProductID INT, Quantity INT, Price MONEY, Cost MONEY)

INSERT @Sales(SalesID, ProductID, Quantity, Price, Cost)
SELECT 20936, 1554, 2, 6.99, 4.91 UNION ALL
SELECT 20936, 1555, 5, 11.99, 9.54 UNION ALL
SELECT 20936, 1554, 4, 5.99, 4.91 UNION ALL
SELECT 20935, 1554, 1, 6.99, 4.91 UNION ALL
SELECT 20935, 721, 1, 10.99, 8 UNION ALL
SELECT 20934, 786, 1, 9.99, 7.35 UNION ALL
SELECT 20934, 786, 2, 9.99, 7.35 UNION ALL
SELECT 20934, 1554, 1, 6.99, 4.91 UNION ALL
SELECT 20934, 721, 2, 10.99, 8 UNION ALL
SELECT 20933, 1554, 1, 6.99, 4.91 UNION ALL
SELECT 20933, 1554, 1, 5.99, 4.91

DECLARE @Products TABLE(ProductID INT, Product VARCHAR(55), CategoryID INT)

INSERT @Products(ProductID, Product, CategoryID)
SELECT 1554, 'Woodbridge Chardonnay, 750 ml', 39 UNION ALL
SELECT 721, 'SHARPE HILL RED SERAPH, 750 ml', 38 UNION ALL
SELECT 786, 'BUD , 12 oz 12 pack Bottle(s)', 3 UNION ALL
SELECT 1555, 'Woodbridge Chardonnay, 1.5 L', 39

DECLARE @QuantityTest TABLE(SalesID INT, CategoryID INT, ParentID INT, CategoryText VARCHAR(60),
SortOrder INT, Quantity INT, Price MONEY, Cost MONEY)

INSERT @QuantityTest(SalesID, CategoryID, ParentID, CategoryText,
SortOrder, Quantity, Price, Cost)
SELECT
SalesID,
CategoryID,
ParentID,
CategoryText,
-- Lineage,
SortOrder,
SUM(Quantity) AS Quantity,
SUM(Price) AS Price,
SUM(Cost) AS Cost
-- PriceMonitor
FROM (
SELECT
A.PriceMonitor,
A.SalesID,
CASE
WHEN (A.GRP = 1) THEN C.CategoryID
ELSE -1
END AS CategoryID,
CASE
WHEN (A.GRP = 1) THEN C.ParentID
ELSE Max(ISNULL(A.ParentID,0))
END AS ParentID,
CASE
WHEN (A.GRP = 1) THEN C.CategoryText
ELSE A.Product
END AS CategoryText,
CASE
WHEN (A.GRP = 1) THEN MAX(ISNULL(C.Lineage,0))
ELSE MAX(A.Lineage)
END AS Lineage,
CASE
WHEN (A.GRP = 1) THEN ISNULL(C.SortOrder,0) * 100
ELSE MAX(A.SortOrder)
END AS SortOrder,
SUM(Quantity) AS Quantity,
SUM(Price) AS Price,
SUM(Cost) AS Cost
FROM
@Category C
RIGHT JOIN (
SELECT
CASE
WHEN P.CategoryID <> -1 THEN Price
ELSE P.CategoryID
END AS PriceMonitor,
SP.SalesID,
MAX(P.CategoryID) AS ParentID,
SUM(SP.Quantity) AS Quantity,
SUM(SP.Price*SP.Quantity) AS Price,
SUM(SP.Cost*SP.Quantity) AS Cost,
CASE
WHEN (GROUPING(P.Product) = 1) THEN
CASE
WHEN(C.Lineage IS NULL) THEN 0
ELSE Max(C.SortOrder) * 100
END
ELSE (Max(C.SortOrder) * 100) + 1
END AS SortOrder,
GROUPING(P.Product) as grp,
GROUPING(SP.Price) as grp2,
P.Product AS Product,
CASE
WHEN (GROUPING(P.Product) = 1) THEN ISNULL(C.Lineage,0)
ELSE 'X.' + ISNULL(C.Lineage,0)
END AS Lineage,
CASE
WHEN(GROUPING(P.Product) = 1) THEN
CASE
WHEN(C.Lineage IS NULL) THEN 'UNKNOWN'
ELSE MAX(C.CategoryText)
END
ELSE ISNULL(P.Product, 'UNKNOWN')
END AS Category
FROM
@Sales SP
INNER JOIN @Products P ON SP.ProductID = P.ProductID
INNER JOIN @Category C ON P.CategoryID = C.CategoryID
GROUP BY
SP.SalesID,
C.Lineage,
P.Product,
SP.Price,
P.CategoryID
WITH ROLLUP
HAVING
((GROUPING(SP.Price) = 0)
OR (GROUPING(SP.Price) = GROUPING(P.Product)))
) A ON SUBSTRING(A.Lineage, 1, LEN(C.Lineage)) = C.Lineage
GROUP BY
A.SalesID,
C.ParentID,
C.CategoryID,
C.CategoryText,
C.SortOrder,
A.Product,
A.GRP,
A.Price,
A.PriceMonitor) Z
WHERE
CategoryID IS NOT NULL
AND (CategoryID <> -1 OR PriceMonitor IS NOT NULL)
GROUP BY
SalesID,
CategoryID,
ParentID,
CategoryText,
Lineage,
SortOrder,
PriceMonitor
ORDER BY
SalesID DESC,
SortOrder,
Price

-- FINAL SELECT
SELECT Q.SalesID, Q.CategoryID, Q.ParentID, Q.CategoryText,
Q.SortOrder, Q.Quantity, Q.Price, Q.Cost
FROM
@QuantityTest Q
INNER JOIN
(
SELECT SalesID, CategoryText,
[SUM_QTY] = SUM(quantity)
FROM @QuantityTest
GROUP BY SalesID, CategoryText
) S
ON S.SalesID = Q.SalesID AND
S.CategoryText = Q.CategoryText
ORDER BY
Q.SalesID DESC,
Q.SortOrder,
S.SUM_QTY DESC,
Q.quantity DESC

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-19 : 01:51:23

See if these are helpful
http://www.nigelrivett.net/RetrieveTreeHierarchy.html
http://www.seventhnight.com/treestructs.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources