Блог

У меня есть таблица с типами int, datetime, datetime:

 id    start date    end date
--    ----------    ----------
1     2019-04-02    2020-09-17
2     2019-08-10    2020-08-10
  

Вот создать / вставить:

 CREATE TABLE dbo.something
(
  id           int, 
  [start date] datetime, 
  [end date]   datetime
);

INSERT dbo.something(id,[start date],[end date]) 
  VALUES(1,'20190402','20200917'),(2,'20190810','20200810');
  

Что такое SQL-запрос, который может выдавать эти результаты:

 id    Year    Quarter
--    ----    ----------
1     2019    2
1     2019    3
1     2019    4
1     2020    1
1     2020    2
1     2020    3
2     2019    3
2     2019    4
2     2020    1
2     2020    2
2     2020    3
  

Просто используйте рекурсивный CTE. Эта версия переключается на подсчет кварталов с 0 года:

 with cte as (
      select id, 
             year(start_date) * 4    datepart(quarter, start_date) - 1 as yyyyq,
             year(end_date) * 4   datepart(quarter, end_date) - 1 as end_yyyyq
      from t
      union all
      select id, yyyyq   1, end_yyyyq
      from cte
      where yyyyq < end_yyyyq
     )
select id, yyyyq / 4 as year, (yyyyq % 4)   1 as quarter
from cte;
  

Вот скрипка db<> .

Если вы не можете создать другую справочную таблицу / etc, вы можете использовать DATEDIFF (и DATEPART) с использованием quarters, а затем выполнить простую арифметику даты.

Приведенная ниже логика заключается в том, чтобы просто найти для каждой начальной даты первый квартал, а затем количество дополнительных кварталов, чтобы достичь максимума. Затем выберите, где дополнительные кварталы добавляются к начальной дате, чтобы получить каждый квартал.

Самая сложная часть запроса для понимания imo — это WITH numberlist раздел — все, что он делает, это генерирует серию целых чисел от 0 до максимального количества кварталов разницы. Если у вас уже есть таблица numbers, вы можете использовать ее вместо этого.

Часть ключевого кода приведена ниже, а вот полный DB_Fiddle с некоторыми дополнительными тестовыми данными.

 CREATE TABLE #yourtable (id int, startdate date, enddate date)
INSERT INTO #yourtable (id, startdate, enddate) VALUES
(1, '2019-04-02', '2020-09-17'),
(2, '2019-08-10', '2020-08-20')

; WITH number_list AS
        -- list of ints from 0 to maximum number of quarters
        (SELECT n 
            FROM (SELECT ones.n   10*tens.n AS n
                    FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
                         (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n)
                ) AS a
            WHERE n <= (SELECT MAX(DATEDIFF(quarter,startdate,enddate)) FROM #yourtable)
        )
    SELECT  id,
            YEAR(DATEADD(quarter, number_list.n, startdate)) AS [Year],
            DATEPART(quarter, DATEADD(quarter, number_list.n, startdate)) AS [Quarter]
    FROM    (SELECT id, startdate, DATEDIFF(quarter,startdate,enddate) AS num_additional_quarters FROM #yourtable) yt
            CROSS JOIN number_list
    WHERE   number_list.n <= yt.num_additional_quarters 


DROP TABLE #yourtable
  

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

 with q_date as
(
select 1 as id, '2019-04-02' :: date as start_date, '2020-09-17' :: date as end_date
UNION ALL
select 2 as id, '2019-08-10' :: date as start_date, '2020-08-10' :: date as end_date
)


select qd.id, dd.calendar_year, dd.calendar_quarter_number 
from dim_date dd, q_date qd 
where dd.date_dmk between qd.start_date and qd.end_date
group by qd.id, dd.calendar_year, dd.calendar_quarter_number
order by qd.id, dd.calendar_year, dd.calendar_quarter_number;