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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Self Join of Some Sort

Author  Topic 

Jadanza
Starting Member

2 Posts

Posted - 2014-09-11 : 14:05:37
Consider the following table:

[Code]
ClientID VisitNo Renewal
-------------------------------------------
1 1 1
1 2 0
1 3 0
1 4 0
1 5 1
1 6 0
2 1 1
2 2 0
2 3 0
2 4 0


Looking for the following output:

ClientID VisitNo Renewal LastRenewal
------------------------------------------------------
1 1 1 1
1 2 0 1
1 3 0 1
1 4 0 1
1 5 1 5
1 6 0 5
2 1 1 1
2 2 0 1
2 3 0 1
2 4 0 1
[/code]
Need to know the visit number of the last time there was a renewal.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-11 : 15:40:27
Perhaps this? I haven't tested it. If this does not work, post some data in a consumable format (i.e., something that can be copied and pasted to a SSMS query window to create a test table to generate your sample table)
SELECT
a.*,
b.VisitNo AS LastRenewal
FROM
YourTable a
CROSS APPLY
(
SELECT TOP (1) VisitNo
FROM YourTable b
WHERE b.ClientId = a.ClientId
AND b.Renewal=1
AND b.VisitNo <= a.VisitNo
ORDER BY b.VisitNo DESC
) b
Go to Top of Page

Jadanza
Starting Member

2 Posts

Posted - 2014-09-11 : 16:44:47
Thanks James... For anyone else here is the complete code with sample data to play with.

[Code]
declare @Test table
(
clientid int,
visitno int,
renewal int
)

insert @Test values(1,1,1)
insert @Test values(1,2,0)
insert @Test values(1,3,0)
insert @Test values(1,4,0)
insert @Test values(1,5,1)
insert @Test values(1,6,0)
insert @Test values(2,1,1)
insert @Test values(2,2,0)
insert @Test values(2,3,1)
insert @Test values(2,4,0)

SELECT
a.*,
b.VisitNo AS LastRenewal
FROM
@Test a
CROSS APPLY
(
SELECT TOP (1) VisitNo
FROM @Test b
WHERE b.ClientId = a.ClientId
AND b.Renewal=1
AND b.VisitNo <= a.VisitNo
ORDER BY b.VisitNo DESC
) b

[/Code]
Go to Top of Page
   

- Advertisement -