Блог

Главная - Вопросы по программированию - Выберите наибольшее значение за пределами диапазона datetime при группировании

Выберите наибольшее значение за пределами диапазона datetime при группировании

#sql #sql-server #select #grouping

#sql #sql-сервер #выберите #группировка

Вопрос:

У меня есть таблица, содержащая четыре столбца: 

 Id (uniqueidentifier), 
Name (varchar),    
StartDateTime (datetime),
EndDateTime (datetime)

И два входных параметра:

 @StartDateTime (datetime)
@EndDateTime (datetime)

Мой запрос в настоящее время выглядит следующим образом:

 SELECT 
[Id],
[Name],
MIN([StartDateTime]),
MAX([EndDateTime]),
FROM 
[Table] 
WHERE 
[StartDateTime] BETWEEN @StartDateTime AND @EndDateTime
GROUP BY 
[Id],
[Name],
DATEADD(DD, DATEDIFF(DD, 0, [StartDateTime]), 0)

Есть ли способ каким-либо образом также выбрать максимальное значение EndDateTime в таблице, которое меньше MIN([StartDateTime]) , независимо от способа группировки таблицы? Например. для следующего набора данных, если @StartDateTime = '2016-06-01' и @EndDateTime = '2016-06-02' , я бы хотел, чтобы в моем запросе возвращался столбец, который извлекается 2015-05-31 09:07:17.000 как EndDateTime для идентификатора 1, 2015-05-31 09:44:00.000 как EndDateTime для идентификатора 2 и т.д., Поскольку он является самым высоким EndDateTime перед выбранным MIN([StartDateTime]) 

 ID |    StartDateTime             | EndDateTime
---------------------------------------------------------
1  |    2015-05-31 08:44:59.000   | 2015-05-31 09:07:17.000
2  |    2015-05-31 09:12:06.000   | 2015-05-31 09:44:00.000
3  |    2015-05-31 13:25:47.000   | 2015-05-31 13:34:34.000
4  |    2015-05-31 14:15:54.000   | 2015-05-31 14:24:23.000
1  |    2015-06-01 06:08:47.000   | 2015-06-01 06:10:58.000
2  |    2015-06-01 06:12:05.000   | 2015-06-01 07:24:11.000
3  |    2015-06-01 12:54:53.000   | 2015-06-01 12:55:34.000
4  |    2015-06-01 13:32:18.000   | 2015-06-01 13:33:05.000

Ответ №1:

Проверьте, помогает ли это. 

 select t.Id, t.Name, minStart, MAX(t.EndDateTime) from #temp t inner join 
     (select Id, Name, MIN(StartDateTime) minStart from #temp t1 group by Id, Name,
    DATEADD(DD, DATEDIFF(DD, 0, starttime), 0))t1
ON t1.minStart = t.StartDateTime
where t.EndTime < t1.minStart
GROUP BY t.Id, t.Name, minStart

Ответ №2:

Используйте ранжирование, чтобы получить то, что вам нужно. Это рабочий пример ниже.

 create table #Temp
(
    Id int,
    Name varchar(10),
    StartDate DateTime,
    EndDate DateTime
)

insert into #Temp  (Id, StartDate, EndDate)
values
(1  ,    '2015-05-31 08:44:59.000'  , '2015-05-31 09:07:17.000' ),
(2  ,    '2015-05-31 09:12:06.000'  , '2015-05-31 09:44:00.000' ),
(3  ,    '2015-05-31 13:25:47.000'  , '2015-05-31 13:34:34.000' ),
(4  ,    '2015-05-31 14:15:54.000'  , '2015-05-31 14:24:23.000' ),
(1  ,    '2015-06-01 06:08:47.000'  , '2015-06-01 06:10:58.000' ),
(2  ,    '2015-06-01 06:12:05.000'  , '2015-06-01 07:24:11.000' ),
(3  ,    '2015-06-01 12:54:53.000'  , '2015-06-01 12:55:34.000' ),
(4  ,    '2015-06-01 13:32:18.000'  , '2015-06-01 13:33:05.000' )

select s.Id, S.EndDate
from (
        select ID, StartDate, EndDate, Rank() Over(Partition By Id Order By StartDate asc) as xrank
        from #Temp
) s     
where s.xrank = 1                                                     
drop table #temp

Ответ №3:

Не ограничивайте данные с помощью @StartDateTime до вычисления функции lag()

 DECLARE @StartDateTime DATETIME = '2015-06-01' ,
        @EndDateTime DATETIME = '2015-06-02';

SELECT ID,sd,ed,ped 
FROM (
    SELECT [Id],
     sd = MIN([StartDateTime]),
     ed = MAX([EndDateTime]),
     ped = LAG(MAX([EndDateTime])) OVER(PARTITION BY id ORDER BY MIN([StartDateTime]))
    FROM(
        -- sample data
        VALUES (1, CAST('2015-05-31 08:44:59.000' AS DATETIME),CAST('2015-05-31 09:07:17.000' AS DATETIME))
        ,(2,'2015-05-31 09:12:06.000','2015-05-31 09:44:00.000')
        ,(3,'2015-05-31 13:25:47.000','2015-05-31 13:34:34.000')
        ,(4,'2015-05-31 14:15:54.000','2015-05-31 14:24:23.000')
        ,(1,'2015-06-01 06:08:47.000','2015-06-01 06:10:58.000')
        ,(2,'2015-06-01 06:12:05.000','2015-06-01 07:24:11.000')
        ,(3,'2015-06-01 12:54:53.000','2015-06-01 12:55:34.000')
        ,(4,'2015-06-01 13:32:18.000','2015-06-01 13:33:05.000')
        ) t(id,StartDateTime,EndDateTime) 
    WHERE [StartDateTime] <= @EndDateTime
    GROUP BY 
        [Id],
        DATEADD(DD, DATEDIFF(DD, 0, [StartDateTime]), 0)
    ) x    
WHERE sd >= @StartDateTime;