Yearly Retention data question

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 2012 Forums

Transact-SQL (2012)

Yearly Retention data question

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2014-11-27 : 11:29:55

Hello everyone, Not been on here for a while. Been able to sort myself out with quite a few bits. Hope your all doing well.

I have the below dataset as an example.

customerReferenceNumber RaceDay Game_Year Retention
10004952 Day 3 2007 New
10004952 Day 3 2008 Retained
10004952 Day 3 2010 Returned
10004952 Day 3 2011 Retained
10004952 Day 5 2007 New
10004952 Day 5 2008 Retained
10004952 Day 5 2010 Returned
10004952 Day 5 2011 Retained

The Retention column is what I am trying to work out with sql. The problem I am having is working out how to look for missing years where you would see a Retention value of Returned.

In the above example. I am trying to achieve the Returned value, as you can see, the year 2009 is missing. Otherwise 2009 would be Retained.

could someone help me out please.

Regards

Rob

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2014-12-04 : 10:34:36

declare @tbl as table
(
customerReferenceNumber varchar(20),
RaceDay varchar(10),
Game_Year int,
Retentions varchar(10)
)

insert into @tbl
select 10004952, 'Day 3', 2007, 'New' union
select 10004952, 'Day 3', 2008, 'Retained' union
select 10004952, 'Day 3', 2010, 'Returned' union
select 10004952, 'Day 3', 2011, 'Retained' union
select 10004952, 'Day 5', 2007, 'New' union
select 10004952, 'Day 5', 2008, 'Retained' union
select 10004952, 'Day 5', 2010, 'Returned' union
select 10004952, 'Day 5', 2011, 'Retained'

;with maxtbl
as
( select MAX(game_Year) as maxgameyear, customerReferenceNumber, RaceDay
from @tbl
group by customerReferenceNumber, RaceDay
),
maxtbl1
as
( select top 1 game_Year, customerReferenceNumber, RaceDay
from @tbl
order by customerReferenceNumber, RaceDay, game_Year
union all
select a.Game_Year+1, a.customerReferenceNumber, a.RaceDay
from maxtbl1 a
inner join maxtbl b
on a.customerReferenceNumber = b.customerReferenceNumber
and a.RaceDay = b.RaceDay
where a.Game_Year <= b.maxgameyear
)

select a.*
from maxtbl1 a
left outer join @tbl b
on a.customerReferenceNumber = b.customerReferenceNumber
and a.RaceDay = b.RaceDay
and a.Game_Year = b.Game_Year
where b.Game_Year is null

Regards
Viggneshwar A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources