StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<pre><code>declare @baseDate datetime set @baseDate = '1 May 2005' SELECT datediff(year, @baseDate, [date]) AS YearBucket ,COUNT(*) AS cnt FROM logins GROUP BY datediff(year, @baseDate, [date]) ORDER BY datediff(year, @baseDate, [date]) </code></pre> <p>EDIT - apologies, you are correct. Here is a fixed version (I should have used a test table to start with...)</p> <pre><code>create table logins (date datetime, foo int) insert logins values ('1 may 2005', 1) insert logins values ('1 apr 2006', 2) insert logins values ('1 may 2006', 3) declare @baseDate datetime set @baseDate = '1 May 2005' SELECT datediff(day, @baseDate, [date]) / 365 AS YearBucket ,COUNT(*) AS cnt FROM logins GROUP BY datediff(day, @baseDate, [date]) / 365 ORDER BY datediff(day, @baseDate, [date]) / 365 </code></pre> <p>Change the datediff units if you want more granularity than days.</p> <p>EDIT #2 - ok, here is a more robust solution that handles leap years :) EDIT #3 - Actually this doesn't handle leap years, instead it allows for variable intervals of time to be specified. Go with dateadd(year, 1, @baseDate) for the leap year safe approach.</p> <pre><code>declare @baseDate datetime, @interval datetime --@interval is expressed as time above 0 time (1/1/1900) select @baseDate = '1 May 2005', @interval = '1901' declare @timeRanges table (beginIntervalInclusive datetime, endIntervalExclusive datetime) declare @i int set @i = 1 while @i <= 10 begin insert @timeRanges values(@baseDate, @baseDate + @interval) set @baseDate = @baseDate + @interval set @i = @i + 1 end SELECT tr.beginIntervalInclusive, tr.endIntervalExclusive, COUNT(*) AS cnt FROM logins join @timeRanges as tr on logins.date >= tr.beginIntervalInclusive and logins.date < tr.endIntervalExclusive GROUP BY tr.beginIntervalInclusive, tr.endIntervalExclusive ORDER BY tr.beginIntervalInclusive </code></pre>

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