Пересечение отрезков времени

доброго времени всем

задача на вычисление кол-ва дней по периодам подключения

-- объекты

create table obj(
	id integer not null
	, name character varying(255)
	, description character varying(255)
	-- ...
	, primary key(id)
);

insert into obj(id, name) values(1, 'собачка Жучка');
insert into obj(id, name) values(2, 'кошка Мурка');
insert into obj(id, name) values(3, 'мышка Тихоня');
insert into obj(id, name) values(4, 'тетя Ася');

-- свойства
create table prop(
	id integer not null
	, name character varying(255)
	, description character varying(255)
	, primary key(id)
);

insert into prop(id, name) values(1, 'находится в Сочи');
insert into prop(id, name) values(2, 'дела - неважно');
insert into prop(id, name) values(3, 'на декрете');
insert into prop(id, name) values(4, 'живет припеваюче');
insert into prop(id, name) values(5, 'орет на всех');
insert into prop(id, name) values(6, 'будни');

-- отрезки включения-выключения свойств
-- !!! несмотря на то, что указывается год свойства задаются на все года (то есть периодически повторяются каждый год
-- в пределах указанного отрезка времени)
create table obj_prop_interim(
	id integer not null
	, obj_id integer
	, prop_id integer
	, start_d date
	, end_d date
);

insert into obj_prop_interim(id, obj_id, prop_id, start_d, end_d) values(1, 1, 1, '2008-01-01', '2008-12-31');
insert into obj_prop_interim(id, obj_id, prop_id, start_d, end_d) values(2, 1, 4, '2008-06-01', '2008-08-31');
insert into obj_prop_interim(id, obj_id, prop_id, start_d, end_d) values(3, 1, 6, '2008-02-01', '2008-03-01');
insert into obj_prop_interim(id, obj_id, prop_id, start_d, end_d) values(4, 1, 6, '2008-07-01', '2008-09-30');

insert into obj_prop_interim(id, obj_id, prop_id, start_d, end_d) values(5, 2, 3, '2008-08-01', '2008-10-31');
insert into obj_prop_interim(id, obj_id, prop_id, start_d, end_d) values(6, 2, 6, '2008-08-01', '2008-08-31');

insert into obj_prop_interim(id, obj_id, prop_id, start_d, end_d) values(7, 3, 6, '2008-01-01', '2008-12-31');

insert into obj_prop_interim(id, obj_id, prop_id, start_d, end_d) values(8, 4, 1, '2008-01-01', '2008-12-31');
insert into obj_prop_interim(id, obj_id, prop_id, start_d, end_d) values(9, 4, 5, '2008-01-01', '2008-12-31');
insert into obj_prop_interim(id, obj_id, prop_id, start_d, end_d) values(10, 4, 2, '2008-01-01', '2008-12-31');

-- постановка задачи
-- дается отрезок времени iFrom:date .. iTo:date
-- нужно вычислить для каждого объекта кол-во пребывания в том или ином качестве(свойстве) за данный отрезок

-- допустим дано iFrom .. iTo = '2008-01-01' .. '2009-10-31'
-- тогда для жучки имеем

объект состояние кол-во собачка Жучка находится в Сочи 304 собачка Жучка живет припеваюче 182 собачка Жучка будни 239

сначала решил в лоб средствами с++ - пересечение множеств (это конечно не подходит в силу статичности)
потом перевел в udf (для каждого объекта вызвается count_days(obj_id)) для большого кол-ва записей работает очень долго (База не важна нужно общее решение) (сам делал на postgres)
буду благодарен за любые идеи

Создать опорную таблицу дат.
джойнить с ней на включение даты в период и считать count'ы.

-------------------------
There’s no silver bullet!

отлично работает
спасибо!)

sherzod_,

Вот так тоже можно (на мой взгляд, это менее "в лоб"):

set nocount on
declare @iFrom datetime
   , @iTo datetime
   , @iTek datetime
   , @iTekK datetime

select @iFrom = '2008-01-01'
   , @iTo = '2009-10-31'
if datediff(day, @iFrom, @iTo) < 0 begin
   print 'Ошибка: дата начала больше даты окончания'
end
else begin

   create table #YEAR_PERIOD (	-- для хранения периодов внутри календарного года
      iFrom datetime
      , iTo datetime
   )

   select @iTek = @iFrom
   while (@iTek <= @iTo) begin
      select @iTekK = convert(datetime, replace(str(datepart(year, @iTek), 4, 0)
                                                , ' ', '0') + '1231'
                              )
      if @iTekK > @iTo select @iTekK = @iTo

      insert into #YEAR_PERIOD
         values(@iTek, @iTekK)

      select @iTek = dateadd(day, 1, @iTekK)
   end

/* так не годится - год мешает
   select *
   from #YEAR_PERIOD YP (nolock)
      , obj_prop_interim OPI (nolock)
   where (OPI.start_d between YP.iFrom and YP.iTo
      or OPI.end_d between YP.iFrom and YP.iTo
      or YP.iFrom between OPI.start_d and OPI.end_d
      or YP.iTo between OPI.start_d and OPI.end_d
          )
*/

   select
      [объект] = (select left(name, 14)
                  from obj (nolock)
                  where id = OPI.obj_id
                  )
      , [состояние] = (select left(name, 17)
                       from prop (nolock)
                       where id = OPI.prop_id
                       )
      , [кол-во] = 
        sum(datediff(day, case /* Начало пересечения */
                             when substring(convert(varchar(8), OPI.start_d, 112), 5, 4) < substring(convert(varchar(8), YP.iFrom, 112), 5, 4)
                                then YP.iFrom
                             else convert(datetime, substring(convert(varchar(8), YP.iFrom, 112), 1, 4) 
                                                  + substring(convert(varchar(8), OPI.start_d, 112), 5, 4)
                                          )
                          end
                        , case /* Окончание пересечения */
                             when substring(convert(varchar(8), OPI.end_d, 112), 5, 4) >= substring(convert(varchar(8), YP.iTo, 112), 5, 4)
                                then YP.iTo
                             else convert(datetime, substring(convert(varchar(8), YP.iTo, 112), 1, 4) 
                                                  + substring(convert(varchar(8), OPI.end_d, 112), 5, 4)
                                          )
                          end 
                     ) + 1
            )

   from #YEAR_PERIOD YP (nolock)
      , obj_prop_interim OPI (nolock)
   where (substring(convert(varchar(8), OPI.start_d, 112), 5, 4) between substring(convert(varchar(8), YP.iFrom, 112), 5, 4) and substring(convert(varchar(8), YP.iTo, 112), 5, 4)
      or substring(convert(varchar(8), OPI.end_d, 112), 5, 4) between substring(convert(varchar(8), YP.iFrom, 112), 5, 4) and substring(convert(varchar(8), YP.iTo, 112), 5, 4)
      or substring(convert(varchar(8), YP.iFrom, 112), 5, 4) between substring(convert(varchar(8), OPI.start_d, 112), 5, 4) and substring(convert(varchar(8), OPI.end_d, 112), 5, 4)
      or substring(convert(varchar(8), YP.iTo, 112), 5, 4) between substring(convert(varchar(8), OPI.start_d, 112), 5, 4) and substring(convert(varchar(8), OPI.end_d, 112), 5, 4)
          )
   group by OPI.obj_id
      , OPI.prop_id
   order by OPI.obj_id
      , OPI.prop_id

   drop table #YEAR_PERIOD
end

А получается вот что:

объект         состояние         кол-во      
-------------- ----------------- ----------- 
собачка Жучка  находится в Сочи  670
собачка Жучка  живет припеваюче  184
собачка Жучка  будни             243
кошка Мурка    на декрете        184
кошка Мурка    будни             62
мышка Тихоня   будни             670
тетя Ася       находится в Сочи  670
тетя Ася       дела - неважно    670
тетя Ася       орет на всех      670