If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register or Login
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

The period of calculation is from 8 am of the set date to 8 am the next day.
Now I have this code:

Code:

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

But this solution is not enough, because if user worked from 8 pm to 2 am then code does not work correctly.
Please, help

Advertiser Disclosure:
Some of the products that appear on this site are from companies from which QuinStreet receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. QuinStreet does not include all companies or all types of products available in the marketplace.