Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Recursion but with some bits ignored Expand / Collapse
Author
Message
Posted Thursday, February 21, 2013 11:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, November 8, 2014 4:15 AM
Points: 193, Visits: 414
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.
Post #1422726
Posted Thursday, February 21, 2013 12:24 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:24 PM
Points: 1,081, Visits: 3,171
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

Post #1422746
Posted Friday, February 22, 2013 2:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, November 8, 2014 4:15 AM
Points: 193, Visits: 414
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.
Post #1422932
Posted Friday, February 22, 2013 1:11 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:24 PM
Points: 1,081, Visits: 3,171
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.
Post #1423234
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse