Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Интервалы дат и времени  [new]
ТолькоУчусь1
Guest
Дано: MS SQL 2005.
Если таблица со столбцом, куда пишутся дискретно каждую минуту дата и время. Надо найти интервалы непрерывности времени с и по. Может быть переход времени с одного дня на другой.

Данные имеют вид:
2009-12-10 11:49:09.000
2009-12-10 14:30:08.000
2009-12-10 14:31:07.000
2009-12-10 14:44:15.000
2009-12-10 14:45:12.000
2009-12-10 14:46:12.000
2009-12-10 14:47:14.000
2009-12-10 14:48:14.000
2009-12-10 14:49:15.000
2009-12-10 14:50:13.000

Надо вывести в виде:
2009-12-10 11:49:09.000 2009-12-10 11:49:09.000
2009-12-10 14:30:08.000 2009-12-10 14:31:07.000
2009-12-10 14:44:15.000 2009-12-10 14:49:15.000
2009-12-10 14:50:13.000 2009-12-10 14:50:13.000

За ранее спасибо!
14 дек 09, 12:26    [8062743]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31202
ТолькоУчусь1
Надо вывести в виде:
2009-12-10 11:49:09.000 2009-12-10 11:49:09.000
2009-12-10 14:30:08.000 2009-12-10 14:31:07.000
2009-12-10 14:44:15.000 2009-12-10 14:49:15.000
2009-12-10 14:50:13.000 2009-12-10 14:50:13.000
;with cte
as
(
	select	ROW_NUMBER() over (order by d) as n,
			d
	from my_table 
)
select c1.d, c2.d
from cte c1
	join cte c2 on c2.n = c1.n+1
where c1.n & 1 =1
14 дек 09, 12:36    [8062834]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
aleks2
Guest
Учись

1. Находишь все начала интервалов непрерывности. Это то, что ПОСЛЕ разрыва.
2. Находишь все концы интервалов непрерывности. Это то, что ДО разрыва.
3. Нумеруешь их по-порядку возрастания дат.
4. 1-ое начало и 1-й конец образуют первый интервал нерерывности.
5. И т.д....
14 дек 09, 12:36    [8062836]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
vino
Member

Откуда:
Сообщений: 1191
ТолькоУчусь1
Дано: MS SQL 2005.
Если таблица со столбцом, куда пишутся дискретно каждую минуту дата и время. Надо найти интервалы непрерывности времени с и по. Может быть переход времени с одного дня на другой.

Данные имеют вид:
2009-12-10 11:49:09.000
2009-12-10 14:30:08.000
2009-12-10 14:31:07.000
2009-12-10 14:44:15.000
2009-12-10 14:45:12.000
2009-12-10 14:46:12.000
2009-12-10 14:47:14.000
2009-12-10 14:48:14.000
2009-12-10 14:49:15.000
2009-12-10 14:50:13.000

Надо вывести в виде:
2009-12-10 11:49:09.000 2009-12-10 11:49:09.000
2009-12-10 14:30:08.000 2009-12-10 14:31:07.000
2009-12-10 14:44:15.000 2009-12-10 14:49:15.000
2009-12-10 14:50:13.000 2009-12-10 14:50:13.000

За ранее спасибо!
какая логика в последней строке?
14 дек 09, 12:38    [8062853]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
ТолькоУчусь1
Guest
vino

какая логика в последней строке?[/quot]

В последней строке и правда ошибка, она склеится с предыдущей.

2 alexeyvg,

Немного не корректно работает, выдает:

2009-12-10 11:49:09.000 2009-12-10 14:30:08.000
2009-12-10 14:31:07.000 2009-12-10 14:44:15.000
2009-12-10 14:45:12.000 2009-12-10 14:46:12.000
2009-12-10 14:47:14.000 2009-12-10 14:48:14.000
2009-12-10 14:49:15.000 2009-12-10 14:50:13.000

А должно быть:

2009-12-10 11:49:09.000 2009-12-10 11:49:09.000
2009-12-10 14:30:08.000 2009-12-10 14:31:07.000
2009-12-10 14:44:15.000 2009-12-10 14:50:13.000

Не учитывает, что данные пишутся каждую минуту.
14 дек 09, 12:44    [8062921]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
vino
Member

Откуда:
Сообщений: 1191
aleks2
Учись

1. Находишь все начала интервалов непрерывности. Это то, что ПОСЛЕ разрыва.
2. Находишь все концы интервалов непрерывности. Это то, что ДО разрыва.
3. Нумеруешь их по-порядку возрастания дат.
4. 1-ое начало и 1-й конец образуют первый интервал нерерывности.
5. И т.д....
это точно
14 дек 09, 12:46    [8062943]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
vino
Member

Откуда:
Сообщений: 1191
бери у alexeyvg основу для вычисления разрывов
;with cte
as
(
	select	ROW_NUMBER() over (order by d) as n,
			d
	from my_table 
)
select n, c1.d
from cte c1
	join cte c2 on c2.n = c1.n+1
where DATEDIFF(mi,c1.d,c2.d) > 1
14 дек 09, 12:50    [8062976]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
ТолькоУчусь1
Guest
vino
бери у alexeyvg основу для вычисления разрывов
;with cte
as
(
	select	ROW_NUMBER() over (order by d) as n,
			d
	from my_table 
)
select n, c1.d
from cte c1
	join cte c2 on c2.n = c1.n+1
where DATEDIFF(mi,c1.d,c2.d) > 1


В этом решении не учитывается, когда сразу первый и последний разрывы.
14 дек 09, 13:15    [8063165]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
ТолькоУчусь1
Guest
Неужели никто не может помочь?
14 дек 09, 14:42    [8063726]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
Anddros
Member

Откуда:
Сообщений: 1077
Классический способ:

declare @t table (d datetime primary key)
insert @t select '2009-12-10 11:49:09.000'
union all select '2009-12-10 14:30:08.000'
union all select '2009-12-10 14:31:07.000'
union all select '2009-12-10 14:44:15.000'
union all select '2009-12-10 14:45:12.000'
union all select '2009-12-10 14:46:12.000'
union all select '2009-12-10 14:47:14.000'
union all select '2009-12-10 14:48:14.000'
union all select '2009-12-10 14:49:15.000'
union all select '2009-12-10 14:50:13.000'

;with q1 as (select d d1, row_number()over(order by d)r1 
from @t t1 where not exists(select 1 from @t t2 where t1.d>t2.d and dateadd(mi,-1,t1.d)<=t2.d)),
q2 as (select d d2, row_number()over(order by d)r2 
from @t t1 where not exists(select 1 from @t t2 where t1.d<t2.d and dateadd(mi,1,t1.d)>=t2.d))
select d1,d2 from q1 inner join q2 on r1=r2

В q1 получаем все начальные границы, в q2 - все конечные. И джойним оные.
14 дек 09, 15:00    [8063874]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
ТолькоУчусь1
Неужели никто не может помочь?
попробуйте так:

declare @t table(dt datetime)
insert @t select '2009-12-10 11:49:09.000'
insert @t select '2009-12-10 14:30:08.000' 
insert @t select '2009-12-10 14:31:07.000' 
insert @t select '2009-12-10 14:44:15.000' 
insert @t select '2009-12-10 14:45:12.000' 
insert @t select '2009-12-10 14:46:12.000' 
insert @t select '2009-12-10 14:47:14.000' 
insert @t select '2009-12-10 14:48:14.000' 
insert @t select '2009-12-10 14:49:15.000' 
insert @t select '2009-12-10 14:50:13.000'
--- some more data:
insert @t select '2010-01-11 14:00:11.000' 
insert @t select '2010-01-11 14:00:12.000' 
insert @t select '2010-01-11 14:00:16.000' 
insert @t select '2010-01-11 14:01:00.000' 
insert @t select '2010-01-11 14:01:59.000' 
insert @t select '2010-01-11 14:02:59.000' 
insert @t select '2010-01-11 14:03:00.000'

insert @t select '2010-01-15 23:55:11.000' 
insert @t select '2010-01-15 23:55:12.000' 
insert @t select '2010-01-15 23:56:16.000' 
insert @t select '2010-01-15 23:59:00.000' 
insert @t select '2010-01-15 23:59:59.000' 
insert @t select '2010-01-16 00:00:59.000' 
insert @t select '2010-01-16 00:01:59.000'

;with c as
(
  select d1=t1.dt,tp=isnull(max(tp.dt),t1.dt),dm=isnull(nullif(isnull(datediff(mi,max(tp.dt),t1.dt),0),0),1)
  from @t t1
  left join @t tp on tp.dt<t1.dt
  group by t1.dt
)
--select * from c

select dtBeg=min(tp),dtEnd=max(d1)
from(
select c1.d1,c1.tp,c1.dm, dg=isnull(max(cp.tp),'19000101')
from c c1
left join c cp on c1.dm=1 and cp.dm>1 and c1.tp>cp.tp
where c1.dm<=1
group by c1.d1,c1.tp,c1.dm
)t
group by dg
result:
dtBegdtEnd
2009-12-10 11:49:09.0002009-12-10 11:49:09.000
2009-12-10 14:30:08.0002009-12-10 14:31:07.000
2009-12-10 14:44:15.0002009-12-10 14:50:13.000
2010-01-11 14:00:11.0002010-01-11 14:03:00.000
2010-01-15 23:55:11.0002010-01-15 23:56:16.000
2010-01-15 23:59:00.0002010-01-16 00:01:59.000
14 дек 09, 15:08    [8063944]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
Anddros,

в условии есть нюанс: надо объединять данные, между которыми МИНУТНЫЙ интервал равен 1.
Если под "минутным интервалом" понимать datediff(mi, ..., ...), то на данных:
insert @t select '2010-01-15 23:55:11.000' 
insert @t select '2010-01-15 23:55:12.000' 
insert @t select '2010-01-15 23:56:16.000'
- должен выводиться ОДИН интервал:
select datediff(mi,'2010-01-15 23:55:11.000','2010-01-15 23:56:16.000') есть 1.
Впрочем, могу ошибаться. Пусть ТС рассудит.
14 дек 09, 15:17    [8064001]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
ТолькоУчусь1
Guest
Ozzy-Osbourne
Anddros,

в условии есть нюанс: надо объединять данные, между которыми МИНУТНЫЙ интервал равен 1.
Если под "минутным интервалом" понимать datediff(mi, ..., ...), то на данных:
insert @t select '2010-01-15 23:55:11.000' 
insert @t select '2010-01-15 23:55:12.000' 
insert @t select '2010-01-15 23:56:16.000'
- должен выводиться ОДИН интервал:
select datediff(mi,'2010-01-15 23:55:11.000','2010-01-15 23:56:16.000') есть 1.
Впрочем, могу ошибаться. Пусть ТС рассудит.


Да, интервал пишется раз в минуту, но почему-то получаются на деле еще и разные миллисекунды.

Вариант Anddros хорош, но у него получается результат:
2009-10-12 11:49:09.000 2009-10-12 11:49:09.000
2009-10-12 14:30:08.000 2009-10-12 14:31:07.000
2009-10-12 14:44:15.000 2009-10-12 14:46:12.000
2009-10-12 14:47:14.000 2009-10-12 14:48:14.000
2009-10-12 14:49:15.000 2009-10-12 14:50:13.000

а последние 3 интервала должны быть объеденены в 1. Т.е. миллисекунды не смотрим.


У вас вроде почти все так. На этих небольших данных, а вот есть после последовательностей есть разрывное 1 число, то его не выводит.
14 дек 09, 15:27    [8064090]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
Anddros
Member

Откуда:
Сообщений: 1077
Ozzy-Osbourne
Anddros,

в условии есть нюанс: надо объединять данные, между которыми МИНУТНЫЙ интервал равен 1.
Если под "минутным интервалом" понимать datediff(mi, ..., ...), то на данных:
insert @t select '2010-01-15 23:55:11.000' 
insert @t select '2010-01-15 23:55:12.000' 
insert @t select '2010-01-15 23:56:16.000'
- должен выводиться ОДИН интервал:
select datediff(mi,'2010-01-15 23:55:11.000','2010-01-15 23:56:16.000') есть 1.
Впрочем, могу ошибаться. Пусть ТС рассудит.


Пусть сначала определится, что есть 'минутный интервал'

Например, между
'2010-01-15 23:55:59.000' и '2010-01-15 23:57:00.000' интервал минутный или нет?
Или между
'2010-01-15 23:55:00.000' и '2010-01-15 23:56:59.000'???

:)
14 дек 09, 15:28    [8064097]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
ТолькоУчусь1
Guest
вот скрипт с большим кол-вом данных.

To Ozzy-Osbourne ,
у вас не попадает интервал 2009-12-10 14:59:17.000


create table #t (IMEI int, Date datetime)

insert into #t
select
3, '20091210 11:49:09.000'
union select
3, '20091210 14:30:08.000'
union select
3, '20091210 14:31:07.000'
union select
3, '20091210 14:44:15.000'
union select
3, '20091210 14:45:12.000'
union select
3, '20091210 14:46:12.000'
union select
3, '20091210 14:47:14.000'
union select
3, '20091210 14:48:14.000'
union select
3, '20091210 14:49:15.000'
union select
3, '20091210 14:50:13.000'
union select
3, '20091210 14:51:14.000'
union select
3, '20091210 14:52:15.000'
union select
3, '20091210 14:53:17.000'
union select
3, '20091210 14:59:17.000'
union select
3, '20091210 17:24:06.000'
union select
3, '20091210 17:25:09.000'
union select
3, '20091210 17:26:09.000'
union select
3, '20091210 17:27:20.000'
union select
3, '20091210 17:28:16.000'
union select
3, '20091210 17:29:06.000'
union select
3, '20091210 17:30:08.000'
union select
3, '20091210 17:31:12.000'
union select
3, '20091210 17:32:14.000'
union select
3, '20091210 17:33:08.000'
union select
3, '20091210 17:35:24.000'
union select
3, '20091210 17:37:31.000'
union select
3, '20091210 17:38:14.000'
union select
3, '20091210 17:39:15.000'
union select
3, '20091210 17:40:14.000'
union select
3, '20091210 17:41:15.000'
union select
3, '20091210 17:42:13.000'
union select
3, '20091210 17:43:07.000'
union select
3, '20091210 17:49:09.000'
union select
3, '20091210 17:50:16.000'
union select
3, '20091210 17:54:09.000'
union select
3, '20091210 17:58:07.000'
union select
3, '20091210 18:00:17.000'
union select
3, '20091210 18:03:18.000'
union select
3, '20091210 18:14:09.000'
union select
3, '20091210 19:04:10.000'
union select
3, '20091210 19:05:11.000'
union select
3, '20091210 19:21:13.000'
union select
3, '20091210 19:22:08.000'
union select
3, '20091210 19:23:13.000'
14 дек 09, 15:28    [8064104]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
vino
Member

Откуда:
Сообщений: 1191
ТолькоУчусь1, уже принцип объяснен, надо же думать еще, тем более, что теперь еще и IMEI вам нужен
;with ord
as
(
	SELECT	imei, ROW_NUMBER() over (order by imei, d) as n, d	FROM @my_table
UNION	SELECT DISTINCT imei, 0, 0	FROM @my_table
),
brk
AS
(
	SELECT	ROW_NUMBER() over (order by c1.d) as m,
		c2.imei, c2.d as fd, c1.d as ed
		FROM ord c1 INNER JOIN ord c2 on c2.n = c1.n+1 WHERE DATEDIFF(mi,c1.d,c2.d) > 1
)
SELECT s.imei, s.fd, f.ed
	FROM brk f INNER JOIN brk s ON f.m = s.m+1
	ORDER BY s.m
14 дек 09, 15:57    [8064342]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
vino
Member

Откуда:
Сообщений: 1191
Кстати, если выборка с большим количеством строк, то надо будет ограничение на циклы увеличить, а для тестовых данных так можно
;with ord
as
(
	SELECT	imei, ROW_NUMBER() over (order by imei, d) as n, d	FROM #t
UNION	SELECT DISTINCT imei, 0, 0	FROM #t
),
brk
AS
(
	SELECT	ROW_NUMBER() over (order by c1.d) as m,
		c2.imei, c2.d as fd, c1.d as ed
		FROM ord c1 INNER JOIN ord c2 on c2.n = c1.n+1 WHERE DATEDIFF(mi,c1.d,c2.d) > 1
)
SELECT s.imei, s.fd, f.ed
	FROM brk f INNER JOIN brk s ON f.m = s.m+1
	ORDER BY s.m
здесь порядок по imei не виден, но он есть ;-)
14 дек 09, 16:00    [8064390]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
aleks2
Guest
Блин..., ваше СТЕ стошнит на приличных объемах.
declare @t  table (IMEI int, Date datetime)

insert into @t
select
3, '20091210 11:49:09.000'
union select
3, '20091210 14:30:08.000'
union select
3, '20091210 14:31:07.000'
union select
3, '20091210 14:44:15.000'
union select
3, '20091210 14:45:12.000'
union select
3, '20091210 14:46:12.000'
union select
3, '20091210 14:47:14.000'
union select
3, '20091210 14:48:14.000'
union select
3, '20091210 14:49:15.000'
union select
3, '20091210 14:50:13.000'
union select
3, '20091210 14:51:14.000'
union select
3, '20091210 14:52:15.000'
union select
3, '20091210 14:53:17.000'
union select
3, '20091210 14:59:17.000'
union select
3, '20091210 17:24:06.000'
union select
3, '20091210 17:25:09.000'
union select
3, '20091210 17:26:09.000'
union select
3, '20091210 17:27:20.000'
union select
3, '20091210 17:28:16.000'
union select
3, '20091210 17:29:06.000'
union select
3, '20091210 17:30:08.000'
union select
3, '20091210 17:31:12.000'
union select
3, '20091210 17:32:14.000'
union select
3, '20091210 17:33:08.000'
union select
3, '20091210 17:35:24.000'
union select
3, '20091210 17:37:31.000'
union select
3, '20091210 17:38:14.000'
union select
3, '20091210 17:39:15.000'
union select
3, '20091210 17:40:14.000'
union select
3, '20091210 17:41:15.000'
union select
3, '20091210 17:42:13.000'
union select
3, '20091210 17:43:07.000'
union select
3, '20091210 17:49:09.000'
union select
3, '20091210 17:50:16.000'
union select
3, '20091210 17:54:09.000'
union select
3, '20091210 17:58:07.000'
union select
3, '20091210 18:00:17.000'
union select
3, '20091210 18:03:18.000'
union select
3, '20091210 18:14:09.000'
union select
3, '20091210 19:04:10.000'
union select
3, '20091210 19:05:11.000'
union select
3, '20091210 19:21:13.000'
union select
3, '20091210 19:22:08.000'
union select
3, '20091210 19:23:13.000'

declare @b table(Date datetime, id int identity primary key clustered)
declare @e table(Date datetime, id int identity primary key clustered)
declare @dT int -- секунды интервала непрерывности

set @dT=60 -- минута

insert @b(Date)
select t.Date
FROM @t t left outer join @t tt
ON dateadd(second, -@dT, t.Date)<=tt.Date and tt.Date<t.Date 
WHERE tt.Date is null
ORDER BY t.Date

insert @e(Date)
select t.Date
FROM @t t left outer join @t tt
ON t.Date<tt.Date and tt.Date<=dateadd(second, @dT, t.Date)
WHERE tt.Date is null
ORDER BY t.Date

-- делов то...
select b.Date, e.Date
FROM
@b b INNER JOIN @e e
on b.id=e.id
14 дек 09, 17:01    [8065043]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
ansi
Member

Откуда: страна дураков
Сообщений: 1223
select IMEI,min(Date) as sd,max(Date) as fd
from
(select IMEI,Date,dense_rank()over(partition by IMEI order by datediff(mi,0,Date))n
from #t)r
group by IMEI,datediff(mi,0,Date)-n

при условии, что более одного раза в одну минуту для одного IMEI данные не пишутся, более простое решение

select IMEI,min(Date) as sd,max(Date) as fd
from
(select IMEI,Date,row_number()over(partition by IMEI order by Date)n
from #t)r
group by IMEI,datediff(mi,0,Date)-n
14 дек 09, 17:02    [8065063]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
vino
Member

Откуда:
Сообщений: 1191
aleks2
Блин..., ваше СТЕ стошнит на приличных объемах...

Замечу, что конструкцию CTE я использовал для удобства, но без рекурсий.
возможно, "стошнит", но - просто для понимания и одним запросом ;) Например, на таких данных
DECLARE	@t	TABLE (
	[imei]	int,
	[d]		DateTime
)
INSERT @t
select
3, '20091210 11:49:09.000'
union select
2, '20091210 11:49:09.000'
union select
3, '20091210 14:30:08.000'
union select
3, '20091210 14:31:07.000'
union select
3, '20091210 14:44:15.000'
union select
3, '20091210 14:45:12.000'
union select
3, '20091210 14:46:12.000'
union select
3, '20091210 14:47:14.000'
union select
3, '20091210 14:48:14.000'
union select
3, '20091210 14:49:15.000'
union select
3, '20091210 14:50:13.000'
union select
3, '20091210 14:51:14.000'
union select
3, '20091210 14:52:15.000'
union select
3, '20091210 14:53:17.000'
union select
3, '20091210 14:59:17.000'
union select
3, '20091210 17:24:06.000'
union select
3, '20091210 17:25:09.000'
union select
3, '20091210 17:26:09.000'
union select
3, '20091210 17:27:20.000'
union select
3, '20091210 17:28:16.000'
union select
3, '20091210 17:29:06.000'
union select
3, '20091210 17:30:08.000'
union select
3, '20091210 17:31:12.000'
union select
3, '20091210 17:32:14.000'
union select
3, '20091210 17:33:08.000'
union select
3, '20091210 17:35:24.000'
union select
3, '20091210 17:37:31.000'
union select
3, '20091210 17:38:14.000'
union select
3, '20091210 17:39:15.000'
union select
3, '20091210 17:40:14.000'
union select
3, '20091210 17:41:15.000'
union select
3, '20091210 17:42:13.000'
union select
3, '20091210 17:43:07.000'
union select
3, '20091210 17:49:09.000'
union select
3, '20091210 17:50:16.000'
union select
2, '20091210 11:50:09.000'
union select
2, '20091210 17:49:01.000'
union select
2, '20091210 17:50:01.000'
union select
2, '20091210 17:54:09.000'
union select
2, '20091210 17:55:09.000'
union select
2, '20091210 17:58:07.000'
union select
2, '20091210 18:00:17.000'
union select
2, '20091210 18:03:18.000'
union select
2, '20091210 18:14:09.000'
union select
2, '20091210 19:04:10.000'
union select
2, '20091210 19:05:11.000'
union select
2, '20091210 19:21:13.000'
union select
2, '20091210 19:22:08.000'
union select
2, '20091210 19:23:13.000'

-- Так очень просто для понимания, хотя быстродействие стоит проверить на большом объеме
;with
mix	-- разделение данных по группам imei, это не рекурсия, лишь вложенный вызов
AS
(
	SELECT	imei, 1 as k, d	FROM @t
UNION	SELECT imei, 0, 0	FROM @t GROUP BY imei -- разрыв перед группой
UNION	SELECT imei, 2, max(d)+1	FROM @t GROUP BY imei -- разрыв после группы
),
ord	-- упорядочивание по всем imei для поиска всех разрывов, это тоже не рекурсия
AS
(
	SELECT	imei, ROW_NUMBER() over (order by imei, k, d) as n, d	FROM mix
),
brk	-- выборка всех разрывов (уже похоже на диапазон), это не рекурсия, а использование объявления выборки ord, чтобы не повторяться
AS
(
	SELECT	ROW_NUMBER() over (order by c2.n) as m,
		c2.imei, c2.d as fd, c1.d as ed
		FROM ord c1 INNER JOIN ord c2 on c2.n = c1.n+1 WHERE DATEDIFF(mi,c1.d,c2.d) > 1
)
-- выборка диапазонов
SELECT s.imei, s.fd, f.ed	--, s.m -- здесь порядковый номер диапазона
	FROM brk f INNER JOIN brk s ON f.m = s.m+1
		and f.imei = s.imei -- вывод без вспомогательных разрывов
	ORDER BY s.m

-- Это самый короткий код от ansi, но, возможно, сложнее для понимания:

select IMEI,min(D) as sd, max(D) as fd
from
(select IMEI,D,dense_rank()over(partition by IMEI order by datediff(mi,0,D))n
from @t)r
group by IMEI,datediff(mi,0,D)-n

А в коде от aleks2 что не так?
declare @b table(Date datetime, id int identity primary key clustered)
declare @e table(imei int, Date datetime, id int identity primary key clustered)
declare @dT int -- секунды интервала непрерывности

set @dT=60 -- минута

insert @b(Date)
select t.D
FROM @t t left outer join @t tt
ON dateadd(second, -@dT, t.D)<=tt.D and tt.D<t.D 
WHERE tt.D is null
ORDER BY t.D

insert @e(imei, Date)
select t.imei, t.D
FROM @t t left outer join @t tt
ON t.D<tt.D and tt.D<=dateadd(second, @dT, t.D)
WHERE tt.D is null
ORDER BY t.D

-- делов то...
select imei, b.Date, e.Date
FROM
@b b INNER JOIN @e e
on b.id=e.id
14 дек 09, 17:47    [8065328]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
ТолькоУчусь1
To Ozzy-Osbourne, у вас не попадает интервал 2009-12-10 14:59:17.000
ТолькоУчусь1
Да, интервал пишется раз в минуту...
Вариант Anddros хорош, но у него получается результат:
2009-10-12 11:49:09.000 2009-10-12 11:49:09.000
2009-10-12 14:30:08.000 2009-10-12 14:31:07.000
2009-10-12 14:44:15.000 2009-10-12 14:46:12.000
2009-10-12 14:47:14.000 2009-10-12 14:48:14.000
2009-10-12 14:49:15.000 2009-10-12 14:50:13.000

а последние 3 интервала должны быть объеденены в 1. Т.е. миллисекунды не смотрим.
У вас нет в данных миллисекунд. Я так понял, что речь идёт об объединении в один интервал моментов времени, для которых datediff(mi, t1, t2) <=1, т.е. относящихся или к одной минуте или к "соседним" минутам. В последнем случае разница в секундах может достигать значения 60+59=119.
Проверьте тогда вот этот вариант:
1) ваши тестовые данные:
+
declare @t table(IMEI int, dt datetime)
insert into @t
select
3, '20091210 11:49:09.000'
union select
3, '20091210 14:30:08.000'
union select
3, '20091210 14:31:07.000'
union select
3, '20091210 14:44:15.000'
union select
3, '20091210 14:45:12.000'
union select
3, '20091210 14:46:12.000'
union select
3, '20091210 14:47:14.000'
union select
3, '20091210 14:48:14.000'
union select
3, '20091210 14:49:15.000'
union select
3, '20091210 14:50:13.000'
union select
3, '20091210 14:51:14.000'
union select
3, '20091210 14:52:15.000'
union select
3, '20091210 14:53:17.000'
union select
3, '20091210 14:59:17.000'
union select
3, '20091210 17:24:06.000'
union select
3, '20091210 17:25:09.000'
union select
3, '20091210 17:26:09.000'
union select
3, '20091210 17:27:20.000'
union select
3, '20091210 17:28:16.000'
union select
3, '20091210 17:29:06.000'
union select
3, '20091210 17:30:08.000'
union select
3, '20091210 17:31:12.000'
union select
3, '20091210 17:32:14.000'
union select
3, '20091210 17:33:08.000'
union select
3, '20091210 17:35:24.000'
union select
3, '20091210 17:37:31.000'
union select
3, '20091210 17:38:14.000'
union select
3, '20091210 17:39:15.000'
union select
3, '20091210 17:40:14.000'
union select
3, '20091210 17:41:15.000'
union select
3, '20091210 17:42:13.000'
union select
3, '20091210 17:43:07.000'
union select
3, '20091210 17:49:09.000'
union select
3, '20091210 17:50:16.000'
union select
3, '20091210 17:54:09.000'
union select
3, '20091210 17:58:07.000'
union select
3, '20091210 18:00:17.000'
union select
3, '20091210 18:03:18.000'
union select
3, '20091210 18:14:09.000'
union select
3, '20091210 19:04:10.000'
union select
3, '20091210 19:05:11.000'
union select
3, '20091210 19:21:13.000'
union select
3, '20091210 19:22:08.000'
union select
3, '20091210 19:23:13.000'
--select * from @t
2) query:
;with c as
(
  select d1=t1.dt
         ,dm=isnull(nullif(isnull(datediff(mi,max(tp.dt),t1.dt),0),0),1)
  from @t t1
  left join @t tp on tp.dt<t1.dt
  group by t1.dt
)

select --dx,
       dtBeg=min(d1),dtEnd=max(d1)
from(
select c1.d1,c1.dm
      ,dx=isNull(max( cp.d1 ), c1.d1)
from c c1
left join c cp on (c1.dm=1 and cp.dm>1 and cp.d1<c1.d1 or c1.dm>1 and cp.d1=c1.d1)
group by c1.d1,c1.dm
)x
group by dx
3) result (проверьте, правильно ли теперь расположен момент времени 2009-12-10 14:59:17 ?):
dtBegdtEnd
2009-12-10 11:49:09.0002009-12-10 11:49:09.000
2009-12-10 14:30:08.0002009-12-10 14:31:07.000
2009-12-10 14:44:15.0002009-12-10 14:53:17.000
2009-12-10 14:59:17.0002009-12-10 14:59:17.000
2009-12-10 17:24:06.0002009-12-10 17:33:08.000
2009-12-10 17:35:24.0002009-12-10 17:35:24.000
2009-12-10 17:37:31.0002009-12-10 17:43:07.000
2009-12-10 17:49:09.0002009-12-10 17:50:16.000
2009-12-10 17:54:09.0002009-12-10 17:54:09.000
2009-12-10 17:58:07.0002009-12-10 17:58:07.000
2009-12-10 18:00:17.0002009-12-10 18:00:17.000
2009-12-10 18:03:18.0002009-12-10 18:03:18.000
2009-12-10 18:14:09.0002009-12-10 18:14:09.000
2009-12-10 19:04:10.0002009-12-10 19:05:11.000
2009-12-10 19:21:13.0002009-12-10 19:23:13.000

PS. Эффективность на большом объёме не проверял. Сильно подозреваю, что наилучшим по скорости будет запрос от alex2, но он на ваших данных выдаёт вот это:
imeiDateDate
32009-12-10 11:49:09.0002009-12-10 11:49:09.000
32009-12-10 14:30:08.0002009-12-10 14:31:07.000
32009-12-10 14:44:15.0002009-12-10 14:46:12.000
32009-12-10 14:47:14.0002009-12-10 14:48:14.000
32009-12-10 14:49:15.0002009-12-10 14:50:13.000
32009-12-10 14:51:14.0002009-12-10 14:51:14.000
32009-12-10 14:52:15.0002009-12-10 14:52:15.000
32009-12-10 14:53:17.0002009-12-10 14:53:17.000
32009-12-10 14:59:17.0002009-12-10 14:59:17.000
32009-12-10 17:24:06.0002009-12-10 17:24:06.000
32009-12-10 17:25:09.0002009-12-10 17:26:09.000
32009-12-10 17:27:20.0002009-12-10 17:29:06.000
32009-12-10 17:30:08.0002009-12-10 17:30:08.000
32009-12-10 17:31:12.0002009-12-10 17:31:12.000
32009-12-10 17:32:14.0002009-12-10 17:33:08.000
32009-12-10 17:35:24.0002009-12-10 17:35:24.000
32009-12-10 17:37:31.0002009-12-10 17:38:14.000
32009-12-10 17:39:15.0002009-12-10 17:40:14.000
32009-12-10 17:41:15.0002009-12-10 17:43:07.000
32009-12-10 17:49:09.0002009-12-10 17:49:09.000
32009-12-10 17:50:16.0002009-12-10 17:50:16.000
32009-12-10 17:54:09.0002009-12-10 17:54:09.000
32009-12-10 17:58:07.0002009-12-10 17:58:07.000
32009-12-10 18:00:17.0002009-12-10 18:00:17.000
32009-12-10 18:03:18.0002009-12-10 18:03:18.000
32009-12-10 18:14:09.0002009-12-10 18:14:09.000
32009-12-10 19:04:10.0002009-12-10 19:04:10.000
32009-12-10 19:05:11.0002009-12-10 19:05:11.000
32009-12-10 19:21:13.0002009-12-10 19:22:08.000
32009-12-10 19:23:13.0002009-12-10 19:23:13.000

То есть, в нём учитывается разность между показаниями в секундах и, если показания различаются более чем на 60 сек, то они будут отнесены к разным интервалам, даже если относятся к "соседним" минутам. Если это именно то, что вы хотели - берите этот вариант. Иначе - решайте сами, подпиливать его напильником или юзать другие из представленных решений.
14 дек 09, 22:42    [8066320]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
ТолькоУчусь1
Guest
Ozzy-Osbourne,

В итоге взял за основу запрос от aleks2. До того как выбирать просто тупо убрал секунды, чтобы везде было 00 секунд, тогда запрос от aleks2 работает правильно.

Всем огромное спасибо за помощь.
15 дек 09, 09:44    [8067020]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
vino
Member

Откуда:
Сообщений: 1191
ТолькоУчусь1, а вы обратили внимание на вот этот пост? Там набор данных, на котором вариант aleks2 не совсем точно отрабатывает. Советую проверить и сравнить с другими вариантами.
15 дек 09, 10:36    [8067336]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
ТолькоУчусь1
Guest
vino
ТолькоУчусь1, а вы обратили внимание на вот этот пост? Там набор данных, на котором вариант aleks2 не совсем точно отрабатывает. Советую проверить и сравнить с другими вариантами.


На пост 8066320 я обратил внимание. Я уже написал, что тупо занулил все секунды и у меня теперь получается между данными ровно 60 секунд и все работает пучком.
15 дек 09, 14:54    [8069505]     Ответить | Цитировать Сообщить модератору
 Re: Интервалы дат и времени  [new]
vino
Member

Откуда:
Сообщений: 1191
ТолькоУчусь1, приведите, пожалуйста, запрос, который считаете правильным - развейте сомнения
15 дек 09, 15:24    [8069722]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить