SQL Server - получить все номера месяцев и лет до текущего месяца с месяца и года присоединения

#sql-server #common-table-expression

Вопрос:

У меня есть дата присоединения сотрудника, с даты присоединения до даты, когда я хотел бы напечатать номера месяцев и лет.

Например, сотрудник присоединился в июле 2020 года, мне нужно получить данные, как показано ниже

 MonthNumber YearNumber
    7          2020
    8          2020
    9          2020
    10         2020
    11         2020
    12         2020
     1         2021

Ниже приведен мой запрос, я использую CTE и пытаюсь увеличить его..

 DECLARE @JoiningDate Date

SET @JoiningDate = '2020-07-04 11:21:03.827'

;With MonthYears as (
SELECT monthNumber = DATEPART(m, @JoiningDate),
       yearNumber = DATEPART(YEAR, DATEADD(m, i 1, @JoiningDate),
       i = 0

UNION ALL

SELECT monthNumber = DATEPART(m, DATEADD(m, i 1, @JoiningDate)),
       yearNumber = DATEPART(YEAR, DATEADD(m, i 1, @JoiningDate)),
       i = i 1
FROM MonthYears
WHERE DATEPART(m, DATEADD(m, i 1, @JoiningDate)) <= DATEPART(m, GETDATE())
AND DATEPART(year, DATEADD(m, i 1, @JoiningDate)) <= DATEPART(year, GETDATE())
)
SELECT * FROM MonthYears

Но я мог видеть только 1 запись, то есть месяц и год присоединения, которые равны 7, 2020

1. Возможно, подсказка: вам не нужна арифметика даты для решения вашей проблемы. Вы редко найдете год, в котором больше или меньше 12 месяцев.

2.Ваше WHERE условие неверно DATEPART(m, DATEADD(m, i 1, @JoiningDate)) <= DATEPART(m, GETDATE()) DATEPART(m, GETDATE()) 1 , поэтому вернется следующая итерация false , и вы получите только 1 строку обратно.

3. Рекурсивный CTE намного медленнее, чем таблица подсчета или таблица календаря, чтобы сделать это

Ответ №1:

Проблема с вашим запросом заключается в WHERE условии

 WHERE DATEPART(m, DATEADD(m, i 1, @JoiningDate)) <= DATEPART(m, GETDATE())
AND DATEPART(year, DATEADD(m, i 1, @JoiningDate)) <= DATEPART(year, GETDATE())

На второй итерации Aug - 8 не менее DATEPART(m, GETDATE()) = 1

Я бы использовал первую дату месяца и увеличил на 1 месяц в рекурсивном CTE. Затем используйте DATEPART() результат

 DECLARE @JoiningDate Date

SET @JoiningDate = '2020-07-04 11:21:03.827'

;With MonthYears as 
(
    SELECT [FirstOfMonth]   = DATEADD(MONTH, DATEDIFF(MONTH, 0, @JoiningDate), 0)

    UNION ALL

    SELECT  [FirstOfMonth]  = DATEADD(MONTH, 1, [FirstOfMonth])
    FROM    MonthYears
    WHERE   [FirstOfMonth]  < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
)
SELECT  *, 
        monthNumber = DATEPART(month, [FirstOfMonth]),
        yearNumber  = DATEPART(YEAR, [FirstOfMonth])
FROM    MonthYears

Ответ №2:

Вы усложняете требование.
Все, что вам нужно, это даты, которые являются 1-м днем каждого месяца, и вы можете получить их, начав с 1-го числа месяца @JoiningDate и добавив 1 месяц рекурсивно:

 DECLARE @JoiningDate Date;
SET @JoiningDate = '2020-07-04 11:21:03.827';

WITH cte as (
  SELECT DATEFROMPARTS(YEAR(@JoiningDate), MONTH(@JoiningDate), 1) date
  UNION ALL
  SELECT DATEADD(m, 1, date)
  FROM cte
  WHERE DATEADD(m, 1, date) <= GETDATE()
)
SELECT MONTH(date) MonthNumber, 
       YEAR(date) YearNumber 
FROM cte
OPTION (MAXRECURSION 0) -- you may need this because there may exist employees with more than 100 months of employement

Смотрите демонстрацию.
Результаты:

 > MonthNumber | YearNumber
> ----------: | ---------:
>           7 |       2020
>           8 |       2020
>           9 |       2020
>          10 |       2020
>          11 |       2020
>          12 |       2020
>           1 |       2021