February 14, 2007 at 1:00 pm
Folks,
I'm attempting to use the recursive CTE functionality in Sql Server 2005 to chase down a set of relationships among people who have borrowed money together.
The original table looks something like:
create table borrowers(
Borrower int,
loan_number int,
loan_role char(1))
So the data looks like
1,1,p -- borrower 1 is the primary borrower for loan 1
2,1,j -- borrower 2 is a joint borrower on account 1
2,2,p -- borrower 2 is the primary borrower on loan 2
3,2,j -- borrower 3 is a joint borrower on loan 2
4,4,p -- borrower 4 is the primary borrower on loan 4
The data set I need would be all the rows for which borrower 1 and any of his joint borrowers (and any of their joint borrowers, and so on...) had any responsibility. So, with the above data, I'd get back all rows except 4,4,p.
I've tried working with the CTE example in BOL and I always end up with infinite recursion. It can't be that complex, but I've been pounding my head on this for long enough that I'm starting to doubt my ability to conceptualize problems and translate them into workable code. That would imply a move into management and I'd like to put that off as long as possible.
Any help would be appreciated.
February 15, 2007 at 11:10 am
I also have difficulty getting my head around this type of query.
The following may not be correct but seems to work on the data provided. There are probably better solutions.
DECLARE @t TABLE
(
Borrower int NOT NULL
,loan_number int NOT NULL
,loan_role char(1) NOT NULL
)
INSERT INTO @t
SELECT 1, 1, 'P' UNION ALL
SELECT 2, 1, 'J' UNION ALL
SELECT 2, 2, 'P' UNION ALL
SELECT 3, 2, 'J' UNION ALL
SELECT 4, 4, 'P'
DECLARE @Borrower int
SET @Borrower = 1
;WITH BorrowerResponsibility (Borrower, loan_number)
AS
(
SELECT Borrower, loan_number
FROM @t
WHERE Borrower = @Borrower
UNION ALL
SELECT T.Borrower, T.loan_number
FROM @t T
JOIN (
SELECT T1.Borrower, T1.loan_number
FROM @t T1
JOIN BorrowerResponsibility R1
ON T1.loan_number = R1.loan_number
AND T1.Borrower <> R1.Borrower
) D
ON T.Borrower = D.Borrower AND T.loan_number <> D.loan_number
)
SELECT T.*
FROM @t T
JOIN BorrowerResponsibility B
ON T.loan_number = B.loan_number
-- OPTION (MAXRECURSION 3)
February 15, 2007 at 11:36 am
You, Sir, are a fine and shining example of the best of this forum and I owe you at least a beer if we meet up at a conference.
Thanks a bunch!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply