добрый день!
подскажите пожалуйста, как сделать процедуру в базе, которая возвращает среднее значение работы сотрудника за час?
имеется таблица вида:
подскажите пожалуйста, как сделать процедуру в базе, которая возвращает среднее значение работы сотрудника за час?
имеется таблица вида:
Код:
user_id t_start t_stop
сотрудник 1 26.03.2018 11:24:11 26.03.2018 11:27:48
сотрудник 1 26.03.2018 11:27:53 26.03.2018 11:31:54
сотрудник 1 26.03.2018 11:31:54 26.03.2018 11:42:50
сотрудник 1 26.03.2018 12:59:00 26.03.2018 13:24:00
где t_start - начало работы, t_stop соответственно конец.
в итоге нужно получить таблицу следующего вида:
user_id 10:00-11:00 11:00-12:00 12:00-13:00 13:00-14:00 и так до 18:00
сотрудник1 0 ~18/60 1/60 24/60
есть код, но результата так и не добился, надо считать с 8 утра до 8 утра следующего дня. на данный момент, если сотрудник работает, допустим, с 20:00 до 01:00 без перерывов - ему не правильно посчитается статус. пробовал задавать промежутки без extract - не правильно считает.
create or alter procedure user_worktime (
user_id d_user,
d d_date)
returns (
user_name d_user,
"08:00 - 09:00" d_bal2,
"09:00 - 10:00" d_bal2,
"10:00 - 11:00" d_bal2,
"11:00 - 12:00" d_bal2,
"12:00 - 13:00" d_bal2,
"13:00 - 14:00" d_bal2,
"14:00 - 15:00" d_bal2,
"15:00 - 16:00" d_bal2,
"16:00 - 17:00" d_bal2,
"17:00 - 18:00" d_bal2,
"18:00 - 19:00" d_bal2,
"19:00 - 20:00" d_bal2,
"20:00 - 21:00" d_bal2,
"21:00 - 22:00" d_bal2,
"22:00 - 23:00" d_bal2,
"23:00 - 00:00" d_bal2,
"00:00 - 01:00" d_bal2,
"01:00 - 02:00" d_bal2,
"02:00 - 03:00" d_bal2,
"03:00 - 04:00" d_bal2,
"04:00 - 05:00" d_bal2,
"05:00 - 06:00" d_bal2,
"06:00 - 07:00" d_bal2,
"07:00 - 08:00" d_bal2)
as
begin
for select
u1.user_name,
sum((case
when extract(hour from wt.t_start)<=8 and extract(hour from wt.t_stop)>=8
then case when extract(hour from wt.t_start)<8 and extract(hour from wt.t_stop)>8 then cast('09:00:00' as time)-cast('08:00:00' as time)
when extract(hour from wt.t_start)<8 then cast(wt.t_stop as time)-cast('08:00:00' as time)
when extract(hour from wt.t_stop)>8 then cast('09:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between08and09,
sum((case
when extract(hour from wt.t_start)<=9 and extract(hour from wt.t_stop)>=9
then case when extract(hour from wt.t_start)<9 and extract(hour from wt.t_stop)>9 then cast('10:00:00' as time)-cast('09:00:00' as time)
when extract(hour from wt.t_start)<9 then cast(wt.t_stop as time)-cast('09:00:00' as time)
when extract(hour from wt.t_stop)>9 then cast('10:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between09and10,
sum((case
when extract(hour from wt.t_start)<=10 and extract(hour from wt.t_stop)>=10
then case when extract(hour from wt.t_start)<10 and extract(hour from wt.t_stop)>10 then cast('11:00:00' as time)-cast('10:00:00' as time)
when extract(hour from wt.t_start)<10 then cast(wt.t_stop as time)-cast('10:00:00' as time)
when extract(hour from wt.t_stop)>10 then cast('11:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between10and11,
sum((case
when extract(hour from wt.t_start)<=11 and extract(hour from wt.t_stop)>=11
then case when extract(hour from wt.t_start)<11 and extract(hour from wt.t_stop)>11 then cast('12:00:00' as time)-cast('11:00:00' as time)
when extract(hour from wt.t_start)<11 then cast(wt.t_stop as time)-cast('11:00:00' as time)
when extract(hour from wt.t_stop)>11 then cast('12:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between11and12,
sum((case
when extract(hour from wt.t_start)<=12 and extract(hour from wt.t_stop)>=12
then case when extract(hour from wt.t_start)<12 and extract(hour from wt.t_stop)>12 then cast('13:00:00' as time)-cast('12:00:00' as time)
when extract(hour from wt.t_start)<12 then cast(wt.t_stop as time)-cast('12:00:00' as time)
when extract(hour from wt.t_stop)>12 then cast('13:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between12and13,
sum((case
when extract(hour from wt.t_start)<=13 and extract(hour from wt.t_stop)>=13
then case when extract(hour from wt.t_start)<13 and extract(hour from wt.t_stop)>13 then cast('14:00:00' as time)-cast('13:00:00' as time)
when extract(hour from wt.t_start)<13 then cast(wt.t_stop as time)-cast('13:00:00' as time)
when extract(hour from wt.t_stop)>13 then cast('14:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between13and14,
sum((case
when extract(hour from wt.t_start)<=14 and extract(hour from wt.t_stop)>=14
then case when extract(hour from wt.t_start)<14 and extract(hour from wt.t_stop)>14 then cast('15:00:00' as time)-cast('14:00:00' as time)
when extract(hour from wt.t_start)<14 then cast(wt.t_stop as time)-cast('14:00:00' as time)
when extract(hour from wt.t_stop)>14 then cast('15:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between14and15,
sum((case
when extract(hour from wt.t_start)<=15 and extract(hour from wt.t_stop)>=15
then case when extract(hour from wt.t_start)<15 and extract(hour from wt.t_stop)>15 then cast('16:00:00' as time)-cast('15:00:00' as time)
when extract(hour from wt.t_start)<15 then cast(wt.t_stop as time)-cast('15:00:00' as time)
when extract(hour from wt.t_stop)>15 then cast('16:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between15and16,
sum((case
when extract(hour from wt.t_start)<=16 and extract(hour from wt.t_stop)>=16
then case when extract(hour from wt.t_start)<16 and extract(hour from wt.t_stop)>16 then cast('17:00:00' as time)-cast('16:00:00' as time)
when extract(hour from wt.t_start)<16 then cast(wt.t_stop as time)-cast('16:00:00' as time)
when extract(hour from wt.t_stop)>16 then cast('17:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between16and17,
sum((case
when extract(hour from wt.t_start)<=17 and extract(hour from wt.t_stop)>=17
then case when extract(hour from wt.t_start)<17 and extract(hour from wt.t_stop)>17 then cast('18:00:00' as time)-cast('17:00:00' as time)
when extract(hour from wt.t_start)<17 then cast(wt.t_stop as time)-cast('17:00:00' as time)
when extract(hour from wt.t_stop)>17 then cast('18:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between17and18,
sum((case
when extract(hour from wt.t_start)<=18 and extract(hour from wt.t_stop)>=18
then case when extract(hour from wt.t_start)<18 and extract(hour from wt.t_stop)>18 then cast('19:00:00' as time)-cast('18:00:00' as time)
when extract(hour from wt.t_start)<18 then cast(wt.t_stop as time)-cast('18:00:00' as time)
when extract(hour from wt.t_stop)>18 then cast('19:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between18and19,
sum((case
when extract(hour from wt.t_start)<=19 and extract(hour from wt.t_stop)>=19
then case when extract(hour from wt.t_start)<19 and extract(hour from wt.t_stop)>19 then cast('20:00:00' as time)-cast('19:00:00' as time)
when extract(hour from wt.t_start)<19 then cast(wt.t_stop as time)-cast('19:00:00' as time)
when extract(hour from wt.t_stop)>19 then cast('20:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between19and20,
sum((case
when extract(hour from wt.t_start)<=20 and extract(hour from wt.t_stop)>=20
then case when extract(hour from wt.t_start)<20 and extract(hour from wt.t_stop)>20 then cast('21:00:00' as time)-cast('20:00:00' as time)
when extract(hour from wt.t_start)<20 then cast(wt.t_stop as time)-cast('20:00:00' as time)
when extract(hour from wt.t_stop)>20 then cast('21:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between20and21,
sum((case
when extract(hour from wt.t_start)<=21 and extract(hour from wt.t_stop)>=21
then case when extract(hour from wt.t_start)<21 and extract(hour from wt.t_stop)>21 then cast('22:00:00' as time)-cast('21:00:00' as time)
when extract(hour from wt.t_start)<21 then cast(wt.t_stop as time)-cast('21:00:00' as time)
when extract(hour from wt.t_stop)>21 then cast('22:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between21and22,
sum((case
when extract(hour from wt.t_start)<=22 and extract(hour from wt.t_stop)>=22
then case when extract(hour from wt.t_start)<22 and extract(hour from wt.t_stop)>22 then cast('23:00:00' as time)-cast('22:00:00' as time)
when extract(hour from wt.t_start)<22 then cast(wt.t_stop as time)-cast('22:00:00' as time)
when extract(hour from wt.t_stop)>22 then cast('23:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between22and23,
sum((case
when extract(hour from wt.t_start)<=23 and extract(hour from wt.t_stop)>=23
then case when extract(hour from wt.t_start)<23 and extract(hour from wt.t_stop)>23 then cast('23:59:59' as time)-cast('23:00:00' as time)
when extract(hour from wt.t_start)<23 then cast(wt.t_stop as time)-cast('23:00:00' as time)
when extract(hour from wt.t_stop)>23 then cast('23:59:59' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between23and00,
sum((case
when extract(hour from wt.t_start)<=0 and extract(hour from wt.t_stop)>=0
then case when extract(hour from wt.t_start)<0 and extract(hour from wt.t_stop)>0 then cast('01:00:00' as time)-cast('00:00:00' as time)
when extract(hour from wt.t_start)<0 then cast(wt.t_stop as time)-cast('00:00:00' as time)
when extract(hour from wt.t_stop)>0 then cast('01:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between00and01,
sum((case
when extract(hour from wt.t_start)<=1 and extract(hour from wt.t_stop)>=1
then case when extract(hour from wt.t_start)<1 and extract(hour from wt.t_stop)>1 then cast('02:00:00' as time)-cast('01:00:00' as time)
when extract(hour from wt.t_start)<1 then cast(wt.t_stop as time)-cast('01:00:00' as time)
when extract(hour from wt.t_stop)>1 then cast('02:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between01and02,
sum((case
when extract(hour from wt.t_start)<=2 and extract(hour from wt.t_stop)>=2
then case when extract(hour from wt.t_start)<2 and extract(hour from wt.t_stop)>2 then cast('03:00:00' as time)-cast('02:00:00' as time)
when extract(hour from wt.t_start)<2 then cast(wt.t_stop as time)-cast('02:00:00' as time)
when extract(hour from wt.t_stop)>2 then cast('03:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between02and03,
sum((case
when extract(hour from wt.t_start)<=3 and extract(hour from wt.t_stop)>=3
then case when extract(hour from wt.t_start)<3 and extract(hour from wt.t_stop)>3 then cast('04:00:00' as time)-cast('03:00:00' as time)
when extract(hour from wt.t_start)<3 then cast(wt.t_stop as time)-cast('03:00:00' as time)
when extract(hour from wt.t_stop)>3 then cast('04:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between03and04,
sum((case
when extract(hour from wt.t_start)<=4 and extract(hour from wt.t_stop)>=4
then case when extract(hour from wt.t_start)<4 and extract(hour from wt.t_stop)>4 then cast('05:00:00' as time)-cast('04:00:00' as time)
when extract(hour from wt.t_start)<4 then cast(wt.t_stop as time)-cast('04:00:00' as time)
when extract(hour from wt.t_stop)>4 then cast('05:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between04and05,
sum((case
when extract(hour from wt.t_start)<=5 and extract(hour from wt.t_stop)>=5
then case when extract(hour from wt.t_start)<5 and extract(hour from wt.t_stop)>5 then cast('06:00:00' as time)-cast('05:00:00' as time)
when extract(hour from wt.t_start)<5 then cast(wt.t_stop as time)-cast('05:00:00' as time)
when extract(hour from wt.t_stop)>5 then cast('06:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between05and06,
sum((case
when extract(hour from wt.t_start)<=6 and extract(hour from wt.t_stop)>=6
then case when extract(hour from wt.t_start)<6 and extract(hour from wt.t_stop)>6 then cast('07:00:00' as time)-cast('06:00:00' as time)
when extract(hour from wt.t_start)<6 then cast(wt.t_stop as time)-cast('06:00:00' as time)
when extract(hour from wt.t_stop)>6 then cast('07:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between06and07,
sum((case
when extract(hour from wt.t_start)<=7 and extract(hour from wt.t_stop)>=7
then case when extract(hour from wt.t_start)<7 and extract(hour from wt.t_stop)>7 then cast('08:00:00' as time)-cast('07:00:00' as time)
when extract(hour from wt.t_start)<7 then cast(wt.t_stop as time)-cast('07:00:00' as time)
when extract(hour from wt.t_stop)>7 then cast('08:00:00' as time)-cast(wt.t_start as time)
else cast(wt.t_stop as time)-cast(wt.t_start as time) end
else 0
end)/60)/60 as between07and08
from worktime wt
join w_users u1 on u1.user_id = wt.user_id
where (
cast(wt.t_start as timestamp) > dateadd(7 hour to :d) and
cast(wt.t_stop as timestamp) < dateadd(33 hour to :d)
)
group by
u1.user_name
into
:user_name,
:"08:00 - 09:00",
:"09:00 - 10:00",
:"10:00 - 11:00",
:"11:00 - 12:00",
:"12:00 - 13:00",
:"13:00 - 14:00",
:"14:00 - 15:00",
:"15:00 - 16:00",
:"16:00 - 17:00",
:"17:00 - 18:00",
:"18:00 - 19:00",
:"19:00 - 20:00",
:"20:00 - 21:00",
:"21:00 - 22:00",
:"22:00 - 23:00",
:"23:00 - 00:00",
:"00:00 - 01:00",
:"01:00 - 02:00",
:"02:00 - 03:00",
:"03:00 - 04:00",
:"04:00 - 05:00",
:"05:00 - 06:00",
:"06:00 - 07:00",
:"07:00 - 08:00"
do
begin
suspend;
end
end