Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to write custom query which shows last two records added only. Expand / Collapse
Author
Message
Posted Tuesday, April 2, 2013 1:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 4, 2013 1:08 AM
Points: 10, Visits: 33
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.......
Post #1437720
Posted Tuesday, April 2, 2013 1:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 2,661, Visits: 4,734
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



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1437723
Posted Tuesday, April 2, 2013 8:25 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 26, 2014 10:52 AM
Points: 547, Visits: 1,664
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
FROM dbo.CustomerPolicy
) AS a
WHERE a.PolicyRank <=2

Post #1437926
Posted Wednesday, April 3, 2013 3:58 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 1:44 PM
Points: 1,481, Visits: 210
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

Post #1438606
Posted Thursday, April 4, 2013 1:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 4, 2013 1:08 AM
Points: 10, Visits: 33
Thnx @darrylc-ssc for your solution. It did worked....
Post #1438662
Posted Thursday, April 4, 2013 2:13 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 887, Visits: 2,421
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
Post #1438676
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse