Custom SQL Server Date Bar Graph

Question

I have user login data with timestamps, and I would like to get a histogram of logins by year, but with a year starting from an arbitrary date. For example, I want to get the following information:

1 May 2005 - 30 Apr 2006 | 525
1 May 2006 - 30 Apr 2007 | 673
1 May 2007 - 30 Apr 2008 | 892
1 May 2006 - 30 Apr 2009 | 1047

Labels in the first column are not important, but date ranges. I know that I can break it for many years:

SELECT YEAR([date]) AS [year], COUNT(*) AS cnt 
FROM logins
GROUP BY YEAR([date])
ORDER BY [year]

But that does not give me the ranges of data that I want. How can I do that?

Answer 1

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])

EDIT - Sorry, you're right. Here is the fixed version (I had to use a test pattern to start ...)

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

Change obsolete units if you want to increase the level of detail than days.

EDIT # 2 - , , :) EDIT # 3 - , . dateadd (, 1, @baseDate) .

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

Answer 2

, , .

Select Count(*) as NoLogons, DateRangeLabel
From logins a
inner join
(
Select
DateRangeLabel, StartDate, EndDate
From tblMyDates 
) b
on a.date between b.startdate and b.enddate
Group by DateRangeLabel