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

Откуда: ***
Сообщений: 474
Доброго времени суток

Имеется две таблички
tab1
НачалоОкончание
01.01.201131.01.2011


tab2
НачалоОкончание
15.01.201114.02.2011


В этих табличках хранятся интервалы времени. Как из двух таблиц получить третью?:
НачалоОкончание
01.01.2011 14.01.2011
15.01.2011 31.01.2011
01.02.2011 14.02.2011


Т.е. если два диапазона пересекаются, то создать таблицу с тремя диапазонами:
Диапазоны дат
не пересекающийся диапазон tab1
пересечение диапазонов tab1 и tab2
не пересекающийся диапазон tab2


в каждой из таблиц tab1 и tab2 может содержаться по нескольку записей.
6 июн 11, 22:37    [10773526]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов дат  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3757
проще всего через юнион....
7 июн 11, 00:41    [10773952]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов дат  [new]
andMegaM
Member

Откуда: ***
Сообщений: 474
Что-то вообще не представляю как в данной ситуации поможет юнион.
Можно по-подробнее?
7 июн 11, 07:55    [10774282]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов дат  [new]
Владимир СА
Member

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

Что-то в этом роде:
declare @tab1 table (d_beg date, d_end date)
insert into @tab1 (d_beg, d_end) values ('20110101','20110131')
select * from @tab1

declare @tab2 table (d_beg date, d_end date)
insert into @tab2 (d_beg, d_end) values ('20110115','20110214')
select * from @tab2

select distinct t.d_date from
(
	select d_beg as d_date from @tab1
	union all
	select d_end as d_date from @tab1
	union all
	select d_beg as d_date from @tab2
	union all
	select d_end as d_date from @tab2
) t
order by t.d_date;


declare @tab3 table (d_beg date, d_end date)

declare @d_date date, @d_beg date, @d_end date;

declare cur_date cursor local for
select distinct t.d_date from
(
	select d_beg as d_date from @tab1
	union all
	select d_end as d_date from @tab1
	union all
	select d_beg as d_date from @tab2
	union all
	select d_end as d_date from @tab2
) t
order by t.d_date;

open cur_date;
fetch next from cur_date into @d_date;

while @@fetch_status = 0
begin
	set @d_beg = @d_date;
	fetch next from cur_date into @d_date;
	set @d_end = DATEADD(day, -1, @d_date);
	insert into @tab3 (d_beg, d_end) values (@d_beg,@d_end);
end;
close cur_date;
deallocate cur_date;

delete from @tab3 where d_beg = @d_date;
update @tab3 set d_end = @d_date where d_end = DATEADD(day, -1, @d_date);

select * from @tab3 order by d_beg
7 июн 11, 08:44    [10774356]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов дат  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3757
можно и без курсора
7 июн 11, 09:58    [10774658]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов дат  [new]
andMegaM
Member

Откуда: ***
Сообщений: 474
2 Ivan Durak

А можно вариант без курсора?
7 июн 11, 10:48    [10774958]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов дат  [new]
mike909
Member

Откуда:
Сообщений: 662
andMegaM
2 Ivan Durak

А можно вариант без курсора?


Решение в лоб с всеобщим объединением диапазонов и заполнением дырок:
declare @t1 table ([START] date, [END] date)
insert into @t1 ([START], [END]) values 
('20110110','20110120'),
('20110115','20110125'),
('20110117','20110220'),
('20110301','20110315')

declare @t2 table ([START] date, [END] date) 
insert into @t2 ([START], [END]) values 
('20110101','20110130'),
('20110310','20110320')

;with tb1( RN, [START], [END] ) as (
  SELECT ROW_NUMBER() OVER(ORDER BY [START] ) as RN, [START], [END]
  FROM @t1
)
,ts1( RN, [START], [END] ) as (
  SELECT ROW_NUMBER() OVER(ORDER BY [START]), t.[START], t.[END]
  FROM (
    SELECT MIN(t1.[START]) as [START], ISNULL(t2.[END], t1.[END]) as [END]
    FROM tb1 as t1
    OUTER APPLY(
      SELECT MAX( t2.[END] ) as [END]
      FROM tb1 as t2
      WHERE t1.RN < t2.RN
      and
        /*
            not	((t1.[END] < t2.[START]) or (t2.[END] < t1.[START]))
          and 
            not ((t1.[END] < t2.[START]) or (t2.[END] < t1.[START]))
        */
        --  После смены базиса 'OR' на 'AND'
          ((t2.[START] <= t1.[END]) AND (t1.[START] <= t2.[END]))
		  and
          ((t2.[START] <= t1.[END]) AND (t1.[START] <= t2.[END]))
      ) as t2
    GROUP BY ISNULL(t2.[END], t1.[END])
  ) as t
)
,tb2( RN, [START], [END] ) as (
  SELECT ROW_NUMBER() OVER(ORDER BY [START] ) as RN, [START], [END]
  FROM @t2
)
,ts2( RN, [START], [END] ) as (
  SELECT ROW_NUMBER() OVER(ORDER BY [START]), t.[START], t.[END]
  FROM (
    SELECT MIN(t1.[START]) as [START], ISNULL(t2.[END], t1.[END]) as [END]
    FROM tb2 as t1
    OUTER APPLY(
      SELECT MAX( t2.[END] ) as [END]
      FROM tb2 as t2
      WHERE t1.RN < t2.RN
        and
            ((t2.[START] <= t1.[END]) AND (t1.[START] <= t2.[END]))
        and
            ((t2.[START] <= t1.[END]) AND (t1.[START] <= t2.[END]))
    ) as t2
    GROUP BY ISNULL(t2.[END], t1.[END])
  ) as t
)
,ts( RN, [START], [END] ) as (
  SELECT ROW_NUMBER() OVER(ORDER BY [START]), t.[START], t.[END]
  FROM (
    SELECT [START], [END]
    FROM ts1
		
    UNION ALL 
		
    SELECT [START], [END]
    FROM ts2
  ) as t
)
,tss( RN, [START], [END] ) as (
  SELECT ROW_NUMBER() OVER(ORDER BY [START]), t.[START], t.[END]
  FROM (
    SELECT MIN(t1.[START]) as [START], ISNULL(t2.[END], t1.[END]) as [END]
    FROM ts as t1
    OUTER APPLY(
      SELECT MAX( t2.[END] ) as [END]
      FROM ts as t2
      WHERE t1.RN < t2.RN
        and
            ((t2.[START] <= t1.[END]) AND (t1.[START] <= t2.[END]))
        and 
            ((t2.[START] <= t1.[END]) AND (t1.[START] <= t2.[END]))
    ) as t2
    GROUP BY ISNULL(t2.[END], t1.[END])
  ) as t
)

select [START], [END], 1 as [isPresent]
from tss
union all
select t1.[END], t2.[START], 0 as [isPresent]
from tss as t1
inner join tss as t2 on
  t1.RN = t2.RN-1
order by [START]
Может это и не то, что Вы хотели, но мне было интересно решить задачу в такой постановке.
7 июн 11, 12:06    [10775717]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов дат  [new]
aleks2
Guest
andMegaM
Доброго времени суток

Диапазоны дат
не пересекающийся диапазон tab1
пересечение диапазонов tab1 и tab2
не пересекающийся диапазон tab2


в каждой из таблиц tab1 и tab2 может содержаться по нескольку записей.


Сколько диапазонов может ОДНОВРЕМЕННО участвовать в пересечении?
7 июн 11, 12:49    [10776099]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов дат  [new]
Добрый Э - Эх
Guest
Делать в лоб - рекурсивным СТЕ генерировать полный список дней заданных в таблицах интервалов, после чего соединять полученные полные наборы дат и группировать.
В этом случае можно будет подсчитать кол-во пересекающихся интервалов, выбрать не пересекающиеся даты и сделать всё что душе угодно.
7 июн 11, 12:57    [10776162]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов дат  [new]
aleks2
Guest
Добрый Э - Эх
Делать в лоб - рекурсивным СТЕ генерировать полный список дней заданных в таблицах интервалов, после чего соединять полученные полные наборы дат и группировать.
В этом случае можно будет подсчитать кол-во пересекающихся интервалов, выбрать не пересекающиеся даты и сделать всё что душе угодно.


А главное, успеть попить чайку и выспаться.
7 июн 11, 13:04    [10776219]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов дат  [new]
Добрый Э - Эх
Guest
aleks2
А главное, успеть попить чайку и выспаться.
Не думаю, что у автора столько данных наберется... Ну если только целью его не является вычисление эпох развития нашей вселенной и даты не лежат в диапазоне от "сотен миллиардов лет до нас" и по "текущее время"...
7 июн 11, 13:12    [10776283]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов дат  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
Только в качестве идеи для дальнейшего развития:
declare @t1 table (d date, e date)
declare @t2 table (d date, e date)

insert into @t1
values
 ('20110101', '20110131'),
 ('20110102', '20110130'),
 ('20110106', '20110110')

insert into @t2
values
 ('20110115', '20110212'),
 ('20110305', '20110422')

;with u as
(
 select d, e from @t1
 union
 select d, e from @t2
),
t as
(
 select
  b.*, max(b.d) over () as md
 from
  u a cross apply
  (select a.d union all select a.e) b
)
select
 t1.d, t2.e
from
 t t1 cross apply
 (select top (1) case when d = t1.md then d else dateadd(day, -1, d) end as e from t where d > t1.d order by d) t2
order by
 t1.d
7 июн 11, 13:53    [10776594]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов дат  [new]
mike909
Member

Откуда:
Сообщений: 662
invm
Только в качестве идеи для дальнейшего развития:
;with u as
(
 select d, e from @t1
 union
 select d, e from @t2
),

Точно, так на много короче ...
+
;with ts( RN, [START], [END] ) as (
  SELECT ROW_NUMBER() OVER(ORDER BY [START] ) as RN, t.*
  FROM (
    SELECT [START], [END]
    FROM @t1
    
    union all

    SELECT [START], [END]
    FROM @t2
  ) as t
)
,tss( RN, [START], [END] ) as (
  SELECT ROW_NUMBER() OVER(ORDER BY [START]), t.[START], t.[END]
  FROM (
    SELECT MIN(t1.[START]) as [START], ISNULL(t2.[END], t1.[END]) as [END]
    FROM ts as t1
    OUTER APPLY(
      SELECT MAX( t2.[END] ) as [END]
      FROM ts as t2
      WHERE t1.RN < t2.RN
      and
          ((t2.[START] <= t1.[END]) AND (t1.[START] <= t2.[END]))
		  and
          ((t2.[START] <= t1.[END]) AND (t1.[START] <= t2.[END]))
      ) as t2
    GROUP BY ISNULL(t2.[END], t1.[END])
  ) as t
)

select [START], [END], 1 as [isPresent]
from tss
union all
select t1.[END], t2.[START], 0 as [isPresent]
from tss as t1
inner join tss as t2 on
  t1.RN = t2.RN-1
order by [START]
7 июн 11, 14:06    [10776712]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов дат  [new]
andMegaM
Member

Откуда: ***
Сообщений: 474
aleks2
andMegaM
Доброго времени суток

Диапазоны дат
не пересекающийся диапазон tab1
пересечение диапазонов tab1 и tab2
не пересекающийся диапазон tab2


в каждой из таблиц tab1 и tab2 может содержаться по нескольку записей.


Сколько диапазонов может ОДНОВРЕМЕННО участвовать в пересечении?


в таблице tab2 всегда три диапазона
в таблице tab1 от одного до пяти
7 июн 11, 14:09    [10776738]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов дат  [new]
andMegaM
Member

Откуда: ***
Сообщений: 474
Извиняюсь, что не сообщил сразу, но дата окончания последней записи в каждой таблице = NULL. Т.е. данная запись актуальна на данный момент.
Сделал следующим образом. В третью таблицу в поле "начало" записал даты начала всех диапазонов из двух таблиц. Т.е.
tab1
date_begin date_end
03.04.2011 30.04.2011
01.05.2011 31.05.2011
01.06.2011 [NULL]


tab2
date_begin date_end
10.03.2011 28.04.2011
29.04.2011 [NULL]


#temp
t_id date_begin date_end
1 10.03.2011 02.04.2011
2 03.04.2011 28.04.2011
3 29.04.2011 30.04.2011
4 01.05.2011 31.05.2011
5 01.06.2011 [NULL]


UPDATE #temp
SET date_end = (SELECT DATEADD(day, -1, date_begin) FROM #temp tt WHERE  #tur_temp.t_id+1 =tt.t_id)
7 июн 11, 14:24    [10776864]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов дат  [new]
Anddros
Member

Откуда:
Сообщений: 1077
Еще пара вариантов:

declare @t1 table (d_beg datetime, d_end datetime null)
insert @t1 select '20110403','20110430'
union all select '20110501','20110531'
union all select '20110601',null

declare @t2 table (d_beg datetime, d_end datetime null)
insert @t2 select '20110310','20110402'
union all select '20110403','20110428'
union all select '20110429','20110430'
union all select '20110501','20110531'
union all select '20110601',null

select min(case when n=1 then rn end)rn ,max(case when n=1 then d end) d_beg, max(case when n=0 then d-1 end) d_end
from (
select d, row_number()over(order by d)rn
from (select d_beg d from @t1 union select d_beg from @t2 union select d_end+1 from @t1 union select d_end+1 from @t2)t
where d is not null
)t
cross join (select 0n union all select 1)n
group by rn+n
having max(case when n=1 then d end) is not null

select number+1 n, convert(datetime,substring(d,number*10+1,10),104) d_beg,
convert(datetime,nullif(substring(d,number*10+11,10),''),104)-1 d_end
from (select (select convert(char(10),d,104)+''
from (select d_beg d from @t1 union select d_beg from @t2 union select d_end+1 from @t1 union select d_end+1 from @t2)t
order by d
for xml path('')) d
)t
cross join master..spt_values v 
where type='P' and substring(d,number*10+1,1) >''
7 июн 11, 15:01    [10777121]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов дат  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3757
andMegaM
2 Ivan Durak

А можно вариант без курсора?

дык смотрю сам справился уже
8 июн 11, 00:12    [10779775]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить