powered by simpleCommunicator - 2.0.48     © 2025 Programmizd 02
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Поиск отсутствующих периодов
14 сообщений из 14, страница 1 из 1
Поиск отсутствующих периодов
    #40132852
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
Поиск отсутствующих периодов
    #40132869
SERG1257
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Для каждой записи поставьте флаг - data_po не принадлежит другому периоду стало быть после data_po перерыв в стаже, и число месяцев до следующей data_s.
После этого выборка будет тривиальной.
...
Рейтинг: 0 / 0
Поиск отсутствующих периодов
    #40132871
alol55
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
SERG1257, спасибо, хорошая идея)
...
Рейтинг: 0 / 0
Поиск отсутствующих периодов
    #40132879
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
Поиск отсутствующих периодов
    #40132883
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
Поиск отсутствующих периодов
    #40132884
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
Поиск отсутствующих периодов
    #40132886
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
Поиск отсутствующих периодов
    #40132888
alol55
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
3unknown,

для предыдущего примера, наверное, это тоже сработает неправильно
...
Рейтинг: 0 / 0
Поиск отсутствующих периодов
    #40132890
3unknown
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
alol55,
Да, для таких случаев этот подход не работает.
...
Рейтинг: 0 / 0
Поиск отсутствующих периодов
    #40132900
Фотография 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
Поиск отсутствующих периодов
    #40132922
alol55
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
HandKot,

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

Посмотрите здесь: "Добавить недостающие периоды" 21132571 .
...
Рейтинг: 0 / 0
Поиск отсутствующих периодов
    #40133011
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
Поиск отсутствующих периодов
    #40133014
alol55
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Ух сколько вариантов накидали)
Всем огромное спасибо за помощь, пошла тренироваться с выборками!
...
Рейтинг: 0 / 0
14 сообщений из 14, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Поиск отсутствующих периодов
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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