Блог

Главная - Вопросы по программированию - SQL, чтобы получить строку для начальной и конечной даты для каждого года, учитывая начальную дату и количество лет

SQL, чтобы получить строку для начальной и конечной даты для каждого года, учитывая начальную дату и количество лет

#sql #sql-server

#sql #sql-server

Вопрос:

У меня есть следующие данные в таблице SQL:

  ------------------------------------ 
| ID          YEARS       START_DATE |
 ------------------------------------ 
| ----------- ----------- ---------- |
| 1           5           2020-12-01 |
| 2           8           2020-12-01 |
 ------------------------------------

Пытаюсь создать SQL, который расширил бы вышеуказанные данные и дал мне начальную и конечную дату для каждого года в зависимости от YEARS и START_DATE из приведенной выше таблицы. Пример вывода ниже:

  ----------------------------------------------- 
|  ID          YEAR        DATE_START DATE_END  |
 ----------------------------------------------- 
| ----------- ----------- ---------- ---------- |
| 1           1           2020-12-01 2021-11-30 |
| 1           2           2021-12-01 2022-11-30 |
| 1           3           2022-12-01 2023-11-30 |
| 1           4           2023-12-01 2024-11-30 |
| 1           5           2024-12-01 2025-11-30 |
| 2           1           2020-12-01 2021-11-30 |
| 2           2           2021-12-01 2022-11-30 |
| 2           3           2022-12-01 2023-11-30 |
| 2           4           2023-12-01 2024-11-30 |
| 2           5           2024-12-01 2025-11-30 |
| 2           6           2025-12-01 2026-11-30 |
| 2           7           2026-12-01 2027-11-30 |
| 2           8           2027-12-01 2028-11-30 |
 -----------------------------------------------

Комментарии:

1. Будьте осторожны с тем, что вы предполагаете, и как это предположение переходит в предложения. Может ли дата начала быть днем, который не является первым в месяце? И прежде чем вы скажете «НЕТ» — это принудительно с ограничением, чтобы предотвратить это? Если нет, то у вас очень коварная ошибка, ожидающая обнаружения.

Ответ №1:

Я бы использовал для этого встроенный подсчет, поскольку они намного быстрее, чем рекурсивное решение CTE. Предполагая, что у вас низкие значения для Years :

 WITH YourTable AS(
    SELECT *
    FROM (VALUES(1,5,CONVERT(date,'20201201')),
                (2,8,CONVERT(date,'20201201')))V(ID,Years, StartDate))
SELECT ID,
       V.I   1 AS [Year],
       DATEADD(YEAR, V.I, YT.StartDate) AS StartDate,
       DATEADD(DAY, -1, DATEADD(YEAR, V.I 1, YT.StartDate)) AS EndDate
FROM YourTable YT
     JOIN (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))V(I) ON YT.Years > V.I;

Если у вас есть более 10 ~ лет, вы можете использовать либо создать таблицу подсчета, либо создать большую встроенную в CTE. Это начнется как:

 WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I --remove the -1 if you don't want to start from 0
    FROM N N1, N N2) --100 rows, add more Ns for more rows
...

Конечно, я сомневаюсь, что у вас есть данные за 1000 лет.

Ответ №2:

Вы можете использовать рекурсивный CTE:

 with cte as (
      select id, 1 as year, start_date,
             dateadd(day, -1, dateadd(year, 1, start_date)) as end_date,
             years as num_years
      from t
      union all
      select id, year   1, dateadd(year, 1, start_date),
             dateadd(day, -1, dateadd(year, 1, start_date)) as end_date,
             num_years             
      from cte
      where year < num_years
     )
select id, year, start_date, end_date
from cte;

Здесь находится db<>fiddle.

Ответ №3:

В запросе вы можете использовать следующее:

 DATEADD(YEAR, 1, DATE_START) - 1

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

 UPDATE MyTable
SET DATE_END = DATEADD(YEAR, 1, DATE_START) - 1

Ответ №4:

Если вы работаете с sql Server, то вы можете попробовать использовать operator CROSS APPLY с таблицей master.dbo.spt_values, чтобы получить список чисел и сгенерировать даты:

  select ID,T.number 1 as YEAR, 
 --generate date_start using T.number
 dateadd(year,T.number,START_DATE)date_start,
 --generate end_date: adding 1 year to start date  
 dateadd(dd,-1,dateadd(year,1,dateadd(year,T.number,START_DATE)))date_end 
 from Table
 cross apply
 master.dbo.spt_values T
 where T.type='P' and T.number<YEARS