SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Recursive SQL Query help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

anchoredwisdom
Starting Member

22 Posts

Posted - 06/14/2013 :  13:56:27  Show Profile  Reply with Quote
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

Edited by - anchoredwisdom on 06/14/2013 14:03:01

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 06/14/2013 :  14:53:01  Show Profile  Reply with Quote
;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 ;
Go to Top of Page

anchoredwisdom
Starting Member

22 Posts

Posted - 06/15/2013 :  01:03:39  Show Profile  Reply with Quote
James,
Many thanks for your help
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/15/2013 :  10:31:20  Show Profile  Reply with Quote
If you have SQL Server 2012 you can take advantage of LEAD() & LAG() functions.

 
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, ''));

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000