shani19831 (9/11/2012)
Hi there,The image you posted is not opopeningp dude.
Regards,
The image does show when you access the link. Interesting problem but it won't be easy to come up with a general solution for n levels. However I can give you a start by solving only the 4 levels shown.
First though, when posting on this forum you really do need to try to give us some DDL and sample data in consumable form as follows:
DECLARE @t TABLE
(node VARCHAR(10), parent VARCHAR(10), value INT)
INSERT INTO @t
SELECT 'lv3a', 'lv2a', 15
UNION ALL SELECT 'lv3b', 'lv2a', 4
UNION ALL SELECT 'lv3c', 'lv2b', 0
UNION ALL SELECT 'lv3d', 'lv2c', 10
UNION ALL SELECT 'lv3e', 'lv2d', 2
UNION ALL SELECT 'lv3f', 'lv2d', 15
UNION ALL SELECT 'lv2a', 'lv1a', 3
UNION ALL SELECT 'lv2b', 'lv1a', 5
UNION ALL SELECT 'lv2c', 'lv1a', 7
UNION ALL SELECT 'lv2d', 'lv1b', 30
UNION ALL SELECT 'lv1a', 'root', 30
UNION ALL SELECT 'lv1b', 'root', 10
UNION ALL SELECT 'root', NULL, 100
Since, as I said the problem looked mighty interesting, I decided to take care of this for you, so I could try to come up with what follows:
;WITH rCTE AS (
SELECT lvl=1, node, parent, value
FROM @t
WHERE parent IS NULL
UNION ALL
SELECT lvl+1, a.node, a.parent, a.value
FROM @t a
INNER JOIN rCTE b ON b.node = a.parent
),
SumsLvl4 AS (
SELECT parent, value=SUM(value)
FROM rCTE
WHERE lvl = 4
GROUP BY parent
),
SumsLvl3 AS (
SELECT a.parent, value=ABS(a.value - b.value)
FROM rCTE a
INNER JOIN SumsLvl4 b ON a.node = b.parent
),
SumsLvl2 AS (
SELECT a.parent,value=ABS(a.value - b.value)
FROM rCTE a
INNER JOIN (
SELECT parent, value=SUM(value)
FROM SumsLvl3
GROUP BY parent
) b ON a.node = b.parent
)
SELECT a.parent,value=a.value - b.value
FROM rCTE a
INNER JOIN (
SELECT parent, value=SUM(value)
FROM SumsLvl2
GROUP BY parent
) b ON a.node = b.parent
The next thing you'll say is that you want to see all the intermediate results and/or handle more than 4 levels, at which point I'll probably have to say ... best of luck to you mate!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St