Блог

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

Вот пара тестовых записей, с которыми я работаю

Я настроил следующий запрос для компиляции CTE date_month для компиляции полных 12 месяцев, полученных из моих переменных начального / конечного диапазона. Затем я присоединил его к своей тестовой таблице, чтобы установить места размещения счетчиков.

 DECLARE @EnrollmentDateStart DATETIME  = '2020-01-01'
DECLARE @EnrollmentDateEnd DATETIME  = '2020-12-01'

;WITH CTE_Months(year_month) AS 
(
  SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @EnrollmentDateStart), 0))
  FROM ( SELECT TOP (DATEDIFF(MONTH, @EnrollmentDateStart, @EnrollmentDateEnd)   1) 
    n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
    FROM sys.all_objects ORDER BY [object_id] ) AS n
)
SELECT
  [Year]     = YEAR(cm.year_month),
  [Month]    = DATENAME(MONTH, cm.year_month), 
  SUM(IIF(tt.[Enrollment Start Date] >= @EnrollmentDateStart,1,0)) AS EnrollmentCount
FROM CTE_Months cm
    LEFT OUTER JOIN #TMP_Testing_Table tt
    ON tt.[Enrollment Start Date] >= cm.year_month
    AND tt.[Enrollment Start Date] < DATEADD(MONTH, 1, cm.year_month)
GROUP BY tt.Department, cm.year_month
  

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

Теперь я пытаюсь выяснить, каков наилучший курс действий для размещения последующего подсчета за дополнительные месяцы, предшествующие дате завершения?

Например, первый пользователь (userId: 1) был зарегистрирован в марте 2020 года и завершен в августе 2020 года, поэтому, по сути, я хочу получить следующий результат, чтобы отразить количество месяцев в диапазоне от марта <> июля (последний месяц до завершения)

Январь: 0 Февраль: 0 Март: 1 Апрель: 1 Май: 1 Июнь: 1 Июль: 1 Август: 0 Сентябрь: 0 Октябрь: 0 Ноябрь: 0 Декабрь: 0

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

Посмотрим, смогу ли я получить ваши мысли / предложения о том, как решить этот сценарий? Прошу прощения, если информация / объяснение сбивают с толку, но, пожалуйста, дайте мне знать, и я сделаю все возможное, чтобы уточнить.

 ....................    
SELECT
  [Year]     = YEAR(cm.year_month),
  [Month]    = DATENAME(MONTH, cm.year_month), 
  count(tt.userid) AS EnrollmentCount
FROM CTE_Months cm
    LEFT OUTER JOIN #TMP_Testing_Table tt on cm.year_month > eomonth([Enrollment Start Date], -1) 
           and cm.year_month <= tt.[Enrollment End Date]
GROUP BY cm.year_month