SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recursion but with some bits ignored


Recursion but with some bits ignored

Author
Message
webskater
webskater
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 479
Sorry for the subject, can't think of a better way of describing what I am trying to do. This is a sort of follow-up to a question I asked recently about sorting a recursion - which I have done okay (and thanks again to those that helped me on that.) If I start with the same data ...


DECLARE @DateLevel int = 0, @DateLevelUp int, @FTID int = 8, @TopBaseID int

CREATE TABLE #FieldRels
(
FTIDBase int,
FTIDCalc int,
Interval int
)

INSERT INTO #FieldRels(FTIDBase, FTIDCalc)
SELECT 6, 7 UNION ALL
SELECT 7, 8 UNION ALL
SELECT 7, 12 UNION ALL
SELECT 8, 13 UNION ALL
SELECT 6, 5 UNION ALL
SELECT 8, 9 UNION ALL
SELECT 9, 16

/*this data shows a relationship between the first and second columns.
So:
7 (second column, first row) relates to 6 (first column, first row)
8 relates to 7
12 relates to 7
13 relates to 8
5 relates to 6
9 relates to 8
16 relates to 9

I'm interested in the relationships 'above' and 'below' Item 8 (which is why @FTID is hard coded to 8)*/

IF EXISTS(SELECT 0 FROM #FieldRels WHERE FTIDCalc = @FTID) --the field passed in has a parent - work up the chain to the parent
BEGIN
;WITH rCTE(FTIDBase, FTIDCalc, DateLevel) AS
(
SELECT FTIDBase, FTIDCalc, 1 AS DateLevel
FROM #FieldRels
WHERE FTIDCalc = @FTID
UNION ALL
SELECT e.FTIDBase, e.FTIDCalc, DateLevel + 1
FROM #FieldRels e
INNER JOIN rCTE c ON e.FTIDCalc = c.FTIDBase
)
SELECT TOP 1 @TopBASEID = FTIDBase FROM rCTE ORDER BY DateLevel DESC
END
ELSE
BEGIN
SET @TopBASEID = @FTID
END

--@TopBaseID is the ID of the 'highest' field above Item 8 that it relates to
--now work down from there to get all the relationships above and below Item 8

;WITH rCTE(FTIDCalc, FTIDBase, DateLevel, SortKey) AS
(
SELECT TOP 1 FTIDBase, FTIDBase, 1 AS DateLevel, CAST('\'+CAST(FTIDBase AS VARCHAR(10)) AS VARCHAR(4000))
FROM #FieldRels
WHERE FTIDBase = @TopBASEID
UNION ALL
SELECT e.FTIDCalc, e.FTIDBase, DateLevel + 1, CAST(d.SortKey + '\'+CAST(e.FTIDCalc AS VARCHAR(10)) AS VARCHAR(4000))
FROM #FieldRels e
INNER JOIN rCTE d ON e.FTIDBase = d.FTIDCalc
)

--this is the recordset that populates the Gridview below the Stage Dates on each stage (shown when a user clicks on the Field Description so they
--can see what date relationships the field is in.
SELECT r.FTIDBase, r.FTIDCalc, DateLevel, CASE WHEN @FTID = r.FTIDCalc THEN 'true' ELSE 'false' END AS [ThisOne]
FROM rCTE r
ORDER BY SortKey


DROP TABLE #FieldRels



The code above returns:
FTIDBase-----FTIDCalc-----DateLevel
6------------------6---------------1
6------------------5---------------2
6------------------7---------------2
7------------------12---------------3
7------------------8---------------3
8------------------13---------------4
8------------------9---------------4
9------------------16---------------5

But, I don't want some of these rows:
6------------------6---------------1
6------------------5---------------2 /// I don't want this one because it is above 8 but does not lead down to Item 8
6------------------7---------------2
7------------------12---------------3 /// I don't want this one because it is level with Item 8
7------------------8---------------3
8------------------13---------------4 // I do want this as it is below Item 8
8------------------9---------------4 // I do want this as it is below Item 8
9------------------16---------------5 // I do want this as it is below Item 8

Using recursion to go 'up' the data to the highest related item and then using recursion to go 'down' the data to the lowest related data gives me extraneous rows. Having gone 'up' the data to Item 6, on the way down I am not interested in the fact that Item 5 relates to Item 6 because Item 5 does not lead me back down to Item 8.

However, once I reach Item 8 I do want to see every item below it.
mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1556 Visits: 3317
Hi

You're very close to getting the result you want, you just need to combine the results of your drill up with a drill down from FTID, rather than drilling down from the TopBaseID

Something like:
;WITH rDrillUp AS (
SELECT FTIDBase, FTIDCalc, 0 datelevel
FROM #fieldrels
WHERE FTIDCalc = 7
UNION ALL
SELECT fr.FTIDBase, fr.FTIDCalc, datelevel - 1 -- Down Count
FROM rDrillUp du
INNER JOIN #fieldrels fr ON du.FTIDBase = fr.FTIDCalc
)
,rDrillDown AS (
SELECT FTIDBase, FTIDCalc, 0 datelevel
FROM #fieldrels
WHERE FTIDCalc = 7
UNION ALL
SELECT fr.FTIDBase, fr.FTIDCalc, datelevel + 1 -- Up Count
FROM rDrillDown du
INNER JOIN #fieldrels fr ON fr.FTIDBase = du.FTIDCalc
)
,combined AS (
SELECT FTIDBase, FTIDCalc, DENSE_RANK() OVER (ORDER BY DateLevel) + 1 DateLevel
FROM (
SELECT * FROM rDrillUp
UNION
SELECT * FROM rDrillDown
) a
)
SELECT FTIDBase, FTIDBase FTIDCalc, 1
FROM combined
WHERE DateLevel = 2
UNION ALL
SELECT FTIDBase, FTIDCalc, DateLevel
FROM combined


webskater
webskater
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 479
Thank you very much for your help. I was under the impression that after using a CTE, you had to SELECT from it. I didn't realise you could create multiple CTEs and then combine them as you have.
mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1556 Visits: 3317
Yep, you can have multiple queries in your cte. It can make things a lot easier to read and understand, but sometimes I think it may have an affect on performance.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search