Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Нахождение мин. и макс. дат вхождения в диапазон  [new]
Kast2K
Member

Откуда: Санкт-Петербург
Сообщений: 499
Добрый день!

В таблице есть вхождения дат и необходимо найти эти диапазоны.

Как пытался:
 declare @t table (Element nvarchar(5), DTS datetime, DTE datetime)

 insert into @t
 (Element,DTS,DTE)
 values
 ('A', '2019-12-12 05:00:00', '2019-12-12 05:05:00'),
 ('A', '2019-12-12 05:02:00', '2019-12-12 05:07:00'),
 ('A', '2019-12-12 05:03:00', '2019-12-12 05:09:00'),
 ('A', '2019-12-12 05:12:00', '2019-12-12 05:15:00')

    select
		top 1 with ties
		he.Element,
		he.DTS,
		fj.DTE
	from @t he
	full outer join (    select
		top 1 with ties
		he.Element,
		he.DTE
	from @t he
	order by ROW_NUMBER() over (partition by he.Element order by he.DTE desc)) fj on
	fj.Element=he.Element 
	order by ROW_NUMBER() over (partition by he.Element order by he.DTS)


В результате ожидаемо получаю
ElementDTSDTE
A2019-12-12 05:00:00.0002019-12-12 05:15:00.000


Ожидаемый результат:
ElementDTSDTE
A2019-12-12 05:00:00.0002019-12-12 05:09:00.000
A2019-12-12 05:12:00.0002019-12-12 05:15:00.000


С одной стороны можно было бы создать курсор и пробежаться по отсортированной по Element и DTS таблице и определить наличие вхождений текущих записей в предыдущие, но это какой-то кривой способ получается и при наличии 20+К записей будет достаточно долгим.

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

Подскажите, пожалуйста.

Спасибо.
19 дек 19, 10:24    [22043924]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20203
https://www.sql.ru/forum/afsearch.aspx?s=?????????? ?????????&submit=?????&bid=1
19 дек 19, 10:47    [22043947]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Kast2K,

https://www.sql.ru/forum/1218798/zapros-na-ukrupnenie-periodov
19 дек 19, 11:17    [22043986]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
Kast2K
Member

Откуда: Санкт-Петербург
Сообщений: 499
Коллеги,

Требуется ваша помощь. :(

В таблице проранжировал все временные отрезки, но если добавляю строчку С, то на выходе получается что первая строка и последняя встают по рангу друг за другом и, следовательно, весь расчет сбивается.
Может быть вы глянете своим свежим не замыленным взглядом, что я упускаю?

 declare @t table (id int identity ,Element nvarchar(5), DateBegin datetime, DateEnd datetime)

 insert into @t
 (Element,DateBegin,DateEnd)
 values
 ('A', '2019-12-12 05:00:00', '2019-12-12 05:05:00'),
 ('A', '2019-12-12 05:02:00', '2019-12-12 05:07:00'),
 ('A', '2019-12-12 05:03:00', '2019-12-12 05:09:00'),
 ('A', '2019-12-12 05:11:00', '2019-12-12 05:15:00')
 ,('B', '2019-12-12 05:10:00', '2019-12-12 05:18:00')
 ,('B', '2019-12-12 05:17:00', '2019-12-12 05:19:00')
 ,('B', '2019-12-12 05:27:00', '2019-12-12 05:29:00')
 ,('C', '2019-12-12 05:00:00', '2019-12-12 05:05:00')

		select 
			Element,
			DateBegin,
			DateEnd,
			max(DateEnd) over (partition by Element ) maxx,
			row_number() over (order by Datebegin),
			row_number() over (partition by Element order by DateBegin),
			row_number() over (order by DateBegin)-row_number() over (partition by Element order by DateBegin) rn
		from @t

select 
	Element, 
	rn,
	min(DateBegin), 
	max(dateend)
from (
		select 
			Element,
			DateBegin,
			DateEnd,
			max(DateEnd) over () maxx,
			row_number() over (order by Datebegin)-row_number() over (partition by Element order by DateBegin) rn
		from @t
	  ) ttt
group by Element,rn,maxx


В итоге должно получиться 5 строчек:
A 0 2019-12-12 05:00:00.000 2019-12-12 05:09:00.000
A 2 2019-12-12 05:11:00.000 2019-12-12 05:15:00.000
B 4 2019-12-12 05:10:00.000 2019-12-12 05:18:00.000
B 5 2019-12-12 05:17:00.000 2019-12-12 05:29:00.000
C 1 2019-12-12 05:00:00.000 2019-12-12 05:05:00.000


но сейчас выходит 6
A 0 2019-12-12 05:00:00.000 2019-12-12 05:05:00.000
A 1 2019-12-12 05:02:00.000 2019-12-12 05:09:00.000
A 2 2019-12-12 05:11:00.000 2019-12-12 05:15:00.000
B 4 2019-12-12 05:10:00.000 2019-12-12 05:18:00.000
B 5 2019-12-12 05:17:00.000 2019-12-12 05:29:00.000
C 1 2019-12-12 05:00:00.000 2019-12-12 05:05:00.000


Сообщение было отредактировано: 19 дек 19, 17:08
19 дек 19, 17:08    [22044566]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
Kast2K,

порядок строк определяет выражение сортировки и больше ничто.
19 дек 19, 17:38    [22044604]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
Remind
Member

Откуда: UK
Сообщений: 523
 declare @t table (id int identity ,Element nvarchar(5), DateBegin datetime, DateEnd datetime)

 insert into @t
 (Element,DateBegin,DateEnd)
 values
 ('A', '2019-12-12 05:00:00', '2019-12-12 05:05:00'),
 ('A', '2019-12-12 05:02:00', '2019-12-12 05:07:00'),
 ('A', '2019-12-12 05:03:00', '2019-12-12 05:09:00'),
 ('A', '2019-12-12 05:11:00', '2019-12-12 05:15:00')
 ,('B', '2019-12-12 05:10:00', '2019-12-12 05:18:00')
 ,('B', '2019-12-12 05:17:00', '2019-12-12 05:19:00')
 ,('B', '2019-12-12 05:27:00', '2019-12-12 05:29:00')
 ,('C', '2019-12-12 05:00:00', '2019-12-12 05:05:00')

;WITH CTE AS
(
  SELECT
    *,
    LAG(DateBegin)  OVER (PARTITION BY Element ORDER BY DateBegin) AS PrevBegin,
    LAG(DateEnd)    OVER (PARTITION BY Element ORDER BY DateBegin) AS PrevEnd   
  FROM @t

),
Groups AS
(
  SELECT *, SUM(CASE WHEN (PrevBegin <= DateEnd AND PrevEnd >= DateBegin) THEN 1 ELSE 0 END) OVER (PARTITION BY Element ORDER BY DateBegin) AS GRP
  FROM CTE
),
FinalGroups AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Element ORDER BY DateBegin) - GRP AS FinalGroup
  FROM Groups
)
SELECT Element, MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd
FROM FinalGroups
GROUP BY Element, FinalGroup
ORDER BY 1, 2
19 дек 19, 18:47    [22044654]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
nullin
Member

Откуда: pullin
Сообщений: 140
Remind, нет гарантии, что в очередном вхождении не будет существовать вложенного интервала дат в один из существующих d(s,e)=(1,9),(4,5).
Если докинуть логически непротиворечивых данных(s<=e) на подобие следующих:
 ,('C', '2019-12-12 05:01:00', '2019-12-12 05:05:00')
 ,('C', '2019-12-12 05:02:00', '2019-12-12 05:05:00')
 ,('C', '2019-12-12 05:03:00', '2019-12-12 05:05:00')
 ,('C', '2019-12-12 05:04:00', '2019-12-12 05:08:00')
 ,('C', '2019-12-12 05:04:00', '2019-12-12 05:07:00')
 ,('C', '2019-12-12 05:04:00', '2019-12-12 05:06:00')

То в таком случае последний запрос возвращает некорректные данные. (В наличии пересечение интервала + вложенный).

Вот что у меня получилось:
with DatesСollapsed as
(select a.Element, min(a.DateBegin) as DateStart, a.CoveringDateEnd as DateEnd
        /*without group by*/
        /*min(a.DateBegin)
         over(partition by a.Element, a.CoveringDateEnd) as DateStart*/
   from (select t.Element, t.DateBegin, t.DateEnd,
                max(t.DateEnd)
                 over(partition by t.Element order by t.DateBegin) as CoveringDateEnd
           from @t as t) as a
  group by a.Element, a.CoveringDateEnd)
  
, DatesGroupMarked as
(select b.Element, b.DateStart, b.DateEnd, 
        sum(b.CrossingDateStatus)
         over(partition by b.Element order by b.DateStart) as CrossingGroup
   from (select c.Element, c.DateStart, c.DateEnd,
                iif(lag(c.DateEnd, 1, c.DateStart)
                     over(partition by c.Element order by c.DateStart) >= c.DateStart, 0, 1) as CrossingDateStatus 
           from DatesСollapsed as c) as b)
           
, DatesFinal as
(select g.Element,
        min(g.DateStart) as DateStart,
        max(g.DateEnd) as DateEnd
   from DatesGroupMarked as g
  group by g.Element, g.CrossingGroup)
  
select f.Element, f.DateStart, f.DateEnd
  from DatesFinal as f
  
 order by f.Element, f.DateStart

Нейминг стилем автора)))

Сообщение было отредактировано: 20 дек 19, 02:00
20 дек 19, 01:50    [22044824]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
declare @t table (Element nvarchar(5), DTS datetime, DTE datetime)

 insert into @t
 (Element,DTS,DTE)
 values
 ('A', '2019-12-12 05:00:00', '2019-12-12 05:05:00'),
 ('A', '2019-12-12 05:02:00', '2019-12-12 05:07:00'),
 ('A', '2019-12-12 05:03:00', '2019-12-12 05:09:00'),
 ('A', '2019-12-12 05:11:00', '2019-12-12 05:15:00')
 ,('B', '2019-12-12 05:10:00', '2019-12-12 05:18:00')
 ,('B', '2019-12-12 05:17:00', '2019-12-12 05:19:00')
 ,('B', '2019-12-12 05:27:00', '2019-12-12 05:29:00')
 ,('C', '2019-12-12 05:00:00', '2019-12-12 05:05:00')
 
 
 
 ;with a as(
 select*
 ,ROW_NUMBER() over(partition by Element order by DTS) n
 from @t
 )
 select Element,MIN(DTS) DTS,MAX(DTE) DTE
 from(
   select Element,DTS,DTE,num
 from(
 select a.Element,a.DTS,a.DTE,
 ROW_NUMBER() over(partition by a.Element order by a.DTS) m,
  case when  a.DTE < a1.DTS then 1 else 0 end num
 from a
  join a a1 on a1.n - a.n = 1
  ) c
  where m=1
 union 
 select Element,DTS,DTE,max(num) num
 from(
 select a1.Element,a1.DTS,a1.DTE,
 ROW_NUMBER() over(partition by a.Element order by a.DTS) m,
  case when  a.DTE < a1.DTS then 1 else 0 end num
 from a
  join a a1 on a1.n - a.n = 1
  ) c
  where m>1
  group by Element,DTS,DTE
  
 ) b
 group by Element,num
 order by Element
20 дек 19, 04:26    [22044848]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
Remind
Member

Откуда: UK
Сообщений: 523
nullin,

Достаточно добавить DateEnd в ORDER BY:
 declare @t table (id int identity ,Element nvarchar(5), DateBegin datetime, DateEnd datetime)

 insert into @t
 (Element,DateBegin,DateEnd)
 values
 ('A', '2019-12-12 05:00:00', '2019-12-12 05:05:00'),
 ('A', '2019-12-12 05:02:00', '2019-12-12 05:07:00'),
 ('A', '2019-12-12 05:03:00', '2019-12-12 05:09:00'),
 ('A', '2019-12-12 05:11:00', '2019-12-12 05:15:00')
 ,('B', '2019-12-12 05:10:00', '2019-12-12 05:18:00')
 ,('B', '2019-12-12 05:17:00', '2019-12-12 05:19:00')
 ,('B', '2019-12-12 05:27:00', '2019-12-12 05:29:00')
 ,('C', '2019-12-12 05:00:00', '2019-12-12 05:05:00')
 ,('C', '2019-12-12 05:01:00', '2019-12-12 05:05:00')
 ,('C', '2019-12-12 05:02:00', '2019-12-12 05:05:00')
 ,('C', '2019-12-12 05:03:00', '2019-12-12 05:05:00')
 ,('C', '2019-12-12 05:04:00', '2019-12-12 05:08:00')
 ,('C', '2019-12-12 05:04:00', '2019-12-12 05:07:00')
 ,('C', '2019-12-12 05:04:00', '2019-12-12 05:06:00')

;WITH CTE AS
(
  SELECT
    *,
    LAG(DateBegin)  OVER (PARTITION BY Element ORDER BY DateBegin, DateEnd) AS PrevBegin,
    LAG(DateEnd)    OVER (PARTITION BY Element ORDER BY DateBegin, DateEnd) AS PrevEnd   
  FROM @t

),
Groups AS
(
  SELECT *, SUM(CASE WHEN (PrevBegin <= DateEnd AND PrevEnd >= DateBegin) THEN 1 ELSE 0 END) OVER (PARTITION BY Element ORDER BY DateBegin, DateEnd) AS GRP
  FROM CTE
),
FinalGroups AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Element ORDER BY DateBegin, DateEnd) - GRP AS FinalGroup
  FROM Groups
)
SELECT Element, MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd
FROM FinalGroups
GROUP BY Element, FinalGroup
ORDER BY 1, 2
20 дек 19, 13:20    [22045172]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
entrypoint
Member

Откуда:
Сообщений: 227
Remind,

Решение не работает

insert into @t
(Element,DateBegin,DateEnd)
values
('A', '1753-12-12 05:00:00', '2050-12-12 05:05:00'), --- ***
('A', '2019-12-12 05:00:00', '2019-12-12 05:05:00'),
('A', '2019-12-12 05:02:00', '2019-12-12 05:07:00'),
('A', '2019-12-12 05:03:00', '2019-12-12 05:09:00'),
('A', '2019-12-12 05:11:00', '2019-12-12 05:15:00'),
('B', '1753-12-12 05:00:00', '2050-12-12 05:05:00') --- ***
,('B', '2019-12-12 05:10:00', '2019-12-12 05:18:00')
,('B', '2019-12-12 05:17:00', '2019-12-12 05:19:00')
,('B', '2019-12-12 05:27:00', '2019-12-12 05:29:00')
,('C', '1753-12-12 05:00:00', '2050-12-12 05:05:00') --- ***
,('C', '2019-12-12 05:00:00', '2019-12-12 05:05:00')
,('C', '2019-12-12 05:01:00', '2019-12-12 05:05:00')
,('C', '2019-12-12 05:02:00', '2019-12-12 05:05:00')
,('C', '2019-12-12 05:03:00', '2019-12-12 05:05:00')
,('C', '2019-12-12 05:04:00', '2019-12-12 05:08:00')
,('C', '2019-12-12 05:04:00', '2019-12-12 05:07:00')
,('C', '2019-12-12 05:04:00', '2019-12-12 05:06:00')
20 дек 19, 15:04    [22045287]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
entrypoint
Member

Откуда:
Сообщений: 227
3unknown,
???

insert into @t
(Element,DTS,DTE)
values
('A', '1753-12-12 05:00:00', '2030-12-12 05:05:00'), -- ***
('A', '2019-12-12 05:00:00', '2019-12-12 05:05:00'),
('A', '2019-12-12 05:02:00', '2019-12-12 05:07:00'),
('A', '2019-12-12 05:03:00', '2019-12-12 05:09:00'),
('A', '2019-12-12 05:11:00', '2019-12-12 05:15:00')
,('B', '1753-12-12 05:10:00', '2030-12-12 05:18:00') -- ***
,('B', '2019-12-12 05:10:00', '2019-12-12 05:18:00')
,('B', '2019-12-12 05:17:00', '2019-12-12 05:19:00')
,('B', '2019-12-12 05:27:00', '2019-12-12 05:29:00')
,('C', '2019-12-12 05:00:00', '2019-12-12 05:05:00')
,('C', '1753-12-12 05:00:00', '2030-12-12 05:05:00') -- ***
20 дек 19, 15:06    [22045291]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
Экзотика
 declare @t table (id int identity ,Element nvarchar(5), DateBegin datetime, DateEnd datetime)

 insert into @t
 (Element,DateBegin,DateEnd)
 values
 ('A', '2019-12-12 05:00:00', '2019-12-12 05:05:00'),
 ('A', '2019-12-12 05:02:00', '2019-12-12 05:07:00'),
 ('A', '2019-12-12 05:03:00', '2019-12-12 05:09:00'),
 ('A', '2019-12-12 05:11:00', '2019-12-12 05:15:00'),
 ('A', '2019-12-12 05:14:00', '2019-12-12 05:19:00')
 ,('B', '2019-12-12 05:10:00', '2019-12-12 05:18:00')
 ,('B', '2019-12-12 05:17:00', '2019-12-12 05:19:00')
 ,('B', '2019-12-12 05:27:00', '2019-12-12 05:29:00')
 ,('C', '2019-12-12 05:00:00', '2019-12-12 05:05:00');
 
with a as
(
 select
  t.id, t.Element, dl.dt, dl.f,
  lag(dl.f, 1, 1) over (partition by t.Element order by dl.dt) as pf,
  lead(dl.f, 1, 0) over (partition by t.Element order by dl.dt) as nf
 from
  @t t cross apply
  (values (t.DateBegin, 0), (t.DateEnd, 1)) dl(dt, f)
),
b as
(
 select
  *,
  (row_number() over (partition by Element order by dt, f) - 1) / 2 as g
 from
  a
 where
  (f = 0 and pf = 1) or (f = 1 and nf = 0)
)
select
 Element, min(dt), max(dt)
from
 b
group by
 Element, g
order by
 Element, min(dt);
20 дек 19, 15:54    [22045354]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
nullin
Member

Откуда: pullin
Сообщений: 140
Kast2K, вы поясните, случай, который я охарактеризовал у вас возможен?
Если - да, то запрос 3unknown, тоже нерабочий, потому что здесь уже шаблонное решение требует учета дополнительных условий.
Почему у unknown не работает - читая по диагонали не понятно, надо разбираться.
Кинул все предложенные варианты(последние) на dbfiddle
20 дек 19, 15:55    [22045356]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
entrypoint
Member

Откуда:
Сообщений: 227
invm,

insert into @t
(Element,DateBegin,DateEnd)
values
('A', '2019-12-12 04:59:59', '2019-12-12 05:19:01'), -- **
('A', '2019-12-12 05:00:00', '2019-12-12 05:05:00'),
('A', '2019-12-12 05:02:00', '2019-12-12 05:07:00'),
('A', '2019-12-12 05:03:00', '2019-12-12 05:09:00'),
('A', '2019-12-12 05:11:00', '2019-12-12 05:15:00'),
('A', '2019-12-12 05:14:00', '2019-12-12 05:19:00')
,('B', '2019-12-12 05:10:00', '2019-12-12 05:18:00')
,('B', '2019-12-12 05:17:00', '2019-12-12 05:19:00')
,('B', '2019-12-12 05:27:00', '2019-12-12 05:29:00')
,('C', '2019-12-12 05:00:00', '2019-12-12 05:05:00');
20 дек 19, 15:57    [22045361]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
nullin
Member

Откуда: pullin
Сообщений: 140
invm, а у вас тоже что-то пошло не так))) хотя автор еще не пояснил Картинка с другого сайта.
20 дек 19, 15:59    [22045364]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
Remind
Member

Откуда: UK
Сообщений: 523
entrypoint,

Пятница :(

;WITH CTE AS
(
  SELECT
    *,
    LAG(DateBegin)  OVER (PARTITION BY Element ORDER BY DateBegin, DateEnd) AS PrevBegin,
    LAG(DateEnd)    OVER (PARTITION BY Element ORDER BY DateBegin, DateEnd) AS PrevEnd   
  FROM @t t
  WHERE NOT EXISTS
  (
    SELECT 1 FROM @t WHERE Element = t.Element AND DateBegin < t.DateBegin AND DateEnd > t.DateEnd
  )
),
Groups AS
(
  SELECT *, SUM(CASE WHEN (PrevBegin <= DateEnd AND PrevEnd >= DateBegin) THEN 1 ELSE 0 END) OVER (PARTITION BY Element ORDER BY DateBegin, DateEnd) AS GRP
  FROM CTE
),
FinalGroups AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Element ORDER BY DateBegin, DateEnd) - GRP AS FinalGroup
  FROM Groups
)
SELECT Element, MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd
FROM FinalGroups
GROUP BY Element, FinalGroup
ORDER BY 1, 2
20 дек 19, 16:02    [22045374]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
Kast2K
Member

Откуда: Санкт-Петербург
Сообщений: 499
Добрый день!

хочу сразу оговориться, что сервер 2008 (стыжусь, что не указал сразу) и не все предложенные варианты на нем стартуют.
Обнаруженные проблемы при таком представлении данных (настоящие):
A541 2019-12-18 10:55:24.627 2019-12-18 11:14:44.710 1
A541 2019-12-18 10:56:44.623 2019-12-18 11:15:25.733 2
A541 2019-12-18 10:59:25.163 2019-12-18 11:18:04.990 3
A541 2019-12-18 11:02:05.350 2019-12-18 11:20:45.003 4
A541 2019-12-18 11:04:44.793 2019-12-18 11:23:24.670 5
A541 2019-12-18 11:07:24.960 2019-12-18 11:26:05.327 6
A541 2019-12-18 11:10:04.913 2019-12-18 11:28:45.030 7
A541 2019-12-18 11:12:45.313 2019-12-18 11:31:24.917 8
A541 2019-12-18 15:33:24.340 2019-12-18 15:52:04.523 9
A541 2019-12-18 15:36:44.180 2019-12-18 15:55:24.620 10
A541 2019-12-18 15:40:04.193 2019-12-18 15:59:24.637 11
A541 2019-12-18 15:42:44.327 2019-12-18 16:02:05.063 12
A541 2019-12-18 15:54:04.840 2019-12-18 16:12:05.450 13
A541 2019-12-18 15:56:44.480 2019-12-18 16:14:44.850 14
A541 2019-12-18 15:58:04.407 2019-12-18 16:16:05.407 15


При данной выборке из БД все примеры спотыкаются (в частности от 3unknown) и в результате получается
A541 2019-12-18 10:55:24.627 2019-12-18 15:52:04.523
A541 2019-12-18 15:33:24.340 2019-12-18 16:16:05.407


nullin
Kast2K, вы поясните, случай, который я охарактеризовал у вас возможен?
Если - да, то запрос 3unknown, тоже нерабочий, потому что здесь уже шаблонное решение требует учета дополнительных условий.
Почему у unknown не работает - читая по диагонали не понятно, надо разбираться.
Кинул все предложенные варианты(последние) на dbfiddle


Возможен только при услови, что Element разные.
В один момент времени в систему попадает несколько Element с одинаковой датой\временем (
A541, 2019-12-18 15:58:04.407,
B741, 2019-12-18 15:58:04.407,
C121, 2019-12-18 15:58:04.407,
)
Выходят (DTE )они также в один и тот же момент

Сообщение было отредактировано: 20 дек 19, 16:56
20 дек 19, 16:48    [22045416]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
Remind
Member

Откуда: UK
Сообщений: 523
Kast2K,

Интересно какой результат вы ожидаете на таких входных данных.
20 дек 19, 17:01    [22045428]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Kast2K, а один из ответов по ссылке из второго ответа в этой теме отдает верный ответ )

Осталось там только группировку по Element добавить.

declare @t table(
	 ID int PRIMARY KEY CLUSTERED,
     Element varchar(100)
	,DateBegin datetime
	,DateEnd datetime
);

insert into @t (Element, DateBegin, DateEnd, ID) VALUES 
('A541', '2019-12-18 10:55:24.627', '2019-12-18 11:14:44.710',	1 ),
('A541', '2019-12-18 10:56:44.623', '2019-12-18 11:15:25.733',	2 ),
('A541', '2019-12-18 10:59:25.163', '2019-12-18 11:18:04.990',	3 ),
('A541', '2019-12-18 11:02:05.350', '2019-12-18 11:20:45.003',	4 ),
('A541', '2019-12-18 11:04:44.793', '2019-12-18 11:23:24.670',	5 ),
('A541', '2019-12-18 11:07:24.960', '2019-12-18 11:26:05.327',	6 ),
('A541', '2019-12-18 11:10:04.913', '2019-12-18 11:28:45.030',	7 ),
('A541', '2019-12-18 11:12:45.313', '2019-12-18 11:31:24.917',	8 ),
('A541', '2019-12-18 15:33:24.340', '2019-12-18 15:52:04.523',	9 ),
('A541', '2019-12-18 15:36:44.180', '2019-12-18 15:55:24.620',	10),
('A541', '2019-12-18 15:40:04.193', '2019-12-18 15:59:24.637',	11),
('A541', '2019-12-18 15:42:44.327', '2019-12-18 16:02:05.063',	12),
('A541', '2019-12-18 15:54:04.840', '2019-12-18 16:12:05.450',	13),
('A541', '2019-12-18 15:56:44.480', '2019-12-18 16:14:44.850',	14),
('A541', '2019-12-18 15:58:04.407', '2019-12-18 16:16:05.407',	15);


with a as
(
 select
  t.DateBegin, row_number() over (order by t.DateBegin) as rn
 from
  @t t
 where
  not exists(select 1 from @t where DateBegin < t.DateBegin and DateBegin <= t.DateEnd and DateEnd >= t.DateBegin)
),
b as
(
 select
  t.DateEnd, row_number() over (order by t.DateEnd) as rn
 from
  @t t
 where
  not exists(select 1 from @t where DateEnd > t.DateEnd and DateBegin <= t.DateEnd and DateEnd >= t.DateBegin)
)
select
 a.DateBegin, b.DateEnd
from
 a join
 b on b.rn = a.rn;
20 дек 19, 17:03    [22045431]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
Kast2K
Member

Откуда: Санкт-Петербург
Сообщений: 499
Remind
Kast2K,

Интересно какой результат вы ожидаете на таких входных данных.


A541 2019-12-18 10:55:24.627 2019-12-18 11:31:24.91
A541 2019-12-18 15:33:24.340 2019-12-18 16:16:05.407
20 дек 19, 17:04    [22045432]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
собственно, вот с группировкой:

+
declare @t table(
	 ID int PRIMARY KEY CLUSTERED,
     Element varchar(100)
	,DateBegin datetime
	,DateEnd datetime
);

insert into @t (Element, DateBegin, DateEnd, ID) VALUES 
('A541', '2019-12-18 10:55:24.627', '2019-12-18 11:14:44.710',	1 ),
('A541', '2019-12-18 10:56:44.623', '2019-12-18 11:15:25.733',	2 ),
('A541', '2019-12-18 10:59:25.163', '2019-12-18 11:18:04.990',	3 ),
('A541', '2019-12-18 11:02:05.350', '2019-12-18 11:20:45.003',	4 ),
('A541', '2019-12-18 11:04:44.793', '2019-12-18 11:23:24.670',	5 ),
('A541', '2019-12-18 11:07:24.960', '2019-12-18 11:26:05.327',	6 ),
('A541', '2019-12-18 11:10:04.913', '2019-12-18 11:28:45.030',	7 ),
('A541', '2019-12-18 11:12:45.313', '2019-12-18 11:31:24.917',	8 ),

('A541', '2019-12-18 15:33:24.340', '2019-12-18 15:52:04.523',	9 ),
('A541', '2019-12-18 15:36:44.180', '2019-12-18 15:55:24.620',	10),
('A541', '2019-12-18 15:40:04.193', '2019-12-18 15:59:24.637',	11),
('A541', '2019-12-18 15:42:44.327', '2019-12-18 16:02:05.063',	12),
('A541', '2019-12-18 15:54:04.840', '2019-12-18 16:12:05.450',	13),
('A541', '2019-12-18 15:56:44.480', '2019-12-18 16:14:44.850',	14),
('A541', '2019-12-18 15:58:04.407', '2019-12-18 16:16:05.407',	15),


 ('A', '2019-12-12 05:00:00', '2019-12-12 05:05:00', 21),
 ('A', '2019-12-12 05:02:00', '2019-12-12 05:07:00', 22),
 ('A', '2019-12-12 05:03:00', '2019-12-12 05:09:00', 23),
 ('A', '2019-12-12 05:11:00', '2019-12-12 05:15:00', 24)
 ,('B', '2019-12-12 05:10:00', '2019-12-12 05:18:00', 31)
 ,('B', '2019-12-12 05:17:00', '2019-12-12 05:19:00', 32)
 ,('B', '2019-12-12 05:27:00', '2019-12-12 05:29:00', 33)
 ,('C', '2019-12-12 05:00:00', '2019-12-12 05:05:00', 34)
;


with a as
(
 SELECT t.Element,
  t.DateBegin, row_number() over (PARTITION BY t.Element ORDER by t.DateBegin) as rn
 from
  @t t
 where
  not exists(select 1 from @t where Element = t.Element AND DateBegin < t.DateBegin and DateBegin <= t.DateEnd and DateEnd >= t.DateBegin)
),
b as
(
 SELECT t.Element,
  t.DateEnd, row_number() over (PARTITION BY t.Element ORDER by t.DateEnd) as rn
 from
  @t t
 where
  not exists(select 1 from @t where Element = t.Element AND DateEnd > t.DateEnd and DateBegin <= t.DateEnd and DateEnd >= t.DateBegin)
)
SELECT a.Element,
 a.DateBegin, b.DateEnd
from
 a join
 b on b.rn = a.rn AND b.Element = a.Element;
20 дек 19, 17:09    [22045437]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
Remind
Member

Откуда: UK
Сообщений: 523
Kast2K,

Как минимум решение nullin'a дает правильный ответ (мое тоже, но у вас версия сервера не подходящая)

upd. А, у него тоже lag используется, не заметил сразу.

Сообщение было отредактировано: 20 дек 19, 17:12
20 дек 19, 17:09    [22045438]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
nullin
Member

Откуда: pullin
Сообщений: 140
Remind, да посмотрел - у меня все ок в последнем примере, но у меня там еще order by в окне, в 2008 такого не было - автор конечно вовремя вспомнил)
Но все эти lag/lead и прочие нарастающие итоги, имея код на руках, легко отображаются в соответствующие cross/outer apply c top(1) или с агрегацией.

Сообщение было отредактировано: 20 дек 19, 17:20
20 дек 19, 17:19    [22045454]     Ответить | Цитировать Сообщить модератору
 Re: Нахождение мин. и макс. дат вхождения в диапазон  [new]
Kast2K
Member

Откуда: Санкт-Петербург
Сообщений: 499
Коллеги,

Я хочу всем участвующим огромное спасибо за помощь и примеры реализации.

Я понял в чём была моя изначальная ошибка: Скрипт предложенный Minamoto я тестировал на кривых тестовых данных и он не отрабатывал нормально + логика понимания была некорректной (образно, надо 7 раз нарисовать и 1 раз написать :) )

Также, благодаря вам я понял как правильно обрабатывать такие массивы данных и как поменять свою логику работы.

Ещё раз всем спасибо!
20 дек 19, 17:35    [22045477]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить