Hi all!
I have to calculate working staff time by hours. For example:
table:
USER_ID | T_START | T_STOP
Empl1 | 03-26-2018 08:00:11 | 03-26-2018 11:27:48
Empl1 | 03-26-2018 11:27:53 | 03-26-2018 11:31:54
Empl1 | 03-26-2018 11:31:54 | 03-26-2018 11:42:50
Empl1 | 03-26-2018 12:59:00 | 03-26-2018 13:24:00
Empl1 | 03-26-2018 22:55:00 | 04-26-2018 06:25:00
Empl1 | 04-26-2018 06:40:00 | 03-26-2018 08:01:02
query result:
USER_ID | 08:00-09:00 | 09:00-10:00 | 10:00-11:00 | 11:00-12:00 | 12:00-13:00 | 13:00-14:00 | and then until 8 am
Empl1 | minutes in hour | min in hour | min in hour | min in hour | min in hour | min in hour | ...
The period of calculation is from 8 am of the set date to 8 am the next day.
Now I have this code:
Blockquote
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
Blockquote
But this solution is not enough, because if user worked from 8 pm to 2 am then code does not work correctly.
Please, help