Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Поиск отсутствующих периодов / 14 сообщений из 14, страница 1 из 1
09.02.2022, 16:17
    #40132852
alol55
alol55
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск отсутствующих периодов
Добрый день!

Помогите, пожалуйста, решить задачку для отличников)
В таблице staj хранятся периоды работы сотрудников предприятия:

tab_nomer;data_s;data_po123;10.01.2018;20.03.2019123;01.01.2019;31.12.2020123;01.04.2021;31.12.2021 456;05.03.2016;01.12.2016456;01.01.2018;31.12.2018

Необходимо выбрать табельные номера сотрудников, у которых есть ПЕРЕРЫВЫ в работе длительностью более 6 месяцев. В примере выше сотрудник 123 не должен попасть в выборку, так как у него перерыв в стаже 3 месяца, а сотрудник 456 должен попасть в выборку, так как у него перерыв в стаже 13 месяцев.

Периоды могут пересекаться. Все даты заполнены, null'ов не бывает. Если это поможет, есть еще таблица, содержащая календарь с 1900 года, в котором одна строка - один день, все даты уникальны.

Можно ли это выбрать с помощью sql? Помогите, пожалуйста, советами.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.02.2022, 17:13
    #40132869
SERG1257
SERG1257
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск отсутствующих периодов
Для каждой записи поставьте флаг - data_po не принадлежит другому периоду стало быть после data_po перерыв в стаже, и число месяцев до следующей data_s.
После этого выборка будет тривиальной.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.02.2022, 17:16
    #40132871
alol55
alol55
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск отсутствующих периодов
SERG1257, спасибо, хорошая идея)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.02.2022, 17:35
    #40132879
3unknown
3unknown
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск отсутствующих периодов
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
drop table if exists #tmp

create table #tmp(tab_nomer int,data_s datetime,data_po datetime)

insert #tmp
select 123,'20181001','20190320'
union
select 123,'20190101','20201231'
union
select 123,'20210104','20211231'
union
select 456,'20160503','20161201'
union
select 456,'20180101','20181231'

;with t as(
select *
,row_number() over(partition by tab_nomer order by data_s) n
from #tmp
)
select t.tab_nomer
from t
join t t1 on t.tab_nomer = t1.tab_nomer
and t1.n-t.n=1
where datediff(month,t.data_po,t1.data_s)>3

alol55,
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.02.2022, 17:45
    #40132883
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск отсутствующих периодов
3unknown
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
drop table if exists #tmp

create table #tmp(tab_nomer int,data_s datetime,data_po datetime)

insert #tmp
select 123,'20181001','20190320'
union
select 123,'20190101','20201231'
union
select 123,'20210104','20211231'
union
select 456,'20160503','20161201'
union
select 456,'20180101','20181231'

;with t as(
select *
,row_number() over(partition by tab_nomer order by data_s) n
from #tmp
)
select t.tab_nomer
from t
join t t1 on t.tab_nomer = t1.tab_nomer
and t1.n-t.n=1
where datediff(month,t.data_po,t1.data_s)>3



Садись. Неуд.

ЗЫ. Индексы отдыхают.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.02.2022, 17:58
    #40132884
alol55
alol55
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск отсутствующих периодов
3unknown
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
drop table if exists #tmp

create table #tmp(tab_nomer int,data_s datetime,data_po datetime)

insert #tmp
select 123,'20181001','20190320'
union
select 123,'20190101','20201231'
union
select 123,'20210104','20211231'
union
select 456,'20160503','20161201'
union
select 456,'20180101','20181231'

;with t as(
select *
,row_number() over(partition by tab_nomer order by data_s) n
from #tmp
)
select t.tab_nomer
from t
join t t1 on t.tab_nomer = t1.tab_nomer
and t1.n-t.n=1
where datediff(month,t.data_po,t1.data_s)>3

alol55,


Спасибо, но правильно ли это сработает для такого случая:
01.01.2018 - 01.10.2018
01.02.2018 - 01.05.2018
01.09.2018 - 01.04.2020
?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.02.2022, 18:07
    #40132886
3unknown
3unknown
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск отсутствующих периодов
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
drop table if exists #tmp

create table #tmp(tab_nomer int,data_s datetime,data_po datetime)



insert #tmp
select 123,'20181001','20190320'
union
select 123,'20190101','20201231'
union
select 123,'20210104','20211231'
union
select 456,'20160503','20161201'
union
select 456,'20180101','20181231'

with t as(
select*
,datediff(month,data_po,lead(data_s) over(partition by tab_nomer order by data_s)) d
from #tmp
)
select tab_nomer from t
where d>3
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.02.2022, 18:13
    #40132888
alol55
alol55
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск отсутствующих периодов
3unknown,

для предыдущего примера, наверное, это тоже сработает неправильно
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.02.2022, 18:16
    #40132890
3unknown
3unknown
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск отсутствующих периодов
alol55,
Да, для таких случаев этот подход не работает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.02.2022, 18:46
    #40132900
HandKot
HandKot
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск отсутствующих периодов
alol55 , возможно так

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
drop table if exists #tmp

create table #tmp(tab_nomer int,data_s datetime,data_po datetime)

insert #tmp

select 123,'20181001','20190320'
union
select 123,'20190101','20201231'
union
select 123,'20210104','20211231'
union
select 456,'20160503','20161201'
union
select 456,'20180101','20181231'
union all
select 789,'20180101','20181001'
union
select 789,'20180201','20180501'
union
select 789,'20180901','20200401'


select * from #tmp

;with p as (
				select t.tab_nomer, dt = t.data_s, flag = -1 
				from #tmp t
				union all
				select t.tab_nomer, t.data_po, 1
				from #tmp t
		)
	, s as (
		select p.tab_nomer
			  ,p.dt
			  ,e = sum(p.flag)  over (partition by p.tab_nomer order by p.dt, p.flag)
			  ,de = lead(p.dt) over (partition by p.tab_nomer order by p.dt)
		from p
	)
select s.*
from s
where s.e = 0 and datediff(mm, s.dt, s.de) > 6
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.02.2022, 19:35
    #40132922
alol55
alol55
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск отсутствующих периодов
HandKot,

идею не поняла, но попробую, спасибо
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 00:02
    #40132973
Wlr-l
Wlr-l
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск отсутствующих периодов
alol55,

Посмотрите здесь: "Добавить недостающие периоды" 21132571 .
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 08:38
    #40133011
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск отсутствующих периодов
Код: sql
1.
2.
3.
4.
5.
6.
7.
with t as ( select * from #tmp )
 select * from t
   where exists( select * from t as t1 
                         where t1.tab_nomer = t.tab_nomer and t1.data_s > t.data_po and t1.data_s >= dateadd(month, 3, t.data_po ) 
                            and not not exists( select * from t as t2 where t2.tab_nomer = t.tab_nomer and t1.data_s between t2.data_s and t2.data_po and t2.id <> t1.id)
               )
         and not exists( select * from t as t1 where t1.tab_nomer = t.tab_nomer and t.data_po between t1.data_s and t1.data_po and t1.id <> t.id)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 08:58
    #40133014
alol55
alol55
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск отсутствующих периодов
Ух сколько вариантов накидали)
Всем огромное спасибо за помощь, пошла тренироваться с выборками!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Поиск отсутствующих периодов / 14 сообщений из 14, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 291ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=1683850]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 174ms
get topic data:
 14ms
get forum data:
 3ms
get page messages:
 53ms
get tp. blocked users:
 2ms
others: 12ms
total:  291ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]