April 2, 2013 at 1:04 am
For Insurance where logic is something like after renewing a policy that renewed policy is current policy and previous policy will not be current anymore. Now want to show customer this last two records i.e. renewed policy as well as original policy which was renewed. And while renewing current policy of original policy will become previous policy for renewed policy.
query i am using to show current policy only or renewed policy is something like:
select * from table_name where is_current='Y'
Modified to
select * from table_name where person_id=somevalue OR
Prev_PolNO=Curr_PolNo
but still it doesn't give correct result.
So can same be achieved.......
April 2, 2013 at 1:12 am
We don't have any idea how your table structures look like and hence, it will be very difficult to assist you
It would be really helpful if you provide the DDL of the tables involved along with some sample data and expected results
If you have any doubts on how to do this, you can refer the link in my signature
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 2, 2013 at 8:25 am
If you have a date to order the rows, then something like this could work.
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Customerid ORDER BY PolicyDateDate DESC) AS PolicyRank
FROMdbo.CustomerPolicy
) AS a
WHEREa.PolicyRank <=2
April 3, 2013 at 3:58 pm
There are other more sophisticated ways to do this but here is a quickie example given a simple Policy table such as you describe and requiring no dates to work from. Basically it gets the list of all current policy numbers and then for each one of them their direct parent policy. Very simple, but effective. Sample table, data and code:-
CREATE TABLE policy (
policyid int identity,
holderid int not null,
policyno varchar(10) not null,
is_current bit not null,
prevpolicyno varchar(10) )
GO
INSERT INTO policy (holderid, policyno, is_current, prevpolicyno) VALUES
(1,'p1a',1,null),
(2,'p2a',0,null),
(2,'p2b',1,'p2a'),
(3,'p3a',0,null),
(3,'p3b',0,'p3a'),
(3,'p3c',0,'p3b'),
(3,'p3d',1,'p3c'),
(4,'p4a',1,null)
GO
;WITH curr (policyid, holderid, policyno, is_current, prevpolicyno) AS (
SELECT policyid, holderid, policyno, is_current, prevpolicyno
FROM policy
WHERE is_current = 1)
SELECT policyid, holderid, policyno, is_current, prevpolicyno
FROM curr
UNION
SELECT p.policyid, p.holderid, p.policyno, p.is_current, p.prevpolicyno
FROM policy p
JOIN curr c ON p.policyno = c.prevpolicyno
GO
April 4, 2013 at 1:08 am
Thnx @darrylc-ssc for your solution. It did worked....
April 4, 2013 at 2:13 am
darrylc-ssc (4/3/2013)
There are other more sophisticated ways to do this but here is a quickie example given a simple Policy table such as you describe and requiring no dates to work from. Basically it gets the list of all current policy numbers and then for each one of them their direct parent policy. Very simple, but effective. Sample table, data and code:-
CREATE TABLE policy (policyid int identity,
holderid int not null,
policyno varchar(10) not null,
is_current bit not null,
prevpolicyno varchar(10) )
GO
INSERT INTO policy (holderid, policyno, is_current, prevpolicyno) VALUES
(1,'p1a',1,null),
(2,'p2a',0,null),
(2,'p2b',1,'p2a'),
(3,'p3a',0,null),
(3,'p3b',0,'p3a'),
(3,'p3c',0,'p3b'),
(3,'p3d',1,'p3c'),
(4,'p4a',1,null)
GO
;WITH curr (policyid, holderid, policyno, is_current, prevpolicyno) AS (
SELECT policyid, holderid, policyno, is_current, prevpolicyno
FROM policy
WHERE is_current = 1)
SELECT policyid, holderid, policyno, is_current, prevpolicyno
FROM curr
UNION
SELECT p.policyid, p.holderid, p.policyno, p.is_current, p.prevpolicyno
FROM policy p
JOIN curr c ON p.policyno = c.prevpolicyno
GO
Wouldn't a Recursive CTE work better in this case as it saves the whole sort and distinct set of processes?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply