Split dataset into group based on date+count [MSSQL 2012]

OK, so I'm assuming that the date calculations for every six months are based on each user, so the earliest date per user is where the calculation for the 6 months begins. I've knocked a query together that can definitely be refined down but should be a good starting block.

after putting the data above into a table called UserGroup I put a row_number() against the initial data in a cte table

 with initialrows as
 (select 
 UserName
 ,DateCreated
 ,row_number() over(partition by UserName order by DateCreated) RowNum
 from
 UserGroup)

This allows you to join the data set to itself using the row number minus 1 to join the next date to the previous date and perform a datediff calculation between that rows date and the next. If you couple this with a cumulative sum, partitioned by user and order by date, you can then get a span of time across the rows between the date created rows. I then divided the sum of the cumulative datediff sum and divided by 180 (roughly six months in days.....ish) and wrapped this in a FLOOR. This then allocates the 'seat' number to the users, anything 0-180 gets 0 anything 181-360 gets 1, and on and on. I put all this in to a second cte feeding off the first:

 , t2 as(
 select 
 fr.UserName
 ,fr.DateCreated
 ,isnull(datediff(dd,sr.datecreated,fr.datecreated),0) DateDiffOne
 ,sum(isnull(datediff(dd,sr.datecreated,fr.datecreated),0)) over(partition by fr.username order by fr.username,fr.datecreated rows unbounded preceding) DateDiffCum
 ,floor(sum(isnull(datediff(dd,sr.datecreated,fr.datecreated),0)) over(partition by fr.username order by fr.username,fr.datecreated rows unbounded preceding) / 180) DateDiffCumFloor
 from
 initialrows fr
 left join initialrows sr on fr.username = sr.username and fr.RowNum -1 = sr.RowNum 
 )

The datediffone and datediffcum calculations above are just to show the stages worked through to get to the datediffcumfloor calculation. You can then just do a count distinct on the DateDiffCumFloor grouped by username

 select
 t2.Username
 ,count(distinct datediffcumfloor) Seat
 from
 t2
 group by
 t2.username

and it will give you the number of seats per user. The query end result is for the provided result set is:

 Username Seat
 User1        3
 User2        2
 User3        1

As I said, it can definitely be refined in to something much neater, but should provide a solid start. Full query below:

 with initialrows as
 (select 
 UserName
 ,DateCreated
 ,row_number() over(partition by UserName order by DateCreated) RowNum
 from
 UserGroup)
 , t2 as(
 select 
 fr.UserName
 ,fr.DateCreated
 ,isnull(datediff(dd,sr.datecreated,fr.datecreated),0) DateDiffOne
 ,sum(isnull(datediff(dd,sr.datecreated,fr.datecreated),0)) over(partition by fr.username order by fr.username,fr.datecreated rows unbounded preceding) DateDiffCum
 ,floor(sum(isnull(datediff(dd,sr.datecreated,fr.datecreated),0)) over(partition by fr.username order by fr.username,fr.datecreated rows unbounded preceding) / 180) DateDiffCumFloor
 from
 initialrows fr
 left join initialrows sr on fr.username = sr.username and fr.RowNum -1 = sr.RowNum 
 )
 select
 t2.Username
 ,count(distinct datediffcumfloor) Seat
 from
 t2
 group by
 t2.username