August 21, 2015 at 1:00 am
Hey Steve,
Happy Friday!
Yes the order is irrelevant as the BenID can go through various combinations with the BenIDNew (these are Beneficiaries that are transacting).
Your solution is much more efficient than what I have come up with.
The results from your modified script are almost correct, they are missing two BenID's, namely 138 and 139.
These two need to be included in the results, because they are linked to BenID 125, and BenID 125 is part of the final output.
Remember, what I am trying to do is start with a BenID and find all combinations that this BenID affects by looking at either the BenID or BenIDNew of the transactions.
This has to keep going until there are no relations left. You look through the original parent to the child relationships and their children and so on.
If you follow the Flow:
BenID 198 moves to BenIDNew 199
BenID 197 relates to BenIDNew 198 (so must also be included)
BenID 197 relates to BenID 129
BenID 129 relates to BenID's 138,139 and 125
BenID 125 ultimately ends up on BenID 114.
you have to look through the links from parent to child to children's relations, bearing in mind that the relationship could be on BenID or BenIDNew.
So if you pass in any of the ID's that gets returned from ID 114, you should get the full list of 8 ID's back.
I know I have probably not explained it very well, it is an odd scenario, but I need to get the full effect right the way down to the lowest level and any related links along the way.
Hope this makes more sense!
Glad you have something that works. I did go back and test, and sure enough, ID 198 fails, so I re-examined what I was doing, and realized that the problem was related to the way I filtered the original set of data. As I also realized that the order in which the values appear is irrelevant, it was easy to add a couple of CASE statements that could force the value order to be smaller first, larger second. Then I realized that the TranID from the original table would have to get replaced with a ROW_NUMBER(), so that was added to the mix as well, and voila, ID 198 passes the test. This appears to better from a statistics IO perspective as well.
August 25, 2015 at 7:57 am
Bruceo (8/21/2015)
Hey Steve,Happy Friday!
Yes the order is irrelevant as the BenID can go through various combinations with the BenIDNew (these are Beneficiaries that are transacting).
Your solution is much more efficient than what I have come up with.
The results from your modified script are almost correct, they are missing two BenID's, namely 138 and 139.
These two need to be included in the results, because they are linked to BenID 125, and BenID 125 is part of the final output.
Remember, what I am trying to do is start with a BenID and find all combinations that this BenID affects by looking at either the BenID or BenIDNew of the transactions.
This has to keep going until there are no relations left. You look through the original parent to the child relationships and their children and so on.
If you follow the Flow:
BenID 198 moves to BenIDNew 199
BenID 197 relates to BenIDNew 198 (so must also be included)
BenID 197 relates to BenID 129
BenID 129 relates to BenID's 138,139 and 125
BenID 125 ultimately ends up on BenID 114.
you have to look through the links from parent to child to children's relations, bearing in mind that the relationship could be on BenID or BenIDNew.
So if you pass in any of the ID's that gets returned from ID 114, you should get the full list of 8 ID's back.
I know I have probably not explained it very well, it is an odd scenario, but I need to get the full effect right the way down to the lowest level and any related links along the way.
Hope this makes more sense!
I understood it okay... I just couldn't quite connect that some numbers path through the maze, so to speak, was inherently different from the path for other numbers, and so when I realized that for id 198, my code had failed, I just adjusted it and got it to work for 198, not even realizing it wasn't actually working correctly. Having had some considerable additional time to mess with it, I did finally come up with code that works for all the possible single ID value sample data choices, as well as the original choice of two values. Not sure you'll want to invest any more effort into this, given that you have a working solution, but here's what I came up with:
CREATE TABLE #Trans (
TranID int identity(1,1),
BenID int,
BenIDNew int
);
INSERT INTO #Trans (BenID, BenIDNew)
VALUES (114,125),
(125,114),
(125,129),
(129,197),
(138,125),
(139,125),
(197,198),
(198,199),
(125,114),
(129,197),
(198,199),
(134,135);
SELECT TranID, BenID, BenIDNew
FROM #Trans
SELECT
CASE WHEN T.BenID < T.BenIDNew THEN T.BenID ELSE T.BenIDNew END AS BenID,
CASE WHEN T.BenID < T.BenIDNew THEN T.BenIDNew ELSE T.BenID END AS BenIDNew,
--ROW_NUMBER() OVER(ORDER BY CASE WHEN T.BenID < T.BenIDNew THEN T.BenID ELSE T.BenIDNew END DESC,
--CASE WHEN T.BenID < T.BenIDNew THEN T.BenIDNew ELSE T.BenID END DESC) AS TranID2,
ROW_NUMBER() OVER(ORDER BY CASE WHEN T.BenID < T.BenIDNew THEN T.BenID ELSE T.BenIDNew END,
CASE WHEN T.BenID < T.BenIDNew THEN T.BenIDNew ELSE T.BenID END) AS TranID
FROM #Trans AS T
WHERE NOT EXISTS (SELECT 1 FROM #Trans AS T2 WHERE T2.BenID = T.BenIDNew AND T2.BenIDNew = T.BenID AND T2.TranID < T.TranID)
GROUP BY CASE WHEN T.BenID < T.BenIDNew THEN T.BenID ELSE T.BenIDNew END,
CASE WHEN T.BenID < T.BenIDNew THEN T.BenIDNew ELSE T.BenID END
SET STATISTICS IO ON;
DECLARE @BenID AS int = null, @BenIDs AS xml = '<IDs><ID>114</ID><ID>134</ID></IDs>';
SELECT ids.ID.value('.','Int') AS ID
INTO #BenIDList
FROM @BenIDs.nodes('/IDs/ID') AS ids(ID);
WITH cteTrans AS (
SELECT
CASE WHEN T.BenID < T.BenIDNew THEN T.BenID ELSE T.BenIDNew END AS BenID,
CASE WHEN T.BenID < T.BenIDNew THEN T.BenIDNew ELSE T.BenID END AS BenIDNew,
--ROW_NUMBER() OVER(ORDER BY CASE WHEN T.BenID < T.BenIDNew THEN T.BenID ELSE T.BenIDNew END DESC,
--CASE WHEN T.BenID < T.BenIDNew THEN T.BenIDNew ELSE T.BenID END DESC) AS TranID2
ROW_NUMBER() OVER(ORDER BY CASE WHEN T.BenID < T.BenIDNew THEN T.BenID ELSE T.BenIDNew END,
CASE WHEN T.BenID < T.BenIDNew THEN T.BenIDNew ELSE T.BenID END) AS TranID
FROM #Trans AS T
WHERE NOT EXISTS (SELECT 1 FROM #Trans AS T2 WHERE T2.BenID = T.BenIDNew AND T2.BenIDNew = T.BenID AND T2.TranID < T.TranID)
GROUP BY CASE WHEN T.BenID < T.BenIDNew THEN T.BenID ELSE T.BenIDNew END,
CASE WHEN T.BenID < T.BenIDNew THEN T.BenIDNew ELSE T.BenID END
),
AllBens AS (
-- ANCHOR for recursion uses ID List and grabs any connected records
SELECT T.BenID, T.TranID
FROM #BenIDList AS BL
INNER JOIN cteTrans AS T
ON BL.ID IN (T.BenID, T.BenIDNew)
UNION ALL
-- Recursive member to get records elsewhere on the list on the other side but from the same BenID
SELECT CT.BenIDNew, CT.TranID
FROM cteTrans AS CT
INNER JOIN AllBens AS AB
ON CT.BenID = AB.BenID
AND CT.TranID = AB.TranID
UNION ALL
--Recursive member to get records elsewhere on the list on this side but from a different BenID
SELECT CT.BenIDNew, CT.TranID
FROM cteTrans AS CT
INNER JOIN AllBens AS AB
ON CT.BenIDNew = AB.BenID
AND CT.TranID <> AB.TranID
UNION ALL
--Recursive member to get same ID records elsewhere in the list
SELECT CT.BenIDNew, CT.TranID
FROM cteTrans AS CT
INNER JOIN AllBens AS AB
ON CT.BenID = AB.BenID
AND CT.BenIDNew <> AB.BenID
AND CT.TranID <> AB.TranID
UNION ALL
--Recursive member to pick up rows below that get missed by the rest
SELECT CT.BenID, CT.TranID
FROM cteTrans AS CT
INNER JOIN AllBens AS AB
ON CT.BenIDNew = AB.BenID
AND CT.TranID < AB.TranID
)
SELECT DISTINCT BenID
FROM AllBens;
SET STATISTICS IO OFF;
DROP TABLE #Trans
DROP TABLE #BenIDList
Here's the results:
BenID
114
125
129
134
135
138
139
197
198
199
And here's the stats:
Table '#Trans______________________________________________________________________________________________________________0000000001F9'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(12 row(s) affected)
(12 row(s) affected)
Table '#Trans______________________________________________________________________________________________________________0000000001F9'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(8 row(s) affected)
Table '#Trans______________________________________________________________________________________________________________0000000001F9'. Scan count 2, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(2 row(s) affected)
(10 row(s) affected)
Table 'Worktable'. Scan count 2, logical reads 59, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Trans______________________________________________________________________________________________________________0000000001F9'. Scan count 82, logical reads 533, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#BenIDList__________________________________________________________________________________________________________0000000001FA'. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 25, 2015 at 8:47 am
That looks much better than my attempt.
Agreed, this solution took a lot of time to get to.
Thanks for the help
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply