предыдущие и следующие строки

ProgramBox

предыдущие и следующие строки

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

#sql #sql-server

#sql #sql-сервер

Вопрос:

Мне нужен SQL-запрос, который извлекает данные из таблицы, которая содержит непрерывные данные по состоянию, как показано ниже. Если я хочу получить данные за заданное временное окно, скажем, час / день, тогда он должен отфильтровывать записи и сводить информацию о состоянии.

Фильтр: ОТ ‘2016-08-11 23:50:00.000’ До ‘2016-08-13 01:15:00.000’ — 85 Минуты, выделенные желтым цветом ниже-

Фильтры, как показано ниже —

Когда я даю следующие фильтры, тогда

 DECLARE @StartDate DATETIME = '2016-08-11 23:50:00.000'
DECLARE @EndDate   DATETIME = '2016-08-15 01:15:00.000'

В идеале, он должен учитывать временное окно как —

 2016-08-11 23:50:00 to 2016-08-12 01:15:00 - 85 minutes
2016-08-12 23:50:00 to 2016-08-13 01:15:00 - 85 minutes
2016-08-13 23:50:00 to 2016-08-14 01:15:00 - 85 minutes
2016-08-14 23:50:00 to 2016-08-15 01:15:00 - 85 minutes
2016-08-15 23:50:00 to 2016-08-16 01:15:00 - 85 minutes

и так далее ….

ОБНОВЛЕНИЕ 3

Может ли кто-нибудь помочь мне с этим запросом?

Пример данных —

 create table #temp1 ([State] varchar(20),StartTimeStamp Datetime2,    EndTimeStamp Datetime2, DurationInSeconds int)
Insert into #temp1 values('Away',       '2016-08-11 23:40:00.000000',           '2016-08-11 23:45:00.000000',   300     )
,('Appear Away','2016-08-11 23:45:00.000000',           '2016-08-11 23:50:00.000000',   300 )
,('Available', '2016-08-11 23:50:00.000000',            '2016-08-11 23:55:00.000000',   300 )
,('Available','2016-08-11 23:55:00.000000',             '2016-08-11 23:59:59.000000',   299 )
,('Away',       '2016-08-12 00:00:00.000000',           '2016-08-12 00:05:00.000000',   300 )
,('Offline',    '2016-08-12 00:05:00.000000',           '2016-08-12 00:15:00.000000',   600 )
,('Away',       '2016-08-12 00:15:00.000000',           '2016-08-12 00:30:00.000000',   900 )
,('Appear Away','2016-08-12 00:30:00.000000',           '2016-08-12 01:15:00.000000',   2700 )
,('Away',       '2016-08-12 01:15:00.000000',           '2016-08-12 01:30:00.000000',   900 )
,('Offline',    '2016-08-12 01:30:00.000000',           '2016-08-12  18:30:00.000000',   64800 )
,('Appear Away','2016-08-12 18:30:00.000000',           '2016-08-12   23:30:00.000000',   18000 )
,('Available',  '2016-08-12 23:30:00.000000',           '2016-08-12 23:45:00.000000',   900 )
,('Away',       '2016-08-12 23:45:00.000000',           '2016-08-12 23:50:00.000000',   300 )
,('Offline',    '2016-08-12 23:50:00.000000',           '2016-08-12 23:55:00.000000',   300 )
,('Available',  '2016-08-12 23:55:00.000000',           '2016-08-12 23:59:59.000000',   299 )
,('Away',       '2016-08-13 00:00:00.000000',           '2016-08-13 00:05:00.000000',   300 )
,('Offline',    '2016-08-13 00:05:00.000000',           '2016-08-13 00:15:00.000000',   600 )
,('Away',       '2016-08-13 00:15:00.000000',           '2016-08-13 00:30:00.000000',   900 )
,('Appear Away','2016-08-13 00:30:00.000000',           '2016-08-13 01:15:00.000000',   2700 )
,('Away',       '2016-08-13 01:15:00.000000',           '2016-08-13 01:30:00.000000',   900 )

UPDATE2: ожидаемый результат:

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

2. Покажите ожидаемый результат, пожалуйста.

3. Изменения, внесенные в вопрос @GordonLinoff

4. @NEER — Разве это невозможно в SQL?

5. @RameshwarPawale Непонятно. Как вы находите Offline: 763 ?

Ответ №1:

Вы можете, как показано ниже:

 DECLARE @StartDate DATETIME = '2016-08-8 17:00:00.000'
DECLARE @EndDate DATETIME = '2016-08-15 18:00:00.000' 

;WITH TmpCte
AS
(
    SELECT
        T.*,
        IIF(T.StartTimeStamp < DATEADD(ms, DATEDIFF(ms, '00:00:00', CAST(@StartDate AS TIME)), CAST(CAST(T.StartTimeStamp AS DATE) AS DATETIME)), 
                DATEADD(ms, DATEDIFF(ms, '00:00:00', CAST(@StartDate AS TIME)), CAST(CAST(T.StartTimeStamp AS DATE) AS DATETIME)), 
                T.StartTimeStamp) AS TmpStart,
        IIF(T.EndTimeStamp > DATEADD(ms, DATEDIFF(ms, '00:00:00', CAST(@EndDate AS TIME)), CAST(CAST(T.EndTimeStamp AS DATE) AS DATETIME)),
                DATEADD(ms, DATEDIFF(ms, '00:00:00', CAST(@EndDate AS TIME)), CAST(CAST(T.EndTimeStamp AS DATE) AS DATETIME)), 
                T.EndTimeStamp) AS TmpEnd        
    FROM
        #temp T
), CTE as (
    SELECT * FROM 
    (
          SELECT
            *,
            ABS(DATEDIFF(SECOND, TmpStart, TmpEnd)) TmpDuration
          FROM TmpCte
          where 
            TmpStart >= @StartDate and 
            TmpEnd <=  @EndDate AND
            TmpEnd > TmpStart       
    ) A
    PIVOT(SUM(TmpDuration)
    FOR state IN ([Appear Away],[Available],[Away],[Offline],[online])  ) as     Pivottable

  )
  select CAST(StartTimestamp as Date) as LocalDate,SUM([Available]) as [Available] , SUM([Away]) as Away,SUM([Online]) as [Online],
  SUM(Offline) as [Offline] 
  from CTE 
  group by CAST(StartTimestamp as Date)

Результат:

  ------------ ----------- ------ -------- --------- 
| LocalDate  | Available | Away | Online | Offline |
 ------------ ----------- ------ -------- --------- 
| 2016-08-11 |       600 | 1039 |    163 |     600 |
 ------------ ----------- ------ -------- ---------

Ответ №2:

Попробуйте, как показано ниже.

 DECLARE @StartDate DATETIME = '2016-08-11 23:45:00.000'
DECLARE @EndDate DATETIME = '2016-08-12 01:00:00.000' 

;WITH TmpCte
AS
(
    SELECT
        T.*,
        IIF(T.StartTimeStamp < @StartDate, 
                @StartDate,
                T.StartTimeStamp) AS TmpStart,
        IIF(T.EndTimeStamp > @EndDate,              
              @EndDate,
                T.EndTimeStamp) AS TmpEnd        
    FROM
        #temp T
), CTE as (
    SELECT * FROM 
    (
          SELECT
            *,
            ABS(DATEDIFF(SECOND, TmpStart, TmpEnd)) TmpDuration
          FROM TmpCte
          where 
            TmpStart >= @StartDate and 
            TmpEnd <=  @EndDate AND
            TmpEnd > TmpStart       
    ) A
    PIVOT(SUM(TmpDuration)
    FOR state IN ([Appear Away],[Available],[Away],[Offline],[online])  ) as     Pivottable

  )
  SELECT  
    CAST(StartTimestamp as Date) as LocalDate,
    SUM([Appear Away]) as [Appear Away], 
    SUM([Available]) as [Available] , 
    SUM([Away]) as Away,SUM([Online]) as [Online],
    SUM(Offline) as [Offline] 
  from CTE 
  group by CAST(StartTimestamp as Date)

1. Это не сработает, если у меня есть промежуток в несколько дней. Если у меня смена с 11:30 вечера до 1:00 ночи и в течение нескольких дней, то это не сработает. Однако в вашем первом ответе сдвиг был с 5: 00 вечера (17:00) до 6: 00 вечера (18:00) для каждого дня, и он работал правильно.

2. Когда я даю следующие фильтры, ОБЪЯВИТЕ @-StartDate DATETIME = ‘2016-08-11 23:00:00.000’ ОБЪЯВИТЕ @-EndDate DATETIME = ‘2016-08-15 01:00:00.000’ Тогда в идеале, он должен учитывать временное окно как — 2016-08-11 23:00:00 до 2016-08-12 01:00:00 2016-08-12 23:00:00 к 2016-08-13 01:00:00 2016-08-13 23:00:00 к 2016-08-14 01:00:00 2016-08-14 23:00:00 к 2016-08-15 01:00:00 2016-08-15 23:00:00 до 2016-08-16 01:00:00

3. @RameshwarPawale Для меня непонятно. Этот запрос дает ожидаемый результат. В чем же проблема?