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

Откуда:
Сообщений: 17
Добрый день. Возникла такая задача:

Есть две таблицы

T1 - Таблица общего стажа по сотрудникам
Таб№НачалоОкончание
100001.01.201131.05.2011
200001.02.201131.03.2011


T2 - Таблица неявок сотрудников в пределах периода из таблицы Т1
Таб№НачалоОкончание
100015.01.201125.01.2011
100010.03.201115.03.2011
200010.02.201112.02.2011
200003.03.201115.03.2011


Необходимо из этих двух таблиц сделать третью в котором разбить общий период стажа из Т1 с учетом неявок из Т2
без пробелов в периодах.
Т.е. должно быть так:

Таб№НачалоОкончание
100001.01.201114.01.2011
100015.01.201125.01.2011
100026.01.201109.03.2011
100010.03.201115.03.2011
100016.03.201131.05.2011
200001.02.201109.02.2011
200010.02.201112.02.2011
200013.02.201102.03.2011
200003.03.201115.03.2011
200016.03.201131.03.2011


(Microsoft SQL Server 2005)
У кого какие идеи есть на этот счет?
Заранее благодарю за любую помощь в обсуждении.
8 июл 11, 12:28    [10944649]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение периодов разложить в цепочку  [new]
yado4eg
Member

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

;with interval as (select nt.id, dateadd(day, 1, nt.ed) as beg,
       dateadd(day, -1, pr.beg) as ed
from
  (select n.Id, n.beg, n.ed,
         row_number() over(partition by n.id order by n.beg) r
   from #ne n
   ) pr
   join (
          select n.Id, n.beg, n.ed,
                 row_number() over (partition by n.id order by n.beg) r
          from #ne n
        ) nt on pr.r = nt.r+1 and pr.id = nt.id )
        

select * from (
select s.id, s.beg, (select top 1 dateadd(day,-1,n.beg) from #ne n where n.id = s.id order by n.beg) ed
from #stazh s
union all
select s.id, (select top 1 dateadd(day,1,n.ed) from #ne n where n.id = s.id order by n.beg desc),
       s.ed
from #stazh s
union all
select n.id, n.beg, n.ed
from #ne n
union all
select i.id, i.beg, i.ed
from interval i ) t
order by t.id, t.beg

8 июл 11, 15:13    [10946223]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение периодов разложить в цепочку  [new]
_ч_
Member

Откуда:
Сообщений: 1446
declare @t1 table(tabnum int, dbegin datetime, dend datetime)
insert into @t1(tabnum, dbegin, dend)
select 1000, '20110101', '20110531'
union
select 2000, '20110201', '20110331'



declare @t2 table(tabnum int, dbegin datetime, dend datetime)
insert into @t2(tabnum, dbegin, dend)
select 1000, '20110115', '20110125'
union
select 1000, '20110310', '20110315'
union
select 2000, '20110210', '20110212'
union
select 2000, '20110303', '20110315'



select * from(
select t1.tabnum, t1.dbegin, dateadd(day, -1, t2.dbegin) as dend from @t1 t1
inner join (select min(dbegin) dbegin, tabnum from @t2 group by tabnum)t2 on t2.tabnum = t1.tabnum
union
select t1.tabnum, dateadd(day, 1, t2.dend) as dbegin, t1.dend from @t1 t1
inner join (select max(dend) dend, tabnum from @t2 group by tabnum)t2 on t2.tabnum = t1.tabnum
union
select * from @t2
union
select T.tabnum, dateadd(day, 1, T.dend), dateadd(day, -1, T2.dbegin) from (select row_number() over (partition by tabnum order by dbegin) as num, * from @t2) T2
inner join (select row_number() over (partition by tabnum order by dbegin) as num, * from @t2)T on T.tabnum = T2.tabnum and T.num+1 = T2.num)
Result
order by tabnum, dbegin


8 июл 11, 15:47    [10946540]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение периодов разложить в цепочку  [new]
aleks2
Guest
declare @t1 table(tabnum int, dbegin datetime, dend datetime)
insert into @t1(tabnum, dbegin, dend)
select 1000, '20110101', '20110531'
union
select 2000, '20110201', '20110331'



declare @t2 table(tabnum int, dbegin datetime, dend datetime)
insert into @t2(tabnum, dbegin, dend)
select 1000, '20110115', '20110125'
union
select 1000, '20110310', '20110315'
union
select 2000, '20110210', '20110212'
union
select 2000, '20110303', '20110315'



;with
  begs as (select tabnum, dbegin, ROW_NUMBER() over(partition by tabnum ORDER BY dbegin) N FROM 
			(
				select tabnum, dbegin FROM @t1 
				UNION ALL 
				select tabnum, dbegin FROM @t2
				UNION ALL 
				select tabnum, dend FROM @t1 
				UNION ALL 
				select tabnum, dend FROM @t2
			) X
  )
select b.tabnum, b.dbegin, e.dbegin dend FROM begs b inner join begs e on b.tabnum=e.tabnum AND b.N+1=e.N
ORDER BY b.tabnum, b.dbegin
8 июл 11, 18:29    [10947896]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение периодов разложить в цепочку  [new]
bdv007
Member

Откуда:
Сообщений: 17
Спасибо, Всем за предложенные варианты.
Последние два особенно понравились лаконичностью.
11 июл 11, 09:47    [10953890]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение периодов разложить в цепочку  [new]
smls
Guest
bdv007,

Для 2000 :

create table #t (i int,d1 datetime,d2 datetime)

create table #t2 (j int,d1 datetime,d2 datetime)

insert into #t values (1,'20110101','20110531')
insert into #t values (2,'20110201','20110331')

insert into #t2 values (1,'20110115','20110125')
insert into #t2 values (1,'20110310','20110315')
insert into #t2 values (2,'20110210','20110212')
insert into #t2 values (2,'20110303','20110315')


select hh.k,case when f2.d2 is null and f.d1 is null then
hh.d else case when f4.d1 is null then hh.d+1 else hh.d end end d1,
case when f.d1 is null then hh.d3 else hh.d3-1 end d2 from
(select qq.k,qq.d, min(zz.d) d3 from
(select i k,d1 d from #t
union
select i k,d2 d from #t
union
select j k,d1 d from #t2
union
select j k,d2 d from #t2
) qq inner join (select i k,d1 d from #t
union
select i k,d2 d from #t
union
select j k,d1 d from #t2
union
select j k,d2 d from #t2) zz on qq.k=zz.k and qq.d<zz.d
group by qq.k,qq.d) hh left join #t2 f on hh.k=f.j and hh.d3=f.d1
left join #t f2 on hh.k=f2.i and hh.d3=f2.d2
left join #t f4 on hh.k=f4.i and hh.d=f4.d1
11 июл 11, 15:45    [10956421]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить