afordFlashpoke, я не знаю как применить вашу процедуру к моей базе и что куда вставить скопировать...Печально.
Процедуры будет две.
Вспомогательная процедура 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.
CREATE OR ALTER PROCEDURE SPLIT_WORKTIME (
START_DATE DATE,
END_DATE DATE)
RETURNS (
USER_ID INTEGER,
T_START TIMESTAMP,
T_STOP TIMESTAMP)
AS
DECLARE VARIABLE TEMP_STOP TIMESTAMP;
DECLARE VARIABLE SPLIT_TIME TIMESTAMP;
BEGIN
FOR SELECT
USER_ID,
T_START,
T_STOP
FROM WORKTIME
WHERE T_START BETWEEN :START_DATE AND :END_DATE
INTO :USER_ID,
:T_START,
:T_STOP
DO
BEGIN
IF (DATEDIFF(HOUR, T_START, T_STOP) > 0) THEN
BEGIN
SPLIT_TIME = CAST(T_START AS DATE);
SPLIT_TIME = DATEADD(HOUR, EXTRACT(HOUR FROM T_START), SPLIT_TIME);
SPLIT_TIME = DATEADD(HOUR, 1, SPLIT_TIME);
TEMP_STOP = T_STOP;
T_STOP = SPLIT_TIME;
SUSPEND;
WHILE (SPLIT_TIME < TEMP_STOP) DO
BEGIN
T_START = SPLIT_TIME;
SPLIT_TIME = DATEADD(HOUR, 1, SPLIT_TIME);
T_STOP = MINVALUE(SPLIT_TIME, TEMP_STOP);
SUSPEND;
END
END
ELSE
SUSPEND;
END
END
Основная процедура 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.
CREATE OR ALTER PROCEDURE GROUP_TIME (
USER_ID INTEGER,
D DATE)
RETURNS (
USER_NAME VARCHAR(100),
"08:00 - 09:00" DOUBLE PRECISION,
"09:00 - 10:00" DOUBLE PRECISION,
"10:00 - 11:00" DOUBLE PRECISION,
"11:00 - 12:00" DOUBLE PRECISION,
"12:00 - 13:00" DOUBLE PRECISION,
"13:00 - 14:00" DOUBLE PRECISION,
"14:00 - 15:00" DOUBLE PRECISION,
"15:00 - 16:00" DOUBLE PRECISION,
"16:00 - 17:00" DOUBLE PRECISION,
"17:00 - 18:00" DOUBLE PRECISION,
"18:00 - 19:00" DOUBLE PRECISION,
"19:00 - 20:00" DOUBLE PRECISION,
"20:00 - 21:00" DOUBLE PRECISION,
"21:00 - 22:00" DOUBLE PRECISION,
"22:00 - 23:00" DOUBLE PRECISION,
"23:00 - 00:00" DOUBLE PRECISION,
"00:00 - 01:00" DOUBLE PRECISION,
"01:00 - 02:00" DOUBLE PRECISION,
"02:00 - 03:00" DOUBLE PRECISION,
"03:00 - 04:00" DOUBLE PRECISION,
"04:00 - 05:00" DOUBLE PRECISION,
"05:00 - 06:00" DOUBLE PRECISION,
"06:00 - 07:00" DOUBLE PRECISION,
"07:00 - 08:00" DOUBLE PRECISION)
AS
DECLARE VARIABLE CUR_DAY SMALLINT;
DECLARE VARIABLE NEXT_DAY SMALLINT;
DECLARE VARIABLE MINHOUR DOUBLE PRECISION;
BEGIN
CUR_DAY = EXTRACT(DAY FROM :D);
NEXT_DAY = EXTRACT(DAY FROM (:D + 1));
MINHOUR = 1;
MINHOUR = MINHOUR / 60;
SELECT USER_NAME FROM W_USERS WHERE USER_ID = :USER_ID INTO :USER_NAME;
SELECT
SUM(CASE WHEN W_DAY = :CUR_DAY AND W_HOUR = 8 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :CUR_DAY AND W_HOUR = 9 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :CUR_DAY AND W_HOUR = 10 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :CUR_DAY AND W_HOUR = 11 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :CUR_DAY AND W_HOUR = 12 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :CUR_DAY AND W_HOUR = 13 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :CUR_DAY AND W_HOUR = 14 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :CUR_DAY AND W_HOUR = 15 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :CUR_DAY AND W_HOUR = 16 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :CUR_DAY AND W_HOUR = 17 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :CUR_DAY AND W_HOUR = 18 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :CUR_DAY AND W_HOUR = 19 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :CUR_DAY AND W_HOUR = 20 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :CUR_DAY AND W_HOUR = 21 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :CUR_DAY AND W_HOUR = 22 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :CUR_DAY AND W_HOUR = 23 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :NEXT_DAY AND W_HOUR = 0 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :NEXT_DAY AND W_HOUR = 1 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :NEXT_DAY AND W_HOUR = 2 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :NEXT_DAY AND W_HOUR = 3 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :NEXT_DAY AND W_HOUR = 4 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :NEXT_DAY AND W_HOUR = 5 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :NEXT_DAY AND W_HOUR = 6 THEN W_INTERVAL * :MINHOUR ELSE 0 END),
SUM(CASE WHEN W_DAY = :NEXT_DAY AND W_HOUR = 7 THEN W_INTERVAL * :MINHOUR ELSE 0 END)
FROM (SELECT
USER_ID,
EXTRACT(DAY FROM T_START) W_DAY,
EXTRACT(HOUR FROM T_START) W_HOUR,
SUM(DATEDIFF(MINUTE, T_START, T_STOP)) W_INTERVAL
FROM SPLIT_WORKTIME(:D, DATEADD(DAY, 1, :D))
GROUP BY 1, 2, 3)
INTO
:"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";
SUSPEND;
END
|
