StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>My answer appears to have not appeared...</p> <p>I'll try again...</p> <p>Rob Farley's answer to the original question has the handy benefit of including the number of consecutive days.</p> <pre><code>with numberedrows as ( select row_number() over (partition by UserID order by CreationDate) - cast(CreationDate-0.5 as int) as TheOffset, CreationDate, UserID from tablename ) select min(CreationDate), max(CreationDate), count(*) as NumConsecutiveDays, UserID from numberedrows group by UserID, TheOffset </code></pre> <p>Using integer division, simply dividing the consecutive number of days gives the number of "distinct (n)-day periods" covered by the whole consecutive period...<br> - 2 / 4 = 0<br> - 4 / 4 = 1<br> - 8 / 4 = 2<br> - 9 / 4 = 2<br> - etc, etc </p> <p>So here is my take on Rob's answer for your needs...<br> (I really LOVE <a href="https://stackoverflow.com/questions/1176011/sql-to-determine-minimum-sequential-days-of-access/1176255#1176255">Rob's answer</a>, go read the explanation, it's inspired thinking!)</p> <pre><code>with numberedrows ( UserID, TheOffset ) as ( select UserID, row_number() over (partition by UserID order by CreationDate) - DATEDIFF(DAY, 0, CreationDate) as TheOffset from tablename ), ConsecutiveCounts( UserID, ConsecutiveDays ) as ( select UserID, count(*) as ConsecutiveDays from numberedrows group by UserID, TheOffset ) select UserID, SUM(ConsecutiveDays / @period_length) AS distinct_n_day_periods from ConsecutiveCounts group by UserID </code></pre> <p>The only real difference is that I take Rob's results and then run it through another GROUP BY...</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload