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

Откуда:
Сообщений: 11
Всем привет.
Такой вопрос. Есть запись в таблице с полями "Дата_Начало" и "Дата_Окончание"

Дата_Начало Дата_Окончание
01.01.201101.05.2012


Требуется разбить на две записи по периодам
Дата_Начало Дата_Окончание
01.01.201131.12.2011
01.01.201201.05.2012
17 май 13, 21:32    [14314237]     Ответить | Цитировать Сообщить модератору
 Re: Разбить запись на периоды  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
declare @years table (ys date, ye date);

insert into @years
values
 ('20100101', '20101231'), ('20110101', '20111231'), ('20120101', '20121231'), ('20130101', '20131231');
 
declare @t table (ps date, pe date);

insert into @t
values
 ('20110101', '20120501');

select
 s.ps, e.pe
from
 @t t join
 @years y on y.ys <= t.pe and y.ye >= t.ps cross apply
 (select max(d) from (values(t.ps), (y.ys)) t(d)) s(ps) cross apply
 (select min(d) from (values(t.pe), (y.ye)) t(d)) e(pe);
17 май 13, 23:13    [14314687]     Ответить | Цитировать Сообщить модератору
 Re: Разбить запись на периоды  [new]
V.Zarubin
Member

Откуда:
Сообщений: 11
Спасибо за пример, но...

В примере работа ведется со статистическими данными. В реале дело обстоит следующим образом.
Необходимо из таблицы считать данные проанализировать поля "Дата_Начало" и "Дата_Окончание", если период нет укладывается в один год то необходимо произвести разбивку. Период может растягиваться от 2-х лет и более....
Т.е. в БД запись одна, а пользователь должен увидеть отдельную запись по каждому году.
18 май 13, 22:10    [14317465]     Ответить | Цитировать Сообщить модератору
 Re: Разбить запись на периоды  [new]
qwerty112
Guest
V.Zarubin
Спасибо за пример, но...

В примере работа ведется со статистическими данными. В реале дело обстоит следующим образом.
Необходимо из таблицы считать данные проанализировать поля "Дата_Начало" и "Дата_Окончание", если период нет укладывается в один год то необходимо произвести разбивку. Период может растягиваться от 2-х лет и более....
Т.е. в БД запись одна, а пользователь должен увидеть отдельную запись по каждому году.

и что, "по вашему", в этой версии - изменилось в ТЗ по сравнению со стартовым постом ?
18 май 13, 22:22    [14317498]     Ответить | Цитировать Сообщить модератору
 Re: Разбить запись на периоды  [new]
V.Zarubin
Member

Откуда:
Сообщений: 11
qwerty112
и что, "по вашему", в этой версии - изменилось в ТЗ по сравнению со стартовым постом ?


Вы конечно гуру в данном вопросе, по сравнению со мной...
а можно рабочий пример, а не сданными типа

insert into @years
values

('20100101', '20101231'), ('20110101', '20111231'), ('20120101', '20121231'), ('20130101', '20131231');

где все разбито ручками....
18 май 13, 22:33    [14317561]     Ответить | Цитировать Сообщить модератору
 Re: Разбить запись на периоды  [new]
qwerty112
Guest
V.Zarubin
qwerty112
и что, "по вашему", в этой версии - изменилось в ТЗ по сравнению со стартовым постом ?


Вы конечно гуру в данном вопросе, по сравнению со мной...
а можно рабочий пример, а не сданными типа

insert into @years
values

('20100101', '20101231'), ('20110101', '20111231'), ('20120101', '20121231'), ('20130101', '20131231');

где все разбито ручками....

это не "разбито ручками" - это опорная таблица, которую вам нужно завести в БД и использовать в запросе.
или генерировать её как временную таб. в ХП, где далее и будет выполнятся запрос
18 май 13, 22:50    [14317642]     Ответить | Цитировать Сообщить модератору
 Re: Разбить запись на периоды  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
V.Zarubin,

Что мешает завести в БД один раз служебную таблицу годов и пользоваться ею? Ну и запрос у меня слегка выпендрежный, можно проще:
select
 case when t.ps >= y.ys then t.ps else y.ys end as ps,
 case when t.pe < y.ye then t.pe else y.ye end as pe
from
 @t t join
 @years y on y.ys <= t.pe and y.ye >= t.ps;
18 май 13, 22:52    [14317658]     Ответить | Цитировать Сообщить модератору
 Re: Разбить запись на периоды  [new]
V.Zarubin
Member

Откуда:
Сообщений: 11
Всем спасибо.

Завести опорную таблицу в БД не получится, т.к. структур БД стороннего разработчика и влезать в нее чревато последствиями.
Попробую генерировать временную таблицу перед выполнением запроса.....
18 май 13, 23:07    [14317719]     Ответить | Цитировать Сообщить модератору
 Re: Разбить запись на периоды  [new]
qwerty112
Guest
V.Zarubin
Всем спасибо.

Завести опорную таблицу в БД не получится, т.к. структур БД стороннего разработчика и влезать в нее чревато последствиями.
Попробую генерировать временную таблицу перед выполнением запроса.....

нуу можно ещё эту "опорную таблицу" через cte получать, "по необходимости",
но это однозначно хуже, имхо ...
declare @t table (id int, ps datetime, pe datetime);

insert into @t
values
 (1, '20110101', '20120501');
insert into @t
values
 (2, '20070101', '20150501');

;with 
cte1 as 
(select min(year(ps)) as min_y, max(year(pe)) as max_y from @t),
cte2 as 
(select cast(str(min_y)+'0101' as datetime) as ys, cast(str(min_y)+'1231' as datetime) as ye, min_y, max_y
from cte1
union all
select cast(str(min_y+1)+'0101' as datetime) as ys, cast(str(min_y+1)+'1231' as datetime) as ye, min_y+1, max_y
from cte2
where min_y+1 <= max_y)

select t.id,
 case when t.ps >= y.ys then t.ps else y.ys end as ps,
 case when t.pe < y.ye then t.pe else y.ye end as pe
from
 @t t join
 cte2 y on y.ys <= t.pe and y.ye >= t.ps
order by 1,2

id          ps                      pe
----------- ----------------------- -----------------------
1           2011-01-01 00:00:00.000 2011-12-31 00:00:00.000
1           2012-01-01 00:00:00.000 2012-05-01 00:00:00.000
2           2007-01-01 00:00:00.000 2007-12-31 00:00:00.000
2           2008-01-01 00:00:00.000 2008-12-31 00:00:00.000
2           2009-01-01 00:00:00.000 2009-12-31 00:00:00.000
2           2010-01-01 00:00:00.000 2010-12-31 00:00:00.000
2           2011-01-01 00:00:00.000 2011-12-31 00:00:00.000
2           2012-01-01 00:00:00.000 2012-12-31 00:00:00.000
2           2013-01-01 00:00:00.000 2013-12-31 00:00:00.000
2           2014-01-01 00:00:00.000 2014-12-31 00:00:00.000
2           2015-01-01 00:00:00.000 2015-05-01 00:00:00.000
18 май 13, 23:39    [14317816]     Ответить | Цитировать Сообщить модератору
 Re: Разбить запись на периоды  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
V.Zarubin
Завести опорную таблицу в БД не получится, т.к. структур БД стороннего разработчика и влезать в нее чревато последствиями.
Без опорной таблицы
declare @t table (id int, ps date, pe date);

insert into @t
values
 (1, '20110101', '20140501'),
 (2, '20130501', '20130701');

select
 t.id,
 case when t.ps >= a.ys then t.ps else a.ys end as ps,
 case when t.pe < b.ye then t.pe else b.ye end as pe
from
 @t t cross apply
 (select datediff(year, t.ps, t.pe) + 1) y(c) cross apply
 (select top (y.c) cast(dateadd(year, row_number() over (order by (select 1)) - 1, cast(year(t.ps) as varchar(4))) as date) from master.dbo.spt_values) a(ys) cross apply
 (select dateadd(day, -1, dateadd(year, 1, a.ys))) b(ye)
order by
 t.id, ps;
19 май 13, 00:15    [14317921]     Ответить | Цитировать Сообщить модератору
 Re: Разбить запись на периоды  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
declare @t table (id int, ps date, pe date);

insert into @t
values
 (1, '20110201', '20140501'),
 (2, '20121210', '20130110'),
 (3, '20130501', '20130701');

with buff (id,ps,pe) AS
(select ID, MIN(ps) as ps ,MIN(ps) as pe from @t group by id
union all
select  b.id
,CASE WHEN b.pe = b.ps THEN b.ps ELSE DATEADD(DAY,1,b.pe) END as ps
,CASE WHEN DATEADD(DAY,-1,DATEADD(YEAR,1,CAST(CAST(YEAR(b.ps) as varchar(4)) as date)))< t.pe	
		 THEN DATEADD(DAY,-1,DATEADD(YEAR,1,CAST(CAST(YEAR(b.ps) as varchar(4)) as date))) else t.pe end as pe
from  buff b
join @t t on t.id =b.id and b.ps <= t.pe and  t.ps <= b.pe
and b.pe < t.pe
)
select * from buff where pe>ps
20 май 13, 08:01    [14318962]     Ответить | Цитировать Сообщить модератору
 Re: Разбить запись на периоды  [new]
V.Zarubin
Member

Откуда:
Сообщений: 11
Всем спасибо. Тема закрыта.
23 май 13, 08:09    [14336517]     Ответить | Цитировать Сообщить модератору
 Re: Разбить запись на периоды  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31991
V.Zarubin
Завести опорную таблицу в БД не получится, т.к. структур БД стороннего разработчика и влезать в нее чревато последствиями.
Попробую генерировать временную таблицу перед выполнением запроса.....
Ну да, бывает, приходится вынуждено делать вспомогательные таблицы непосредственно при каждом запросе. Главное, понимать нелепость такого подхода, стараться при возможности его избежать.
23 май 13, 10:37    [14337119]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить