Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Recursive SQL Query help

Author  Topic 

anchoredwisdom
Starting Member

22 Posts

Posted - 2013-06-14 : 13:56:27
create table policy
(policy_key varchar(10),
policy_nbr varchar(15),
prior_policy_nbr varchar(15)
)
/

insert into policy values ('301','P0001','NA')
insert into policy values ('302','P0002','P0001')
insert into policy values ('303','P0003','P0002')
insert into policy values ('304','P0004','P0003')
insert into policy values ('305','P0005','P0004')
insert into policy values ('306','P0006','NA')
insert into policy values ('307','P0007','NA')


My requirement is
i have to get the initial policy number and latest policy number

example for P0001 is initial policy number and for this the latest is P0005
for P0006 and P0007 they are the initial and final values

hence the output is

POLICY_NBR PRIOR_POLICY_NBR
p0005 p0001
P0006 NA
P0007 NA


Your help is highly appreciated

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-14 : 14:53:01
[code];WITH cte AS
(
SELECT
1 AS LVL,
ROW_NUMBER() OVER (ORDER BY policy_key) AS RN,
policy_nbr,
prior_policy_nbr,
policy_nbr AS firstPriorPolicy
FROM policy
WHERE prior_policy_nbr = 'NA'

UNION ALL
SELECT
LVL+1,
RN,
p.policy_nbr,
p.prior_policy_nbr,
CASE WHEN c.firstPriorPolicy = 'NA' THEN c.policy_nbr ELSE c.firstPriorPolicy END
FROM policy p
INNER JOIN cte c ON c.policy_nbr = p.prior_policy_nbr
),
cte2 AS
(
SELECT
policy_nbr,
CASE WHEN firstPriorPolicy = policy_Nbr THEN prior_policy_nbr ELSE firstPriorPolicy END AS prior_policy_nbr,
ROW_NUMBER() OVER (PARTITION BY RN ORDER BY LVL DESC) AS RN2
FROM cte
)
SELECT policy_nbr, prior_policy_nbr FROM cte2 WHERE RN2 = 1 ;[/code]
Go to Top of Page

anchoredwisdom
Starting Member

22 Posts

Posted - 2013-06-15 : 01:03:39
James,
Many thanks for your help
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-15 : 10:31:20
If you have SQL Server 2012 you can take advantage of LEAD() & LAG() functions.
[CODE]

SELECT policy_nbr, PriorPolicyNumber FROM
(SELECT *, (CASE WHEN prior_policy_nbr <> 'NA' THEN LAG(policy_nbr) OVER(ORDER BY (SELECT(1)))
ELSE 'NA' END) AS PriorPolicyNumber FROM
(SELECT *, LEAD(prior_policy_nbr) OVER(ORDER BY (SELECT(1))) AS leadingelement,
LAG(prior_policy_nbr) OVER(ORDER BY (SELECT(1))) AS lagingelement from Policy) A
WHERE (prior_policy_nbr = 'NA' OR leadingelement = 'NA')) B
WHERE (policy_nbr <> COALESCE(leadingelement, ''));

[/CODE]
Go to Top of Page
   

- Advertisement -