Active members ultimo every year - from start- and enddate

[hvsql]

Hi
Can anyone help me with a SQL statement that finds the active members ultimo every year - from a table with start- and enddate - and returns them as shown below? Regards, Henrik

[yosiasz]

please provide sample data as DDL and DML rather than a picture

--DDL
create table #hvsql(memberid int, membername varchar(50), 
startdate datetime, enddate datetime)

--DML
insert into #hvsql
select 1, 'Darth Vader', '2014-01-01' union
select 2, 'Luke', '2015-01-01' 

[femiolan]

Try

if OBJECT_ID('tempDb..#temp') is not null drop table #temp;

create table #temp
(
MemberId smallint,
StartDate date,
EndDate date

);

insert #temp
values 
('1','1/1/2015','1/5/2017'),
('2','1/2/2014','1/1/2018');

select a.MemberId , b.x 
from #temp  a
outer apply
	(
	select top (Year(a.EndDate) - Year(a.StartDate) + 1) YEAR(a.StartDate) - 1 + ROW_NUMBER() over(order by b.object_id asc) [x]
	from sys.all_objects b
	) b
group by a.MemberId , b.x 
order by b.x, a.MemberId ;	

Thanks!

[harishgg1]

hi

I tried to do this

hope it helps

I used recursive CTE
something different
not good for performance … if large data

drop create data ...

	drop table #temp;
	go 

	create table #temp
	(
	MemberId smallint,
	StartDate date,
	EndDate date

	)
	go

	insert #temp
	values 
	('1','1/1/2015','1/5/2017'),
	('2','1/2/2014','1/1/2018');
	go

SQL ...

; WITH cte 
     AS (SELECT memberid, 
                Datepart(year, startdate) AS yr, 
                enddate 
         FROM   #temp 
         UNION ALL 
         SELECT a.memberid, 
                yr + 1, 
                a.enddate 
         FROM   cte a 
                JOIN #temp b 
                  ON a.memberid = b.memberid 
         WHERE  yr < Datepart(year, a.enddate)) 
SELECT memberid, 
       yr 
FROM   cte 
ORDER  BY 1, 
          2