February 21, 2013 at 11:17 am
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.
February 21, 2013 at 12:24 pm
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
February 22, 2013 at 2:35 am
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.
February 22, 2013 at 1:11 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply