Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Помогите, пожалуйста, составить запрос. / 15 сообщений из 15, страница 1 из 1
25.11.2008, 13:17
    #35673977
Наумов Александр
Наумов Александр
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите, пожалуйста, составить запрос.
Добрый день!
Помогите пожалуйста написать запрос.
Исходные данные:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
WITH a AS
   (SELECT 'ZF' company,'10' emp_no, TO_DATE('25112008','ddmmyyyy') acc_date, 'IN' in_out_type, TO_DATE('25112008080000','ddmmyyyyhh24miss') stamp from dual
    UNION ALL
    SELECT 'ZF' company,'10' emp_no, TO_DATE('25112008','ddmmyyyy') acc_date, 'IN' in_out_type, TO_DATE('25112008100000','ddmmyyyyhh24miss') stamp from dual
    UNION ALL
    SELECT 'ZF' company,'10' emp_no, TO_DATE('25112008','ddmmyyyy') acc_date, 'OUT' in_out_type, TO_DATE('25112008130000','ddmmyyyyhh24miss') stamp from dual
    UNION ALL
    SELECT 'ZF' company,'10' emp_no, TO_DATE('25112008','ddmmyyyy') acc_date, 'IN' in_out_type, TO_DATE('25112008140000','ddmmyyyyhh24miss') stamp from dual
    UNION ALL
    SELECT 'ZF' company,'10' emp_no, TO_DATE('25112008','ddmmyyyy') acc_date, 'OUT' in_out_type, TO_DATE('25112008180000','ddmmyyyyhh24miss') stamp from dual
    UNION ALL
    SELECT 'ZF' company,'10' emp_no, TO_DATE('25112008','ddmmyyyy') acc_date, 'OUT' in_out_type, TO_DATE('25112008190000','ddmmyyyyhh24miss') stamp from dual
    UNION ALL
    SELECT 'ZF' company,'10' emp_no, TO_DATE('26112008','ddmmyyyy') acc_date, 'OUT' in_out_type, TO_DATE('26112008070000','ddmmyyyyhh24miss') stamp from dual
    UNION ALL
    SELECT 'ZF' company,'10' emp_no, TO_DATE('26112008','ddmmyyyy') acc_date, 'IN' in_out_type, TO_DATE('26112008080000','ddmmyyyyhh24miss') stamp from dual
   )
SELECT * FROM a ORDER BY company, emp_no, acc_date, stamp

Необходимо составить запрос, чтобы получить следующий результат:
Код: plaintext
1.
2.
3.
4.
5.
ZF 	 10  	 25 -ноя- 2008 	 25 -ноя- 2008   8 : 00 : 00 	 25 -ноя- 2008   10 : 00 : 00 
ZF 	 10  	 25 -ноя- 2008 	 25 -ноя- 2008   10 : 00 : 00 	 25 -ноя- 2008   13 : 00 : 00 
ZF 	 10  	 25 -ноя- 2008 	 25 -ноя- 2008   14 : 00 : 00 	 25 -ноя- 2008   18 : 00 : 00 
ZF 	 10  	 25 -ноя- 2008 	 25 -ноя- 2008   18 : 00 : 00 	 25 -ноя- 2008   19 : 00 : 00 
ZF 	 10  	 26 -ноя- 2008 	 26 -ноя- 2008   6 : 00 : 00 	 26 -ноя- 2008   7 : 00 : 00 
ZF 	 10  	 26 -ноя- 2008 	 26 -ноя- 2008   8 : 00 : 00 	 26 -ноя- 2008   21 : 00 : 00

Время 6:00:00 - это значение по умолчанию, если перед строкой с типом 'OUT' не было строк с типом 'IN' в этот день, 21:00:00 - это значение по умолчанию, если после строки с типом 'IN' не было строк с типом 'OUT' в этот день.

Уважаемые гуру, очень надеюсь на Вашу помощь.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.11.2008, 13:31
    #35674022
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите, пожалуйста, составить запрос.
Наумов Александрочень надеюсь на Вашу помощь.Взаимно надеемся на твою нескупость на слова, чтобы мы не гадали, почему нет перехода 13:00 - 14:00
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.11.2008, 13:35
    #35674038
Добрый Э - Эх
Добрый Э - Эх
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите, пожалуйста, составить запрос.
ElicНаумов Александрочень надеюсь на Вашу помощь.Взаимно надеемся на твою нескупость на слова, чтобы мы не гадали, почему нет перехода 13:00 - 14:00Судя по всему потому, что там переход из состояния OUT в состояние IN ...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.11.2008, 14:06
    #35674132
ps
ps
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите, пожалуйста, составить запрос.
Похоже на регистрацию времени - ЗАШЁЛ/ВЫШЕЛ. Но вопросы остаются.
Если emp_no - это номер работника, то как он мог несколько раз войти, не выходя? И также выходить несколько раз подряд.
Если emp_no - это номер отдела, то какой смысл в разрывах? Люди из отдела были постоянно внутри с 8-ми до 19 часов. По-моему не хватает колонки с номером работника.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.11.2008, 14:07
    #35674136
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите, пожалуйста, составить запрос.
Код: plaintext
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.
, aa as
( select * from a
  union all
  select company, emp_no, acc_date, 'IN', trunc(acc_date) +  6 / 24 
    from a 
    group by company, emp_no, acc_date
    having min(in_out_type) keep (dense_rank first order by stamp) <> 'IN'
  union all
  select company, emp_no, acc_date, 'OUT', trunc(acc_date) +  21 / 24 
    from a 
    group by company, emp_no, acc_date
    having min(in_out_type) keep (dense_rank last order by stamp) <> 'OUT'
)
select company, emp_no, acc_date, stamp1, stamp as stamp2
  from
  ( select aa.*, 
        lag(stamp) over (partition by company, emp_no, acc_date order by stamp) as stamp1,
        in_out_type || lag(in_out_type,  1 , 'OUT') over (partition by company, emp_no, acc_date order by stamp) as flag
      from aa 
  )
  where flag <> 'INOUT'
  order BY company, emp_no, acc_date, stamp1
;

CO EM ACC_DATE          STAMP1            STAMP2
-- -- ----------------- ----------------- -----------------
ZF  10   25 . 11 . 08   00 : 00 : 00   25 . 11 . 08   08 : 00 : 00   25 . 11 . 08   10 : 00 : 00 
ZF  10   25 . 11 . 08   00 : 00 : 00   25 . 11 . 08   10 : 00 : 00   25 . 11 . 08   13 : 00 : 00 
ZF  10   25 . 11 . 08   00 : 00 : 00   25 . 11 . 08   14 : 00 : 00   25 . 11 . 08   18 : 00 : 00 
ZF  10   25 . 11 . 08   00 : 00 : 00   25 . 11 . 08   18 : 00 : 00   25 . 11 . 08   19 : 00 : 00 
ZF  10   26 . 11 . 08   00 : 00 : 00   26 . 11 . 08   06 : 00 : 00   26 . 11 . 08   07 : 00 : 00 
ZF  10   26 . 11 . 08   00 : 00 : 00   26 . 11 . 08   08 : 00 : 00   26 . 11 . 08   21 : 00 : 00 

 6  rows selected.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.11.2008, 14:10
    #35674147
Наумов Александр
Наумов Александр
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите, пожалуйста, составить запрос.
Elic,

Исходные данные - учет приходов и уходов сотрудников на фирму.
Записи с типом IN - соответствуют приходу, OUT - соответственно уходу.
Бывают ситуации, когда сотрудник забывает приложить карточку во время прихода/ухода.
Таким образом, например, если 2 строки подряд с типом IN (1я-8:00, 2я-10:00), то считаем время ухода(out_stamp) для 1й результирующей строки равным времени след. прихода (2я строка исходных данных):
Код: plaintext
1.
2.
company	emp_no	acc_date	in_stamp		out_stamp
ZF 	 10  	 25 -ноя- 2008 	 25 -ноя- 2008   8 : 00 : 00 	 25 -ноя- 2008   10 : 00 : 00 
ZF 	 10  	 25 -ноя- 2008 	 25 -ноя- 2008   10 : 00 : 00 	 25 -ноя- 2008   13 : 00 : 00

С 13:00 - 14:00 сотрудника не было на фирме, поэтому интервал и не отображается.

P.S.: Версия Oracle 9.2.0.8.0
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.11.2008, 14:18
    #35674171
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите, пожалуйста, составить запрос.
Наумов АлександрБывают ситуации, когда сотрудник забывает приложить карточку во время прихода/ухода.Хорошо так работать: пришёл в 14:00, типа забыв приложить карточку, и ушёл в 15:00 честно приложившись. В итоге в табеле всё как и положено: от звонка (06:00) до звонка (15:00)
Или в рабочее время сходил в бильярдную ни разу не приложившись

Детский сад, а не учёт рабочего времени :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.11.2008, 14:35
    #35674191
Наумов Александр
Наумов Александр
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите, пожалуйста, составить запрос.
psПохоже на регистрацию времени - ЗАШЁЛ/ВЫШЕЛ.
Так и есть.
ps
Если emp_no - это номер работника, то как он мог несколько раз войти, не выходя? И также выходить несколько раз подряд.
К сожалению внешная программа, которая используется совместно с терминалами входа выхода, не учитывает такие случаи... :(
Зашел приложив пропуск к терминалу входа, когда выходил, пропуск приложил к терминалу выхода сотрудник, который выходил вместе с ним, теперь возврашался, и опять приложил к терминалу входа.
Там 2 терминала 1 - с одной стороны двери, 2й - с другой.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.11.2008, 15:01
    #35674296
Alexander Konakov
Alexander Konakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите, пожалуйста, составить запрос.
Наумов Александр,

турникет типа "вертушка" решает - больше одного человека за раз обычно не просочится :)
а тем, кто по два раза подряд шлёпает - административно бить по ручонкам :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.11.2008, 15:10
    #35674339
Наумов Александр
Наумов Александр
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите, пожалуйста, составить запрос.
Elic,

большое спасибо за помощь с запросом. :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
09.07.2010, 16:45
    #36733103
реаниматор
реаниматор
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите, пожалуйста, составить запрос.
Elic
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
, aa as
( select * from a
  union all
  select company, emp_no, acc_date, 'IN', trunc(acc_date) +  6 / 24 
    from a 
    group by company, emp_no, acc_date
    having min(in_out_type) keep (dense_rank first order by stamp) <> 'IN'
  union all
  select company, emp_no, acc_date, 'OUT', trunc(acc_date) +  21 / 24 
    from a 
    group by company, emp_no, acc_date
    having min(in_out_type) keep (dense_rank last order by stamp) <> 'OUT'
)
select company, emp_no, acc_date, stamp1, stamp as stamp2
  from
  ( select aa.*, 
        lag(stamp) over (partition by company, emp_no, acc_date order by stamp) as stamp1,
        in_out_type || lag(in_out_type,  1 , 'OUT') over (partition by company, emp_no, acc_date order by stamp) as flag
      from aa 
  )
  where flag <> 'INOUT'
  order BY company, emp_no, acc_date, stamp1
;

Код: plaintext
1.
2.
3.
4.
SELECT  distinct company, emp_no,acc_date,
decode(in_out_type,'OUT',lag(stamp, 1 ,acc_date+ 6 / 24 ) over(partition by company,emp_no,acc_date order by stamp),stamp)  in_time,
decode(in_out_type,'IN',lead(stamp, 1 ,acc_date+ 21 / 24 ) over(partition by company,emp_no,acc_date order by stamp),stamp) out_time 
FROM a 
ORDER BY company, emp_no, acc_date, in_time
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
13.10.2016, 13:51
    #39326262
Non-Grata
Non-Grata
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите, пожалуйста, составить запрос.
Добрый день! Подниму тему.
Помогите модифицировать запрос для случая, когда нет значений по умолчанию, и надо отслеживать события, если работник не отметился при входе или выходе. Т.е. если у него несколько входов подряд, то время выхода будет отображаться только для последнего (если есть), а у всех предыдущих будет пустым. Исходные данные те же:

COMPANYEMP_NOACC_DATETIME_INTIME_OUTZF1025.11.0825.11.08 8:00ZF1025.11.0825.11.08 10:0025.11.08 13:00ZF1025.11.0825.11.08 14:0025.11.08 18:00ZF1025.11.0825.11.08 19:00ZF1026.11.0826.11.08 07:00ZF1026.11.0826.11.08 08:00

Заранее спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2016, 14:28
    #39326310
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите, пожалуйста, составить запрос.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
 18  select * from a
 19    match_recognize
 20    ( partition by company,emp_no, acc_date
 21      order by stamp
 22      measures i.stamp as time_in
 23             , o.stamp as time_out
 24      pattern ((i o?)|o)
 25      define i as in_out_type = 'IN'
 26           , o as in_out_type = 'OUT'
 27    );

CO EM ACC_DATE         TIME_IN          TIME_OUT
-- -- ---------------- ---------------- ----------------
ZF 10 25.11.2008 00:00 25.11.2008 08:00
ZF 10 25.11.2008 00:00 25.11.2008 10:00 25.11.2008 13:00
ZF 10 25.11.2008 00:00 25.11.2008 14:00 25.11.2008 18:00
ZF 10 25.11.2008 00:00                  25.11.2008 19:00
ZF 10 26.11.2008 00:00                  26.11.2008 07:00
ZF 10 26.11.2008 00:00 26.11.2008 08:00
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2016, 14:50
    #39326345
Non-Grata
Non-Grata
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите, пожалуйста, составить запрос.
Elic, спасибо, высший пилотаж как всегда!
Но у меня Oracle 11.2.0.1.0 :-)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2016, 15:02
    #39326368
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите, пожалуйста, составить запрос.
Non-GrataНо у меня Oracle 11.2.0.1.0И оригинальная задача, и твоя: STFF start_of_group
 
Код: plsql
1.
, case when (lag(in_out_type) over (partition by company, emp_no, acc_date order by stamp), in_out_type) = (('IN', 'OUT')) then 0 else 1 end as start_of_group

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Помогите, пожалуйста, составить запрос. / 15 сообщений из 15, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 483ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1887242]:
 0ms
get settings:
 7ms
get forum list:
 17ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 176ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 50ms
get tp. blocked users:
 1ms
others: 210ms
total:  483ms
x
x
Закрыть


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