Self Join of Some Sort - SQL Server Forums

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

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]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources