|
Вычисление среднего значения за час
#39629126
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Flashpoke, я не знаю как применить вашу процедуру к моей базе и что куда вставить скопировать.... вот моя целиком:
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.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
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
Результата так и не добился, надо считать с 8 утра до 8 утра следующего дня. На данный момент, если сотрудник работает, допустим, с 20:00 до 01:00 без перерывов - ему не правильно посчитается статус. Пробовал задавать промежутки без EXTRACT - не правильно считает. |
|
|
