Hi all. This question was originally posted to StackOverflow but thought I would post here as the knowledge is much more concentrated :) I have a dataset within SQL Server 2012 that needs to work on the following principle:
So I have the following data:
Based on the above rules, they could be split out into the following "groups"
Which could then end up as
How would this be achieved within a SQL query, if at all? The comment http://stackoverflow.com/a/40385655/1283391 is halfway there. I don't think I need a SUM, more a running total.
(comments are locked)

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
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 0180 gets 0 anything 181360 gets 1, and on and on. I put all this in to a second cte feeding off the first:
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
and it will give you the number of seats per user. The query end result is for the provided result set is:
As I said, it can definitely be refined in to something much neater, but should provide a solid start. Full query below:
(comments are locked)

Just for clarification  what is 6 months? is 1st Sept 6 months after 2nd March? Datetdiff(month....) would say so, but I don't think that's what you want?
When does the 6 months start? In your example you say "6 months from the date of the first result", but then in the results say "created 6+ months after previous row"  so for example where would a row
go? It's clearly more than 6 months since the start date of group 2 (1st Sept 2015), but not more than 6 months than the previous row (1st Dec 2015)