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

Откуда: Приднестровье
Сообщений: 295
Здравствуйте.

MS SQL SERVER 2012.

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

CREATE TABLE [dbo].[Отпуск](
	[НачОтп] [date] NULL,
	[КонОтп] [date] NULL
) ON [PRIMARY]

INSERT INTO [Отпуск] (НачОтп, КонОтп) ('01-01-2016', '15-01-2016')
INSERT INTO [Отпуск] (НачОтп, КонОтп) ('16-01-2016', '30-01-2016')


CREATE TABLE [dbo].[Больничный](
	[НачБол] [date] NULL,
	[КонБол] [date] NULL
) ON [PRIMARY]

INSERT INTO [Отпуск] (НачБол, КонБол) ('04-01-2016', '10-01-2016')
INSERT INTO [Отпуск] (НачБол, КонБол) ('25-01-2016', '02-02-2016')


в результате селекта хочу получить
СПо
01-01-201604-01-2016
10-01-201615-01-2016
16-01-201625-01-2016


Как организовать запрос ?

Спасибо ...
11 янв 17, 17:06    [20094790]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица  [new]
Wlr-l
Member

Откуда:
Сообщений: 429
Алексаша,

Периоды [НачОтп, КонОтп] и [НачБол, КонБол] не пересекаются, если выполняются условия:

(КонОтп<НачБол)and(КонБол<НачОтп).

Отрицание этого условия даст условие пересечения периодов:

not((КонОтп<НачБол)and(КонБол<НачОтп))=(КонОтп>=НачБол)or(КонБол>=НачОтп),

т.е. отрицание конъюнкции есть не что иное, как дизъюнкция отрицаний (закон де Моргана).
11 янв 17, 17:45    [20094974]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица  [new]
Wlr-l
Member

Откуда:
Сообщений: 429
Wlr-l,
Поспешил, правильно будет так:


Периоды [НачОтп, КонОтп] и [НачБол, КонБол] не пересекаются, если выполняются условия:

(КонОтп<НачБол)or(КонБол<НачОтп).

Отрицание этого условия даст условие пересечения периодов:

not((КонОтп<НачБол)or(КонБол<НачОтп))=(КонОтп>=НачБол)and(КонБол>=НачОтп),

т.е. отрицание дизъюнкции есть не что иное, как конъюнкция отрицаний (закон де Моргана).
11 янв 17, 17:53    [20095001]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица  [new]
Wlr-l
Member

Откуда:
Сообщений: 429
Да, еще забыл сказать, что периоды [НачОтп, КонОтп] и [НачБол, КонБол] должны относиться к одному и тому же человеку.
11 янв 17, 18:00    [20095041]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица  [new]
Алексаша
Member

Откуда: Приднестровье
Сообщений: 295
Wlr-l,

То о чем вы говорите понятно. Не могу сфорганить запрос. Видите в результатирующей табл. поля записи должны формироваться из 2 табл. т.е. не просто надо исключить пересекающиеся записи. Обратите внимание на рез. таблицу пожалуста
11 янв 17, 18:11    [20095077]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 5214
С пересечением-то понятно, а вот как составить список. Кроме как совместить данные и пронумеровать чёт-нечет ничего не приходит в голову.

Например, такая картина:
А1 Б1 Б2 Б3 Б4 Б5 А2 Б6 А3 Б7 Б8 А4

Если начинается с А, то чёт, если с Б, то нечет.

пресечение1
А1 1
Б1 2
Б2 1
Б3 2
Б4 1
Б5 2
А2 1
Б6

пересечение2
А3 1
Б7 2
Б8 1
А4

оставляем единицы, не выходящие за пределы А интервала:
А1 1 Б1
Б2 1 Б3
Б4 1 Б5
А3 1 Б7
Б8 1 А4
11 янв 17, 18:11    [20095078]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица  [new]
o-o
Guest
Wlr-l
Периоды [НачОтп, КонОтп] и [НачБол, КонБол] не пересекаются, если выполняются условия:

(КонОтп<НачБол)or(КонБол<НачОтп).

Отрицание этого условия даст условие пересечения периодов:

not((КонОтп<НачБол)or(КонБол<НачОтп))=(КонОтп>=НачБол)and(КонБол>=НачОтп)

предлагаю новый тэг для оформления цитат из БОЛ-а: [НачБол] [КонБол]
11 янв 17, 18:12    [20095080]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица  [new]
3unknown
Member

Откуда:
Сообщений: 129
with a as ( 
 select [НачОтп] as date, 1 as f from [dbo].[Отпуск]
 union
 select [КонОтп] as date, 2 as f from [dbo].[Отпуск] 
 union
 select [НачБол] as date, 3 as f from [dbo].[Больничный]
 union
 select [КонБол] as date, 4 as f from [dbo].[Больничный]
 ) 
 , b as
 (select row_number() over(order by date) n,date,f from a )

 select b.date, b1.date,b.f,b1.f
 from b join b b1 on b1.n-b.n=1
	where (b.f = 1 and b1.f = 2)
	or (b.f = 1 and b1.f = 4)
	or(b.f = 1 and b1.f = 3)
	or (b.f = 4 and b1.f = 2)
11 янв 17, 18:18    [20095118]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица  [new]
Wlr-l
Member

Откуда:
Сообщений: 429
o-o,

Согласен, но пальма первенства принадлежит Алексаше.
11 янв 17, 18:18    [20095119]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица  [new]
Алексаша
Member

Откуда: Приднестровье
Сообщений: 295
3unknown,

Спасибо, очень помогли !
11 янв 17, 18:34    [20095179]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица  [new]
Wlr-l
Member

Откуда:
Сообщений: 429
"Обратите внимание на рез. таблицу пожалуста". Обратил еще вчера, но меня остановило слово "сфорганить ".

Несмотря на то, что ТС уже сказал "Спасибо", задача еще не решена. Точнее даже не поставлена.

"Есть таблица содержащая периоды отпуска и таблица периодов больничного. На основании этих двух таблиц нужно получить периоды отпуска не покрытых больничным".

В результате хочу получить ("сфорганить"):
С По
01-01-2016 04-01-2016
10-01-2016 15-01-2016
16-01-2016 25-01-2016

Но 4 января человек был в отпуске и на больничном, поэтому этот день отпуска покрывается больничным, следовательно, эта дата не должна входить в результат.
Поскольку нет признака, по которому отличаются различные периоды отпусков и больничных, правильный результат должен быть таким:
С По
01-01-2016 03-01-2016
11-01-2016 24-01-2016

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

Решение:
1.На календаре отмечаем дни отпусков и дни больничных;
2.Находим дни, когда человек был в отпуске и не был на больничном;
3.Находим интервалы непрерывности;
4.Для каждого интервала непрерывности находим дату начала интервала и дату окончания интервала.

Примерно так решают эту задачу работники отдела кадров.
12 янв 17, 12:37    [20097899]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 3758
автор
Примерно так решают эту задачу работники отдела кадров.

а как её решают сотрудники транспортного цеха?
12 янв 17, 12:42    [20097931]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица  [new]
Wlr-l
Member

Откуда:
Сообщений: 429
Пример одного из возможных решений:

if object_id(N'tempdb..#kal') is not null drop table #kal;
create table #kal (
 n      int   not null,
 d      date  not null
);
with
 l0(n) as (select 0 union all select 0 union all select 0),
 l1(n) as (select 0 from l0 t1 cross join l0 t2),
 l2(n) as (select 0 from l1 t1 cross join l1 t2),
 r(n)  as (select row_number() over(order by (select 0)) from l2)
 insert #kal (n, d)
  select n, cast(dateadd(DAY, n-1, '20160101') as date) d
    from r
    --where n <= 200;
;
--select * from #kal;

if object_id(N'tempdb..#otp') is not null drop table #otp;
create table #otp (
 nOtp   date  not null,
 kOtp   date  not null
);
insert #otp (      nOtp,       kOtp)
  values    ('20160101', '20160115'),
            ('20160116', '20160130'),
            ('20160205', '20160208'),
            ('20160212', '20160220');

if object_id(N'tempdb..#bol') is not null drop table #bol;
create table #bol (
 nBol   date  not null,
 kBol   date  not null
);
insert #bol (      nBol,       kBol)
  values    ('20160104', '20160110'),
            ('20160125', '20160202'),
            ('20160210', '20160215');

--select * from #otp;
--select * from #bol;

with A as (--отмечаем на календаре дни отпусков и дни больничных
  select k.n,
         k.d,
         (select 1 from #otp o where k.d between o.nOtp and o.kOtp) as o,
         (select 1 from #bol b where k.d between b.nBol and b.kBol) as b
    from #kal k
)
, B as (--находим дни, когда человвек был в отпуске и не был на больничном
  select d, n-row_number() over (order by n) as rn
    from A
   where o=1 and b is null 
)
--находим интервалы непрерывности и их концы 
select min(d), max(d)
  from B
  group by rn

Результат:
2016-01-01	2016-01-03
2016-01-11 2016-01-24
2016-02-05 2016-02-08
2016-02-16 2016-02-20
12 янв 17, 12:50    [20097991]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица  [new]
Wlr-l
Member

Откуда:
Сообщений: 429
TaPaK
автор
Примерно так решают эту задачу работники отдела кадров.

а как её решают сотрудники транспортного цеха?


Скажу честно, что не знаю. Я у них ни разу не был.
12 янв 17, 12:53    [20098016]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица  [new]
Алексаша
Member

Откуда: Приднестровье
Сообщений: 295
Wlr-l,

Абсолютно правы
Wlr-l
Но 4 января человек был в отпуске и на больничном, поэтому этот день отпуска покрывается больничным ...

когда создавал тему не придал этому значения, в итоге получил ответ, который потом допилил чтобы избежать этой коллизии.
Благодарю, что не поленились вчитаться в вопрос, сформулировать его правильно и дать пример решения. Обязательно воспользуюсь им.
Еще раз спасибо ...
15 янв 17, 20:40    [20108136]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица  [new]
Алексаша
Member

Откуда: Приднестровье
Сообщений: 295
Wlr-l,
в этой части кода делается календарь на 81 день 2016 года
Wlr-l
with
 l0(n) as (select 0 union all select 0 union all select 0),
 l1(n) as (select 0 from l0 t1 cross join l0 t2),
 l2(n) as (select 0 from l1 t1 cross join l1 t2),
 r(n)  as (select row_number() over(order by (select 0)) from l2)
 insert #kal (n, d)
  select n, cast(dateadd(DAY, n-1, '20160101') as date) d
    from r

а как Бы вы поступили если на входе не известно какие года обхватят периоды отпусков и больничных. (тема отпусков и больничных это к примеру) ?
15 янв 17, 21:28    [20108298]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 25740
Алексаша
в этой части кода делается календарь на 81 день 2016 года
Это пример заполнения таблицы-календаря.
В реальной системе нужно такую таблицу сделать постоянной, и заполнить с запасом (скажем, на 100 лет)
16 янв 17, 06:14    [20108884]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить