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 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:
(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)