Selt referencing From and To records 1

[eo]

Hi,

I have a table with Policy Numbers, this links to a PolicyNumberLink table that holds an audit trail of those policy numbers, and states previous Policy Numbers and new Policy Numbers. By calling a particular Policy Number, I am hoping there is a way to return all its predecessors. I will try and explain by example:

Policy numbers can move from business system 1 to 2 to 3, and gets a new policy number of every new business system:

The PolicyNumbers as they move between BusinessSystems:
BusinessSystem1 > BusinessSystem2 > BusinessSystem3
1001001         > 2002001         > 3003001

These records are written to the Policy table:

PolicyTable:
Date       PolicyNumber
====       ============
01/01/2006 1001001
01/01/2007 2002001
01/01/2008 3003001

A PolicyNumber link table shows the audit trial:

PolicyNumberLinkTable:
PolicyNumber PrevPolicyNumber NewPolicyNumber
============ ================ ===============
1001001      1001001          2002001
2002001      1001001          3003001
3003001      2002001          NULL

If I want to select the current PolicyNymber...

where PolicyNumberLinkTable.PolicyNumber = 3003001

...how do I return all its predecessors?

Bu using a self referencing join on the PolicyNumberLinkTable from PrevPolicyNumber join PolicyNumber I am sure I can return the PolicyNumber 2002001, but I think the PolicyNumber 1001001 will be ignored

Any ideas

I work with SQL Server 2005 btw

EO
Hertfordshire, England

 

[jhaith]

Try this article

http://msdn.microsoft.com/en-us/library/aa175801.aspx

-jhaith

 

[bborissov]

And how do you know that PolicyNumber 1001001 became 2002001 and after that 3003001?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[eo]

Hi Borislav,

This will be handeled by ETL. In order bnot to go into complex business processes, assume for the sake of argument that the table structure havethis captured that PolicyNumber 1001001 became 2002001 and then 3003001.


EO
Hertfordshire, England

 

[SgtJarrow]

Since you are working with SQL2005, have you looked at a recursive CTE (Common Table Expression)???

While I haven't used it in the same context you need, I use a CTE to get employee hierarchy in my company. I can pass in a name of an employee at any level and find all his/her subordinates as far down the tree as I want to walk.

You would want to begin with a CTE off your link table...finding all "previous" numbers, and then use that CTE as the basis for your infomation gathering....

As an example, here is my employee hierarchy CTE. BOL should help you find your exact syntax...

WITH EmployeeHierarchy (LastName, FirstName, SupervisorName, DeptName, EmpID, HierarchyLevel) AS
(
   -- Base case
   SELECT
	LastName,
	FirstName,
	SupervisorName,
	DeptName,
	EmpId,
	  1 as HierarchyLevel
   FROM adp.EmpInfo
   WHERE Email = @email
		AND TermDate IS NULL
SUBSTRING(FirstName, 0, CHARINDEX(' ', FirstName)) END = @FirstName

   UNION ALL

   -- Recursive step
   SELECT
	e.LastName,
	e.FirstName,
	e.SupervisorName,
	e.DeptName,
	e.EmpId,
	  eh.HierarchyLevel + 1 AS HierarchyLevel
   FROM adp.EmpInfo e
	  INNER JOIN EmployeeHierarchy eh ON
		e.SupervisorId = CONVERT(NVARCHAR(8),eh.EmpID)
	WHERE TermDate IS null
)

SELECT z.*
FROM EmployeeHierarchy z
WHERE HierarchyLevel <> 1
ORDER BY HierarchyLevel, LastName, FirstName

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[eo]

The recursive CTE worked fine for gathering the policies from the link table. My challenge going forward is to retrieve all records from the Policy table based on that result.

EO
Hertfordshire, England

 

[eo]

I maneged to resolve the issue by turning the CTE result set into a distinct list of PolicyNumbers, I can therefore now link to the PolicyTable

WITH CTE (PolicyNumberFrom, SourceSystemFrom, PolicyNumberTo, SourceSystemTo)
			AS 
			(--BASE CASE
				SELECT BASE.PolicyNumberFrom, BASE.SourceSystemFrom, BASE.PolicyNumberTo, BASE.SourceSystemTo
				FROM dbo.EtTest_PolicyLinkingTest BASE
				WHERE BASE.PolicyNumberTo = 3003001
				
				UNION ALL

				--RECURSIVE STEP
				SELECT TBL.PolicyNumberFrom, TBL.SourceSystemFrom, TBL.PolicyNumberTo, TBL.SourceSystemTo
				FROM CTE CTE
				JOIN dbo.EtTest_PolicyLinkingTest TBL
					ON CTE.PolicyNumberFrom = TBL.PolicyNumberTo
			)

--SELECT PolicyNumberFrom, SourceSystemFrom, PolicyNumberTo, SourceSystemTo
SELECT DISTINCT PolicyNumber FROM (

SELECT PolicyNumberFrom as PolicyNumber
FROM CTE

UNION ALL

SELECT PolicyNumberTo as PolicyNumber
FROM CTE
) PolicyNumbers

EO
Hertfordshire, England