SQL Server: group_by select active dates -

in table have 2 columns:

• leasecontract.[contract activation date]
• leasecontract.[contract ending date]

with 2 columns, i'd generate table example this:

so query first row (new contracts):

select     count(leasecontract.id) total,    year(leasecontract.[contract activation date]) jaar       leasecontract group     year(leasecontract.[contract activation date]) 

but there way same middle row? show active contracts grouped year in 1 query. thanks!

sql version 2008

never did recursive cte before. place learn. so, 1 below should work. in case there's contract no end date, show "active" on listed years following start date. that's assuming sql server version 2008 or above, think.

the recursion may not necessary, in theory getting distinct years start , end dates, possible there leap years no new contracts opened, or old ones closed, while still active. way, leap years included, plus min/max surpass distinct / group dates performance concerned.

;with datelimits      -- fetching absolute min , max date         (select mindate, case when maxdate < maxdatecheck maxdatecheck else maxdate end maxdate                     (select min([contract activation date]) mindate                 , max([contract ending date]) maxdate                 , max([contract activation date]) maxdatecheck             leasecontract) src)     -- creating years between min start , max endtime     , cteseq         (select year(mindate) [year]         datelimits         union         select [year]+1         cteseq          join datelimits on 1=1         [year] < year(maxdate)) -- forming results based on cteseq above select * cteseq cte outer apply (select count(*) [new contracts] leasecontract year([contract activation date]) = cte.year) oastarts outer apply (select count(*) [active contracts] leasecontract      cte.year between year([contract activation date]) , isnull(year([contract ending date]),cte.year)) oaactive outer apply (select count(*) [closed contracts] leasecontract year([contract ending date]) = cte.year) oaends order [year] asc option (maxrecursion 100); 

edit: added check in case max endtime smaller max starttime.

edit 2: holy, neph's multiple cte queries example superb. didn't know can that. changed response single query example, replacing previous variables additional cte select instead.