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.
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 P0005for P0006 and P0007 they are the initial and final values hence the output is POLICY_NBR PRIOR_POLICY_NBRp0005 p0001P0006 NAP0007 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] |
|
|
anchoredwisdom
Starting Member
22 Posts |
Posted - 2013-06-15 : 01:03:39
|
James, Many thanks for your help |
|
|
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] |
|
|
|
|
|
|
|