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

Откуда:
Сообщений: 32
Помогите, пожалуйста, решить следующую проблему рекурсивным CTE.
Имеется следующая таблица:

cln_monthemp_numdesc
2011-01-01105aaa
2011-02-01105aaa
2011-04-01105bbb
2011-05-01105aaa


Нужно обозначить для каждого месяца каждого emp_num который работал подряд два месяца на одном и том же desc,
то есть в данном случае на выходе должно получится:

cln_monthemp_numdesc flag
2011-01-01105aaa 'NO'
2011-02-01105aaa 'YES'
2011-04-01105bbb 'NO'
2011-05-01105aaa 'NO'
31 окт 11, 00:25    [11523747]     Ответить | Цитировать Сообщить модератору
 Re: Как создать рекурсивным запросом?  [new]
iljy
Member

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

делаете соединение таблицы на саму себя со сдвигом на месяц назад и проверяете, выполняется ваше условие или нет. Рекурсивное CTE тут ну совем не нужно.
31 окт 11, 00:36    [11523768]     Ответить | Цитировать Сообщить модератору
 Re: Как создать рекурсивным запросом?  [new]
sviha
Member

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

А если больше двух месяцев, скажем 6, то такое соединение не подойдет.
31 окт 11, 00:44    [11523778]     Ответить | Цитировать Сообщить модератору
 Re: Как создать рекурсивным запросом?  [new]
iljy
Member

Откуда:
Сообщений: 8711
sviha
iljy ,

А если больше двух месяцев, скажем 6, то такое соединение не подойдет.

Пронумеруйте строки и используйте соединение по номеру. Тогда без разницы, на сколько месяцев отличаются последовательные строки.
31 окт 11, 01:19    [11523810]     Ответить | Цитировать Сообщить модератору
 Re: Как создать рекурсивным запросом?  [new]
sviha
Member

Откуда:
Сообщений: 32
iljy
sviha
iljy ,

А если больше двух месяцев, скажем 6, то такое соединение не подойдет.

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


Это не поможет, потому что очень важно, чтобы месяцы былу последовательными, то есть если после 2011-02-01 следует 2011-04-01
то здесь нет последовательности: отсутствует 2011-03-01.
31 окт 11, 01:39    [11523829]     Ответить | Цитировать Сообщить модератору
 Re: Как создать рекурсивным запросом?  [new]
iljy
Member

Откуда:
Сообщений: 8711
sviha
iljy
пропущено...

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


Это не поможет, потому что очень важно, чтобы месяцы былу последовательными, то есть если после 2011-02-01 следует 2011-04-01
то здесь нет последовательности: отсутствует 2011-03-01.

Тогда чем вас не устраивает связывание по условию месяц - 1? Определитесь с задачей для начала.
31 окт 11, 09:35    [11524170]     Ответить | Цитировать Сообщить модератору
 Re: Как создать рекурсивным запросом?  [new]
sviha
Member

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

Тогда я немного расширю пример, а то возможно я не совсем верно определил задачу.

cln_monthemp_numdescflag
2011-01-01105aaa'NO'
2011-02-01105aaa'NO'
2011-03-01105aaa'NO'
2011-04-01105aaa'NO'
2011-05-01105aaa'YES'
2011-08-01105aaa'NO'
2011-09-01105bbb'NO'
2011-10-01105aaa'NO'
2011-11-01105aaa'NO'
2011-12-01105aaa'NO'


Тогда я немного расширю пример, а то возможно я не совсем верно определил задачу.

Допустим требуется чтобы работник (emp_num) работал подряд 5 месяцев или больше на одном и том же desc, но так чтобы была непрерывность.
В данном случае только с 2011-01-01 по 2011-05-01 не было перерывов в работе, человек работал все пять месяцев на одном и том же desc пез перерыва. Причем только одна строка (2011-05-01) получила 'YES' а другие нет, так как относительно других месяцов он еще не успел отработать 5 месяцев. Между 2011-05-01 и 2011-08-01 у него получается был перерыв, хоть и в 2011-08-01 он работал на том же desc поэтому и flag получил 'NO', так как он прервал работу.
Далее, с 2011-08-01 до 2011-09-01 он вообще работал на другом desc, но не успел отработать хотя бы 5 месяцев.
С 2011-09-01 по 2011-12-01 он хоть и работал без перерывов, но все же те же 5 месяцев не успел отработать.

В общем, я думаю что обычным соединением по cln_month - 1 не получится решить эту проблему.
Kажется здесь можно как-то разрешить это проблему рекурсией.

Есть конечно немного другой способ, но этот способ зависим от количества месяцев.

select a.*
 from a
   where exists
              (select 1
                  from a a1
                  where a.cln_month = a1.cln_month - 1)
   and exists (select 1
                     from a a2
                     where a.cln_month = a2.cln_month - 2)
   *
   *
   *
   and exists (select
                    from a a5
                     where a.cln_month = a5.cln_month - 5)


Поэтому и хочется узнать, как решить эту проблему рекурсивно, или способом, не привязанным к кол-ву месяцев, потому что у меня что-то не получается.
31 окт 11, 13:06    [11525902]     Ответить | Цитировать Сообщить модератору
 Re: Как создать рекурсивным запросом?  [new]
iljy
Member

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

четче надо стаивть задачу. Соединение тут не поможет, но и рекурсия не нужна.
declare @t table (cln_month	date, emp_num int, 	descr varchar(10))
insert @t values
('20110101',105,'aaa'),
('20110201',105,'aaa'),
('20110301',105,'aaa'),
('20110401',105,'aaa'),
('20110501',105,'aaa'),
('20110801',105,'aaa'),
('20110901',105,'bbb'),
('20111001',105,'aaa'),
('20111101',105,'aaa'),
('20111201',105,'aaa')

declare @months int = 3

select emp_num, descr, MIN(cln_month) mi, MAX(cln_month) ma
from(
	select *, DATEDIFF(mm, 0,cln_month) m, ROW_NUMBER() over(partition by emp_num, descr order by cln_month) rn
	from @t
)t
group by emp_num, descr,  m-rn
having MAX(m) - MIN(m) >= @months - 1
31 окт 11, 13:23    [11526037]     Ответить | Цитировать Сообщить модератору
 Re: Как создать рекурсивным запросом?  [new]
sviha
Member

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

Большое спасибо! Это работает. Что самое интересное, что я в похожем направлении и думал, но что-то до конца сложить все не смог.
31 окт 11, 15:27    [11527253]     Ответить | Цитировать Сообщить модератору
 Re: Как создать рекурсивным запросом?  [new]
Yuriy Petrov
Member

Откуда:
Сообщений: 91
как вариант...
declare @t table (cln_month	date, emp_num int, 	descr varchar(10))

insert @t values
('20110101',105,'aaa'),
('20110201',105,'aaa'),
('20110301',105,'aaa'),
('20110401',105,'aaa'),
('20110501',105,'aaa'),
('20110801',105,'aaa'),
('20110901',105,'bbb'),
('20111001',105,'aaa'),
('20111101',105,'aaa'),
('20111201',105,'aaa')

declare @months int = 3

select *,
       case when (select COUNT(*)
                    from @t t2
                   where t2.cln_month >= dateadd (MONTH, -@months+1, t1.cln_month)
                     and t2.cln_month <= t1.cln_month
                     and t2.emp_num = t1.emp_num
                     and t2.descr = t1.descr)
                 >= @months
            then 'Y' else 'N' end
from @t t1
31 окт 11, 15:48    [11527503]     Ответить | Цитировать Сообщить модератору
 Re: Как создать рекурсивным запросом?  [new]
sviha
Member

Откуда:
Сообщений: 32
Yuriy Petrov,


Благодарю, это тоже очень симпатичное решение.
А еще в этом примере, конечно, напрашивается рекурсия.
31 окт 11, 20:04    [11529235]     Ответить | Цитировать Сообщить модератору
 Re: Как создать рекурсивным запросом?  [new]
iljy
Member

Откуда:
Сообщений: 8711
sviha
Yuriy Petrov,


Благодарю, это тоже очень симпатичное решение.
А еще в этом примере, конечно, напрашивается рекурсия.

Куда напрашивается, а главное - нафига? Тут и без нее все прекрасно получается.
1 ноя 11, 00:52    [11530095]     Ответить | Цитировать Сообщить модератору
 Re: Как создать рекурсивным запросом?  [new]
sviha
Member

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


Напрашивается, потому что для каждого работника (emp_num) каждый последующий месяц имеет отношение к каждому предыдыщему. n-1 n-2 ... n - (k-1)
А зачем нужно...? Может, и не нужно, но просто интересно.
1 ноя 11, 12:50    [11532028]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить