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.
asked Jan 18 at 04:00 PM in Default
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 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:
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: