Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to write custom query which shows last two records added only.


How to write custom query which shows last two records added only.

Author
Message
naisu1383
naisu1383
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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.......
Kingston Dhasian
Kingston Dhasian
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2999 Visits: 4986
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/
Ed B
Ed B
Mr or Mrs. 500
Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)

Group: General Forum Members
Points: 594 Visits: 1775
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


darrylc-ssc
darrylc-ssc
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2315 Visits: 262
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


naisu1383
naisu1383
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 33
Thnx @darrylc-ssc for your solution. It did worked....
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 3229
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search