Нестандартная гистограмма на основе даты SQL Server

У меня есть данные входа пользователя с отметками времени, и мне хотелось бы получить гистограмму логинов по годам, но с годом, начинающимся с произвольной даты. Например, я хочу получить следующую информацию:

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 

Ярлыки в первом столбце не важны, но диапазоны дат. Я знаю, что могу разорвать его с помощью проливных лет:

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

Но это не дает мне диапазоны данных, которые я хочу. Как это может быть сделано?

     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 – извинения, вы правы. Вот фиксированная версия (я должен был использовать тестовую таблицу, чтобы начать с …)

     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 

    Измените единицы датирования, если вы хотите увеличить степень детализации, чем дни.

    EDIT # 2 – ОК, вот более надежное решение, которое обрабатывает високосные годы 🙂 EDIT # 3 – На самом деле это не обрабатывает високосные годы, вместо этого он позволяет задавать переменные интервалы времени. Пойдите с dateadd (год, 1, @baseDate) для безопасного подхода в режиме високосного года.

     declare @baseDate datetime, @interval datetime [email protected] 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 

    Если вы можете найти способ определения диапазонов дат в отдельной таблице, выберите ярлык и два столбца дат и присоединитесь к этому из основного запроса примерно так, в зависимости от ваших таблиц.

     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 
    Interesting Posts

    Поддерживает ли SQL Server физическую сборку полного набора результатов?

    Запланированное задание SQL Server – говорит, что он работает без ошибок, но ничего не делает

    ASP.Net SessionState с использованием SQL Server – это зашифрованные данные?

    Выберите каждую n-ю строку в SQL

    Вопрос SqlServer Rand ()

    Получите только строки, которые расположены ближе к столбцу Y в столбце X

    Поиск отсутствующих данных или Сравнение целых строк

    SQL-запрос STUFF в NHibernate, в основном пытается реплицировать MySQLGroupConcat

    Group By Count и поле в одном запросе

    Выберите информацию пользователей с их общим количеством комментариев.

    Содержит () функция falters со строками чисел?

    Как я могу поддерживать общее количество в базе данных SQL Server с помощью VB.NET?

    Эффективность SQL-сервера Datetime vs Int

    Сценарий письменной передачи данных DataBase и копирование файлов

    SQL Server Management Studio: отображение даты / времени в строке состояния

    Давайте будем гением компьютера.