Как писать запросы с колонкой по временным интервалам (неделя, месяц, квартал, год)
Задача - вывести таблицу некоторых данных по неделям, месяцам и т.д.
Раньше мы решали эту задачу через табличную переменную и insert в нее строк. Это громоздкое решение.
Ниже код функции, dbo.as_periods, которая выдает таблицу интервалов.
На входе:
- type nvarchar(32) - какие интервалы смотрим? day, week, month, quarter, year
- cnt int - сколько нужно назад интервалов пройти?
- format nvarchar(32) - в каком формате выводить интервал? Можно ставить пустую строку.
- langCode nvarchar(32) - код языка (передается в функцию FORMAT). Если пустая строка, то будет русский язык (ru-ru).
На выходе таблица с полями:
- num int - номер
- date1 date - дата начала периода
- date2 date - дата окончания периода
- name nvarchar(128) - название периода
Как использовать:
set datefirst 1 -- это используется для недель. Чтобы первым шел понедельник.
select * from [dbo].[as_periods] ('day', 4, '', '')
select * from [dbo].[as_periods] ('week', 4, '', '')
select * from [dbo].[as_periods] ('month', 4, '', '')
select * from [dbo].[as_periods] ('quarter', 4, '', '')
select * from [dbo].[as_periods] ('year', 4, '', '')
Результат выполнения:
Нюансы:
- для кварталов используется псевдопеременная вида qq (она заменяется на номер квартала),
- для недель используется псевдопеременная вида ww (она заменяется на номер недели).
Как можно использовать:
select num, name,
(select count(*) from as_trace e where e.created > p.date1 and e.created < p.date2)
from [dbo].[as_periods] ('week', 4, '', '') p
Код самой функции:
CREATE OR ALTER FUNCTION [dbo].[as_periods]
(
@type varchar(128),
@cnt int,
@format nvarchar(128) = '',
@langCode nvarchar(10) = ''
)
RETURNS
@res TABLE (num int, date1 date, date2 date, name nvarchar(128))
AS
BEGIN
if(@langCode='') set @langCode = 'ru-ru'
declare @startDate date = getdate()
declare @wwekStart date = DATEADD(dd, -(DATEPART(dw, @startDate)-1), @startDate)
declare @monthStart date = DATEADD(month, DATEDIFF(month, 0,@startDate), 0)
declare @quarterStart date = DATEADD(quarter, DATEDIFF(quarter, 0,@startDate), 0)
declare @yearStart date = DATEADD(year, DATEDIFF(year, 0,@startDate), 0)
--set datefirst 1
;WITH nums(num) AS
(
SELECT 1
UNION ALL
SELECT num+1
FROM nums
WHERE num < @cnt
)
insert into @res
SELECT num,
case @type
when 'day' then dateadd(day, -num+1, @startDate)
when 'week' then dateadd(week, -num+1, @wwekStart)
when 'month' then dateadd(month, -num+1, @monthStart)
when 'quarter' then dateadd(quarter, -num+1, @quarterStart)
when 'year' then dateadd(year, -num+1, @yearStart)
else getdate()
end date1,
case @type
when 'day' then dateadd(day, -num+2, @startDate)
when 'week' then dateadd(week, -num+2, @wwekStart)
when 'month' then dateadd(month, -num+2, @monthStart)
when 'quarter' then dateadd(quarter, -num+2, @quarterStart)
when 'year' then dateadd(year, -num+2, @yearStart)
else getdate()
end date2,
case @type
when 'day' then format(dateadd(day, -num+1, @startDate), iif(@format<>'', @format, 'dd.MM.yyyy'), @langCode)
when 'week' then replace(
format(dateadd(week, -num+1, @wwekStart), iif(@format<>'', @format,'ww неделя yyyy'), @langCode),
'ww', datepart(week, dateadd(week, -num+1, @wwekStart)) )
when 'month' then format(dateadd(month, -num+1, @monthStart), iif(@format<>'', @format,'MMMM yyyy'), @langCode)
when 'quarter' then replace(
format(dateadd(quarter, -num+1, @quarterStart), iif(@format<>'', @format,'qq квартал yyyy'), @langCode),
'qq', datepart(quarter, dateadd(quarter, -num+1, @quarterStart)) )
when 'year' then format(dateadd(year, -num+1, @yearStart), iif(@format<>'', @format,'yyyy'), @langCode)
else ''
end name
FROM nums
RETURN
END
Что еще посмотреть по SQL Server
Скрипт бекапа базы и восстановления из бекапа
SQL запрос для получения всех текущих выполняющихся запросов на базе
Запрос для поиска проблемных SQL по процессору
Как сделать поиск по всем таблицам базы данных SQL Server
Поиск строки во всех хранимых процедурах SQL Server
Ищем партнеров-разработчиков на T-SQL
Прямая работа с заказчиками как ИП или самозанятый. Нужно знать только SQL и HTML
Выгода от использования Falcon Space
В 2-3 раза экономнее и быстрее, чем заказная разработка
Более гибкая, чем коробочные решения и облачные сервисы
Используйте готовые решения и изменяйте под свои потребности
Нужна бесплатная консультация?
Планируете делать веб-проект?
- Шаг 1. Создать концепт проекта
- Шаг 2. Получить оценку бюджета (КП)
- Шаг 3. Заключить договор
- Шаг 4. Создать совместно техническое задание
- Шаг 5. Поэтапная реализация проекта