Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / подсчет отработанных часов / 5 сообщений из 5, страница 1 из 1
02.04.2014, 13:33:05
    #38602983
PG81
PG81
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
подсчет отработанных часов
есть таблица
user_id
exec_time
task_id
code_id (1 начало работы, 2 перерыв, 3 конец рабочего дня)

Нужно посчитать сколько работал часов в день сотрудник.
перерыв не учитывать. НИкак не получается написать SQL запрос
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.04.2014, 15:25:57
    #38603146
Hawkmoon
Hawkmoon
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
подсчет отработанных часов
как определить начало и конец перерыва?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.04.2014, 17:46:47
    #38603371
Dim666
Dim666
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
подсчет отработанных часов
PG81,

если без перерыва (ибо, как выше написал, неясно как считать его продолжительность), то как-то так вычисляются рабочие дни
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
WITH log AS (SELECT * FROM (VALUES
 (12,'12-JAN-14 08:00'::timestamp,1), (12,'12-JAN-14 14:00'::timestamp,3),
 (12,'13-JAN-14 08:00'::timestamp,1), (12,'13-JAN-14 16:00'::timestamp,3),
 (12,'14-JAN-14 08:00'::timestamp,1), (12,'14-JAN-14 15:30'::timestamp,3),
 (12,'02-APR-14 08:00'::timestamp,1)
) as f (user_id, exec_time, code_id))
SELECT a.user_id, a.exec_time as start_work, COALESCE(min(b.exec_time),now()) as stop_work
 FROM log a LEFT JOIN log b ON (b.code_id=3 and a.user_id=b.user_id and a.exec_time<=b.exec_time)
 WHERE a.code_id=1 GROUP BY a.user_id, a.exec_time ORDER BY 1,2

user_id | start_work | stop_work 12 | 2013-01-12 08:00:00 | 2013-01-12 14:00:00+04 12 | 2013-01-13 08:00:00 | 2013-01-13 16:00:00+04 12 | 2013-01-14 08:00:00 | 2013-01-14 15:30:00+04 12 | 2013-04-02 08:00:00 | 2014-04-02 17:38:42.623754+04(4 строки)
Соответственно потом нужно по каждому юзеру посчитать sum(stop_work - start_work) в часах.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.04.2014, 17:58:33
    #38603381
Dmitri Krizhanovski
Dmitri Krizhanovski
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
подсчет отработанных часов
PG81,

Здесь не принято приводить ddl и пример данных?

Первая попытка.
 
version, create table, insert data
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
bctm201401=# select version();
                                            version
-----------------------------------------------------------------------------------------------
 PostgreSQL 8.4.12 on x86_64-unknown-linux-gnu, compiled by GCC gcc (SUSE Linux) 4.6.2, 64-bit
(1 row)


bctm201401=#
Код: 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.
CREATE TABLE xx_time
(
   timeid bigint primary key,
   userid integer, 
   dt timestamp with time zone, 
   code integer
) 
WITH ( OIDS = FALSE );

insert into xx_time (timeid, userid, dt, code) values 
( 1,1,'2014-04-01 08:00',1),
( 2,1,'2014-04-01 08:50',2),
( 3,1,'2014-04-01 08:55',1),
( 4,1,'2014-04-01 11:30',2),
( 5,1,'2014-04-01 11:40',1),
( 6,1,'2014-04-01 12:00',2),
( 7,1,'2014-04-01 13:00',1),
( 8,1,'2014-04-01 15:00',2),
( 9,1,'2014-04-01 17:00',3),

(10,2,'2014-04-01 08:00',1),
(11,2,'2014-04-01 12:00',2),
(12,2,'2014-04-01 13:00',1),
(13,2,'2014-04-01 15:00',3),

(14,3,'2014-04-01 07:00',1),
(15,3,'2014-04-01 11:00',3),

(16,4,'2014-04-01 08:00',1),
(17,4,'2014-04-01 12:00',2),

(18,5,'2014-04-01 08:00',1),
(19,5,'2014-04-01 12:00',2),
(20,5,'2014-04-01 13:00',1),
(21,5,'2014-04-01 17:00',3);

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SELECT userid, SUM( ival ) AS ttltime
  FROM (SELECT userid,
               dt,
               dtnext,
               dtnext - dt AS ival
          FROM (SELECT userid,
                       dt,
                       LEAD( dt ) OVER (PARTITION BY userid ORDER BY userid, dt) AS dtnext,
                       code,
                       LEAD( code ) OVER (PARTITION BY userid ORDER BY userid, dt) AS codenext
                  FROM xx_time) x
         WHERE     code = 1
               AND codenext IN (2, 3)) x
GROUP BY userid
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.04.2014, 09:27:33
    #38603747
PG81
PG81
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
подсчет отработанных часов
 
Исходные данные
Код: 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.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
CREATE TABLE xx_time
(
   timeid bigint primary key,
   userid integer, 
   dt timestamp with time zone, 
   code integer
) 
WITH ( OIDS = FALSE );

insert into xx_time (timeid, userid, dt, code) values 
( 1,1,'2014-04-01 08:00',1),
( 2,1,'2014-04-01 08:50',2),
( 3,1,'2014-04-01 08:55',1),
( 4,1,'2014-04-01 11:30',2),
( 5,1,'2014-04-01 11:40',1),
( 6,1,'2014-04-01 12:00',2),
( 7,1,'2014-04-01 13:00',1),
( 8,1,'2014-04-01 15:00',2),
( 9,1,'2014-04-01 17:00',3),

(10,2,'2014-04-01 08:00',1),
(11,2,'2014-04-01 12:00',2),
(12,2,'2014-04-01 13:00',1),
(13,2,'2014-04-01 15:00',3),

(14,3,'2014-04-01 07:00',1),
(15,3,'2014-04-01 11:00',3),

(16,4,'2014-04-01 08:00',1),
(17,4,'2014-04-01 12:00',2),

(18,5,'2014-04-01 08:00',1),
(19,5,'2014-04-01 12:00',2),
(20,5,'2014-04-01 13:00',1),
(21,5,'2014-04-01 17:00',3),



(31,1,'2014-04-02 08:00',1),
(32,1,'2014-04-02 08:50',2),
(33,1,'2014-04-02 08:55',1),
(34,1,'2014-04-02 11:30',2),
(35,1,'2014-04-02 11:40',1),
(36,1,'2014-04-02 12:00',2),
(37,1,'2014-04-02 13:00',1),
(38,1,'2014-04-02 15:00',2),
(39,1,'2014-04-02 17:00',3),

(40,2,'2014-04-02 08:00',1),
(41,2,'2014-04-02 12:00',2),
(42,2,'2014-04-02 13:00',1),
(43,2,'2014-04-02 15:00',3),

(44,3,'2014-04-02 07:00',1),
(45,3,'2014-04-02 11:00',3),

(46,4,'2014-04-02 08:00',1),
(47,4,'2014-04-02 12:00',2),

(48,5,'2014-04-02 08:00',1),
(49,5,'2014-04-02 12:00',2),
(50,5,'2014-04-02 13:00',1),
(51,5,'2014-04-02 17:00',3);



Спасибо за помощь!
вощем вот что получилось

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select 
cast(date_trunc('day',dt) as date),
userid,
sum(age((select min(dt)
         from xx_time b
         where b.dt>a.dt
           and b.userid=a.userid),
        a.dt))
from xx_time a
where code=1
group by date_trunc('day',dt),userid
order by userid,date_trunc('day',dt)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / подсчет отработанных часов / 5 сообщений из 5, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 322ms
Закрыть
start [/forum/topic.php?fid=53&mobile=1&tid=1998765]:
 0ms
get settings:
 8ms
get forum list:
 14ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 49ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 33ms
get tp. blocked users:
 1ms
others: 203ms
total:  322ms
x
x
Закрыть


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