Диапазоны DATEDIFF по дате создания с использованием внешней даты для подсчета возрастной истории по месяцам

ProgramBox

Диапазоны DATEDIFF по дате создания с использованием внешней даты для подсчета возрастной истории по месяцам

Post author:admin
Запись опубликована:25 октября, 2022
Post category:Вопросы по программированию

#sql #sql-server

#sql #sql-сервер

Вопрос:

Использовать DATEDIFF для определения разницы в днях между двумя датами легко, и я использовал его много раз в прошлом. Чего я, кажется, не могу понять на этот раз, так это того, когда мне нужно использовать «внешнюю» ссылку, я полагаю. Цель моего запроса состоит в том, чтобы «вернуться назад во времени» и просмотреть тикеты в моей базе данных SQL Server. Затем подсчитайте, сколько им было лет, если они все еще были открыты в этом месяце, исходя из его первого дня, а затем поместите подсчеты в такие ячейки, как «от 0 до 30 дней», «от 31 до 60 дней», «от 61 до 90 дней» и «Выше 91». У меня есть следующий тестовый набор данных здесь:

 USE [TestDB]
GO
/****** Object:  Table [dbo].[taskDB]    Script Date: 12/8/2020 9:49:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[taskDB](
    [ticket] [varchar](50) NULL,
    [created] [date] NULL,
    [closed] [date] NULL,
    [rating] [varchar](50) NULL,
    [status] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023345', CAST(N'2019-09-01' AS Date), CAST(N'2020-01-17' AS Date), N'Low', N'Resolved')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023346', CAST(N'2019-08-01' AS Date), CAST(N'2019-08-03' AS Date), N'Critical', N'Resolved')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023347', CAST(N'2019-09-01' AS Date), CAST(N'2019-09-20' AS Date), N'Critical', N'Resolved')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023348', CAST(N'2019-08-01' AS Date), CAST(N'2020-08-06' AS Date), N'Critical', N'Resolved')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023349', CAST(N'2020-08-01' AS Date), CAST(N'2020-08-05' AS Date), N'Medium', N'Resolved')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023350', CAST(N'2019-08-01' AS Date), CAST(N'2019-08-05' AS Date), N'Medium', N'Resolved')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023351', CAST(N'2019-12-22' AS Date), CAST(N'1900-01-01' AS Date), N'High', N'Open')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023352', CAST(N'2019-11-07' AS Date), CAST(N'2020-08-05' AS Date), N'Medium', N'Resolved')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023353', CAST(N'2020-08-02' AS Date), CAST(N'1900-01-01' AS Date), N'Low', N'Open')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023354', CAST(N'2019-08-02' AS Date), CAST(N'2019-08-05' AS Date), N'Medium', N'Resolved')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023356', CAST(N'2019-08-02' AS Date), CAST(N'2019-08-05' AS Date), N'Critical', N'Resolved')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023357', CAST(N'2019-08-06' AS Date), CAST(N'2020-07-05' AS Date), N'Critical', N'Resolved')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023358', CAST(N'2019-10-04' AS Date), CAST(N'1900-01-01' AS Date), N'Low', N'Open')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023359', CAST(N'2019-12-02' AS Date), CAST(N'2020-02-25' AS Date), N'High', N'Resolved')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023360', CAST(N'2019-08-05' AS Date), CAST(N'2019-08-05' AS Date), N'Medium', N'Resolved')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023361', CAST(N'2020-08-02' AS Date), CAST(N'1900-01-01' AS Date), N'High', N'Open')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023362', CAST(N'2019-09-02' AS Date), CAST(N'2019-10-06' AS Date), N'Critical', N'Resolved')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023363', CAST(N'2019-10-03' AS Date), CAST(N'2019-11-08' AS Date), N'High', N'Resolved')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023365', CAST(N'2019-10-03' AS Date), CAST(N'2019-12-08' AS Date), N'Low', N'Resolved')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023364', CAST(N'2019-11-03' AS Date), CAST(N'2019-11-05' AS Date), N'High', N'Resolved')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023366', CAST(N'2020-06-03' AS Date), CAST(N'1900-01-01' AS Date), N'High', N'Open')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023368', CAST(N'2019-08-03' AS Date), CAST(N'2019-08-05' AS Date), N'High', N'Resolved')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023367', CAST(N'2019-11-03' AS Date), CAST(N'1900-01-01' AS Date), N'Low', N'Open')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023371', CAST(N'2019-08-03' AS Date), CAST(N'2019-08-05' AS Date), N'Low', N'Resolved')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023370', CAST(N'2019-08-03' AS Date), CAST(N'2019-08-05' AS Date), N'Critical', N'Resolved')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023434', CAST(N'2020-09-03' AS Date), NULL, N'Low', N'Open')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'012312', CAST(N'2020-08-14' AS Date), NULL, N'High', N'Open')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'054312', CAST(N'2020-10-16' AS Date), NULL, N'Medium', N'Open')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'023423', CAST(N'2020-01-18' AS Date), NULL, N'High', N'Open')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'032444', CAST(N'2019-12-22' AS Date), CAST(N'2020-02-22' AS Date), N'High', N'Resolved')
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [status]) VALUES (N'043234', CAST(N'2020-02-24' AS Date), CAST(N'2020-06-07' AS Date), N'Critical', N'Resolved')
GO

Запрос, который я построил до сих пор, выглядит следующим образом:

 declare @FromDate datetime, 
            @ToDate datetime;
    
    SET @FromDate = ('2020-08-01 00:00:00.000');
    SET @ToDate = (Select max(created) From [TestDB].[dbo].[taskDB]);
  
    declare @openTicketsBM table (firstDayOfMonth datetime, firstDayNextMonth datetime, year int, month int, Rating int, ZTT int, TTS int, STN int, ANN int)
    
    Insert into @openTicketsBM(firstDayOfMonth, firstDayNextMonth, year, month)
    
    Select top  (datediff(month, @FromDate, @ToDate)   1) 
                        dateadd(month, number, @FromDate),
                  dateadd(month, number   1, @FromDate),
                 year(dateadd(month, number, @FromDate)),
                  month(dateadd(month, number, @FromDate))
                  from [master].dbo.spt_values 
                  where [type] = N'P' order by number;
    
    update R
    Set  R.ZTT = (Select COUNT(CASE WHEN DATEDIFF(day, created, R.firstDayOfMonth) > 0 AND DATEDIFF(day, created, R.firstDayOfMonth) <= 30 THEN 1 ELSE NULL END) from [TestDB].[dbo].[taskDB] where created < R.firstDayNextMonth and NOT status like 'Risk Accepted' and (closed >= R.firstDayNextMonth or closed = '' or closed is null)),
         R.TTS = (Select COUNT(CASE WHEN DATEDIFF(day, created, R.firstDayOfMonth) >= 31 AND DATEDIFF(day, created, R.firstDayOfMonth) <= 60 THEN 1 ELSE NULL END) from [TestDB].[dbo].[taskDB] where created < R.firstDayNextMonth and NOT status like 'Risk Accepted' and (closed >= R.firstDayNextMonth or closed = '' or closed is null)),
         R.STN = (Select COUNT(CASE WHEN DATEDIFF(day, created, R.firstDayOfMonth) >= 61 AND DATEDIFF(day, created, R.firstDayOfMonth) <= 90 THEN 1 ELSE NULL END) from [TestDB].[dbo].[taskDB] where created < R.firstDayNextMonth and NOT status like 'Risk Accepted' and (closed >= R.firstDayNextMonth or closed = '' or closed is null)),
         R.ANN = (Select COUNT(CASE WHEN DATEDIFF(day, created, R.firstDayOfMonth) > 90 THEN 1 ELSE NULL END) from [TestDB].[dbo].[taskDB] where created < R.firstDayNextMonth and NOT status like 'Risk Accepted' and (closed >= R.firstDayOfMonth or closed = '' or closed is null))
  
    From @openTicketsBM R
    
    select  CAST(year AS VARCHAR(50))   '/'   CAST(month AS VARCHAR(50)) AS date,
            ZTT,
            TTS,
            STN,
            ANN
          
            
    from @openTicketsBM

При выполнении этого запроса генерируется следующая ошибка:

 Msg 8124, Level 16, State 1, Line 20
Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

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

EDITT 12/18/2020 10:18am est

Чтобы прояснить мою логику, давайте вернемся назад во времени, скажем, в феврале 2020 года. Сколько билетов было открыто на 1 февраля и сколько им было лет? результаты должны выглядеть следующим образом

 date       ZTT(0-30)  TTS(31-60)  STN(61-90) ANN(91 )
2020/2        5          3           3           1

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

2. Ну, у нас определенно есть образцы данных, @GordonLinoff , и, безусловно, в лучшей форме; Операторы DDL и DML.

3. Это настолько просто, насколько я мог сделать это на основе моей текущей проблемы.

4. Да, но мы не знаем ни логики, которую вы пытаетесь использовать здесь, @mister.cake, ни ожидаемых результатов. Это то, что нам тоже нужно.

5. Хорошо, я добавил правку.

Ответ №1:

Поэтому я оставляю этот ответ исключительно на основе одной части вашего поста:

Сколько билетов было открыто на 1 февраля [2020 года] и сколько им было лет?

Просто основываясь только на этой цитате…ваша проблема звучит довольно прямолинейно.

У вас есть таблица билетов с created closed указанием дат. Итак, вашим первым шагом было бы получить список всех билетов, которые существовали на 1 февраля или до этого.

Это очень просто:

 SELECT *
FROM dbo.taskDB
WHERE created <= '2020-02-01'

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

Таким образом, это означает, что для того, чтобы билет считался открытым в определенный день, назовем его @AsOfDate так, одно из них должно быть истинным…

closed IS NULL
closed = '1900-01-01'
closed >= @AsOfDate — Если билет был закрыт в тот же день, когда он был открыт, мы будем считать его открытым в этот день. Но вы можете изменить это на > , чтобы исключить эти билеты. Это зависит от вас.

Теперь давайте объединим все это в один запрос:

 DECLARE @AsOfDate date = '2020-02-01'
SELECT *, TicketAge = DATEDIFF(DAY, created, @AsOfDate)
FROM dbo.taskDB
WHERE created <= @AsOfDate -- Only look at tickets that existed at this point in time
    AND (closed IS NULL OR closed >= @AsOfDate OR closed < created) -- Only look at tickets that were open
ORDER BY created DESC

Этот запрос не выдает те же цифры, что и цифры, которые вы ожидали увидеть в своем сообщении … но я также не уверен, что ваши цифры точны. Ваши цифры складываются в 12 билетов на 1 февраля 2020 года … но я вижу 9 билетов, которые существовали и были открыты в то время.

РЕДАКТИРОВАТЬ 1:

Вы спросили, как вы собираетесь реализовать мое решение в соответствии с вашей таблицей дат…Вот один из способов сделать это:

 SELECT ot.firstDayOfMonth
    , [0-30]    = SUM(IIF(x.TicketAge BETWEEN 0 AND 30, 1, 0))
    , [31-60]   = SUM(IIF(x.TicketAge BETWEEN 31 AND 60, 1, 0))
    , [61-90]   = SUM(IIF(x.TicketAge BETWEEN 61 AND 90, 1, 0))
    , [91 ]     = SUM(IIF(x.TicketAge >= 91, 1, 0))
FROM @openTicketsBM ot
    LEFT JOIN #taskDB t ON t.created <= ot.firstDayOfMonth -- Only look at tickets that existed at this point in time
        AND (t.closed IS NULL OR t.closed >= ot.firstDayOfMonth OR t.closed < t.created) -- Only look at tickets that were open
    CROSS APPLY (SELECT TicketAge = DATEDIFF(DAY, t.created, ot.firstDayOfMonth)) x
GROUP BY ot.firstDayOfMonth

* Отредактированный запрос так, чтобы вместо него использовался a LEFT JOIN , таким образом, вы увидите месяцы, в течение которых не было открыто ни одного билета.

Некоторые несвязанные наблюдения и личные рекомендации…

Не ссылайтесь на даты, в '' которые они затем преобразуются '1900-01-01' . Если вы используете это как способ указать, что билет открыт, почему бы и нет NULL ?
Используйте понятные / самоописывающие имена столбцов. «созданный» или «закрытый» звучит (для меня) как столбцы флагов / битов, а не столбцы даты. Я бы использовал что-то вроде CreateDateUTC и CloseDateUTC .
Зачем использовать номера билетов, хранящиеся в виде строк, а не int IDENTITY(1,1) ?
Вы должны нормализовать свою таблицу. Вместо использования строк для rating и status используйте столбцы внешнего ключа, ссылающиеся на таблицу подстановки.
Вместо того, чтобы полагаться на spt_values таблицу подсчета, создайте постоянную таблицу дат / календаря (вот пример) или используйте таблицу подсчета на лету, подобную этой:

 --IF OBJECT_ID('tempdb..#tally','U') IS NOT NULL DROP TABLE #tally; --SELECT * FROM #tally
WITH c1 AS (SELECT x.x FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) x(x)) -- 10
    , c2(x) AS (SELECT 1 FROM c1 x CROSS JOIN c1 y) -- 10 * 10
    , c3(rn) AS (SELECT 0 UNION ALL SELECT ROW_NUMBER() OVER (ORDER BY 1/0) FROM c2) -- Add zero record, and row numbers
INTO #tally
SELECT x.rn
FROM c3 x

1. Итак, в моем вопросе вместо использования @AsOfDate для date diff я использую значение (R.firstDayOfMonth) для каждого 1-го дня месяца в сгенерированной таблице @openTicketsBM R . SQL выдает ошибку, указывающую на то, что это «Внешняя ссылка». Есть ли способ, которым я все еще могу использовать это значение?

2. @mister.cake см. Edit 1 В сообщении, я применил свое решение к вашей таблице дат.