Блог

У меня есть следующая таблица SQL Server (2019) :

 CREATE TABLE [dbo].[log_Current](
   [line_number] [int] NULL,
   [Time] [time](7) NULL,
   [server_time_initial] [datetime] NULL,
   [Correct_Date_Time] [datetime] NULL
) ON [PRIMARY]
GO
 

Данные:

и т.д…..

Поля:

• [server_time_initial] является одинаковым для всех строк и представляет время, когда журнал был извлечен. Равно 2021-05-15 07:05:05
• [номер строки] — это порядок ввода в журнал
• [Время] — это время записи в журнале, но, к сожалению, в нем также нет даты.
• Correct_Date_Time — Должна быть заполнена правильная дата время для каждой строки. Как вы можете видеть, записи времени начинаются где-то утром (не обязательно в час ночи), а затем проходят за полночь и т. Д…. И эта закономерность продолжается в течение нескольких дней. Первая партия, представляющая время с 07:05:08 по 23:57:07, до [номер строки] 512, вводится в 2021-05-15. От [номер строки] 513 — это следующий день, т. е. 2021-05-16, до [номер строки] 1889. Затем, начиная с [номер строки] 1890, дата ввода-2021-05-17 и т. Д….

Желаемый результат должен содержать обновленный столбец [Correct_Date_Time] :

   **line_number  Time       server_time_initial**    Correct_Date_Time
  69    07:05:08.0000000    2021-05-15 07:05:05.000      2021-05-15 07:05:08
  72    07:05:25.0000000    2021-05-15 07:05:05.000      2021-05-15 07:05:25 
  73    07:05:30.0000000    2021-05-15 07:05:05.000      2021-05-15 07:05:30   
 215    16:34:36.0000000    2021-05-15 07:05:05.000      2021-05-15 16:34:36 
 216    16:56:01.0000000    2021-05-15 07:05:05.000           etc...
 283    21:06:02.0000000    2021-05-15 07:05:05.000
 284    21:08:52.0000000    2021-05-15 07:05:05.000
 511    23:57:06.0000000    2021-05-15 07:05:05.000
 512    23:57:07.0000000    2021-05-15 07:05:05.000      2021-05-15 23:57:07
 513    00:00:05.0000000    2021-05-15 07:05:05.000      2021-05-16 00:00:05
 514    00:01:57.0000000    2021-05-15 07:05:05.000            etc...
 633    02:49:06.0000000    2021-05-15 07:05:05.000
 634    02:49:08.0000000    2021-05-15 07:05:05.000
 1074   05:13:58.0000000    2021-05-15 07:05:05.000
 1075   05:13:59.0000000    2021-05-15 07:05:05.000
 1888   23:53:52.0000000    2021-05-15 07:05:05.000
 1889   23:54:08.0000000    2021-05-15 07:05:05.000    2021-05-16 23:54:08 
 1890   00:00:16.0000000    2021-05-15 07:05:05.000    2021-05-17 00:00:16 
 1891   00:00:31.0000000    2021-05-15 07:05:05.000    2021-05-17 00:00:31
 

Используйте lag([Time]) для сравнения [Time] , чтобы найти переход на следующий день

 [Time] < lag([Time]) over (order by line_number)
 

для линии после пересечения, d = 1 еще 0

 d = case when [Time] < lag([Time]) over (order by line_number)
         then 1
         else 0 
         end
 

и с помощью этого вы можете рассчитать совокупное количество дней смещения с начала (общее смещение дней).

 sum(d) over (order by line_number) 
 

Чтобы установить время на 00:00:00

 dateadd(day, datediff(day, 0, server_time_initial), 0)
 

и

 Final corrected time = server_time_initial (with Time 00:00:00)
                       total days offset
                       [Time]
 

Сложи все это воедино,

 with cte
as 
(
    select  *,
            d = case when [Time] < lag([Time]) over (order by line_number)
                     then 1
                     else 0 
                     end
    from    [log_Current]
)
select  *,
        dateadd(day,
                sum(d) over (order by line_number),
                dateadd(day, datediff(day, 0, server_time_initial), 0) )
          convert(varchar(10), [Time], 108) as Correct_Date_Time
from    cte
order by line_number
 

db<>демонстрация скрипки