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

Откуда:
Сообщений: 52
Уважаемые форумчане, был бы Вам очень признателен за помощь составлении запроса.

Есть данные:

with cte as (
	select '1111' numb, 'one two' field, CAST('2014/01/01 00:00:00' as datetime) event_date union all
	select '1112' numb, 'one two' field, CAST('2014/01/03 00:00:00' as datetime) event_date union all
	select '1112' numb, 'two' field, CAST('2014/01/05 00:00:00' as datetime) event_date union all
	select '1112' numb, 'one two' field, CAST('2014/01/06 00:00:00' as datetime) event_date union all		
	select '1112' numb, 'two' field, CAST('2014/01/07 00:00:00' as datetime) event_date union all
	select '1112' numb, 'two' field, CAST('2014/01/08 00:00:00' as datetime) event_date union all
	select '1112' numb, 'one two' field, CAST('2014/01/09 00:00:00' as datetime) event_date union all		
	select '1113' numb, 'one two' field, CAST('2014/01/10 00:00:00' as datetime) event_date --union all
)
select * from cte

numbfieldevent_date
1111one two2014-01-01 00:00:00.000
1112one two2014-01-03 00:00:00.000
1112two2014-01-05 00:00:00.000
1112one two2014-01-06 00:00:00.000
1112two2014-01-07 00:00:00.000
1112two2014-01-08 00:00:00.000
1112one two2014-01-09 00:00:00.000
1113one two2014-01-10 00:00:00.000

Результат:
numbfieldresevent_date
1111one two12014-01-01 00:00:00.000
1112one two12014-01-03 00:00:00.000
1112two22014-01-05 00:00:00.000
1112one two12014-01-06 00:00:00.000
1112two22014-01-07 00:00:00.000
1112two32014-01-08 00:00:00.000
1112one two12014-01-09 00:00:00.000
1113one two12014-01-10 00:00:00.000


Т.е. для данных в рамках numb, отсортированных по event_date необходимо добавить нумерацию, где всякий раз при встрече one two счетчик сбрасывается в 1.

P.s. Сервер MS SQL Server 2008 R2
24 апр 14, 10:34    [15927190]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в запросе  [new]
Кавказ-сила
Member

Откуда: Москва
Сообщений: 261
with cte as (
	select '1111' numb, 'one two' field, CAST('2014/01/01 00:00:00' as datetime) event_date union all
	select '1112' numb, 'one two' field, CAST('2014/01/03 00:00:00' as datetime) event_date union all
	select '1112' numb, 'two' field, CAST('2014/01/05 00:00:00' as datetime) event_date union all
	select '1112' numb, 'one two' field, CAST('2014/01/06 00:00:00' as datetime) event_date union all		
	select '1112' numb, 'two' field, CAST('2014/01/07 00:00:00' as datetime) event_date union all
	select '1112' numb, 'two' field, CAST('2014/01/08 00:00:00' as datetime) event_date union all
	select '1112' numb, 'one two' field, CAST('2014/01/09 00:00:00' as datetime) event_date union all		
	select '1113' numb, 'one two' field, CAST('2014/01/10 00:00:00' as datetime) event_date --union all
), cte2 as (
select numb, field, event_date, row_number() over(order by event_date) n
from cte), cte3 as (
select numb, field, 1 res, event_date, n
from cte2 where n = 1
union all
select cte2.numb, cte2.field, case when cte2.field = 'one two' then 1 else cte3.res + 1 end, cte2.event_date, cte2.n
from cte2 join cte3 on cte3.n = cte2.n - 1)
select numb, field, res, event_date from cte3
24 апр 14, 10:44    [15927266]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в запросе  [new]
aleks2
Guest
Канешно, можно напрячь СТЕ. Но нужно ли?

declare @t table (numb varchar(10),  field varchar(10), res int,  event_date datetime, id int identity primary key clustered);

with cte as (
	select '1111' numb, 'one two' field, CAST('2014/01/01 00:00:00' as datetime) event_date union all
	select '1112' numb, 'one two' field, CAST('2014/01/03 00:00:00' as datetime) event_date union all
	select '1112' numb, 'two' field, CAST('2014/01/05 00:00:00' as datetime) event_date union all
	select '1112' numb, 'one two' field, CAST('2014/01/06 00:00:00' as datetime) event_date union all		
	select '1112' numb, 'two' field, CAST('2014/01/07 00:00:00' as datetime) event_date union all
	select '1112' numb, 'two' field, CAST('2014/01/08 00:00:00' as datetime) event_date union all
	select '1112' numb, 'one two' field, CAST('2014/01/09 00:00:00' as datetime) event_date union all		
	select '1113' numb, 'one two' field, CAST('2014/01/10 00:00:00' as datetime) event_date --union all
)
insert @t(numb, field, event_date, res ) select *, case field when 'one two' then 1 end from cte order by numb, event_date ;

while exists(select * from @t where res is null)
  update t set res = tt.res+1 from ( select * from @t where res is null) t inner join (select * from @t where res is not null) tt on tt.id = t.id-1 and t.numb = tt.numb 
   
select * from @t   
   
24 апр 14, 10:51    [15927321]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в запросе  [new]
Dmitry E. Loginov
Member

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

при условии уникальности numb, event_date. в лоб:
with cte as (
	select '1111' numb, 'one two' field, CAST('2014/01/01 00:00:00' as datetime) event_date union all
	select '1112' numb, 'one two' field, CAST('2014/01/03 00:00:00' as datetime) event_date union all
	select '1112' numb, 'two' field, CAST('2014/01/05 00:00:00' as datetime) event_date union all
	select '1112' numb, 'one two' field, CAST('2014/01/06 00:00:00' as datetime) event_date union all		
	select '1112' numb, 'two' field, CAST('2014/01/07 00:00:00' as datetime) event_date union all
	select '1112' numb, 'two' field, CAST('2014/01/08 00:00:00' as datetime) event_date union all
	select '1112' numb, 'one two' field, CAST('2014/01/09 00:00:00' as datetime) event_date union all		
	select '1113' numb, 'one two' field, CAST('2014/01/10 00:00:00' as datetime) event_date --union all
)
, t1 as (select numb, field, event_date, ROW_NUMBER() over (order by numb, event_date) rn from cte
where field='one two')
, t2 as ( select a.numb, a.event_date bd, b.event_date ed, a.rn from t1 a left join t1 b on a.numb=b.numb and b.rn=a.rn+1
)
select cte.*, ROW_NUMBER() over (partition by t2.numb, t2.rn order by cte.field, cte.event_date) 
from t2 join cte on t2.numb=cte.numb 
and cte.event_date>=t2.bd and cte.event_date<isnull(t2.ed, dateadd(d,1,cte.event_date))
order by t2.numb, t2.rn, cte.field, cte.event_date
24 апр 14, 11:03    [15927432]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в запросе  [new]
Andrey Blednykh
Member

Откуда:
Сообщений: 52
Кавказ-сила, aleks2, Dmitry E. Loginov, спасибо за решение. Пытался реализовать вариант предложенный Кавказ-сила, но немного запутался в деталях рекурсии...
24 апр 14, 12:47    [15928417]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить