Menu
My table has info similar to below
Emp Date START_TIME END_TIME Code Minutes
--- -------- ------------------- ------------------- ---- -------
E1 11/1/2012 11/1/2012 6:55:00 AM 11/1/2012 7:01:00 AM C1 6
E1 11/1/2012 11/1/2012 6:57:00 AM 11/1/2012 8:01:00 AM C2 64
E2 11/1/2012 11/1/2012 6:57:00 AM 11/1/2012 8:00:00 AM C2 63
E1 11/2/2012 11/2/2012 7:35:00 AM 11/2/2012 8:01:00 AM C1 26
Expected Output is
Date Code Range Minutes
--------- ---- ----------------------- -------
11/1/2012 C1 6:30:00 AM-7:00:00 AM 5
11/1/2012 C1 7:00:00 AM-7:30:00 AM 1
11/1/2012 C2 6:30:00 AM-7:00:00 AM 6
11/1/2012 C2 7:00:00 AM-7:30:00 AM 60
11/1/2012 C2 7:30:00 AM-8:00:00 AM 60
11/1/2012 C2 8:00:00 AM-8:30:00 AM 1
11/2/2012 C1 7:30:00 AM-8:00:00 AM 25
11/2/2012 C1 8:00:00 AM-8:30:00 AM 1
Leaving out Emp field, I want to group by date, and code with total time spent in a span of 30 minutes each. And the limitation I have is to achieve this using select statements i.e. only through SQL queries coz PL/SQL is not allowed. Thanks in advance!
411
a solution involving the model clause.
first lets compute the amount of 30 minute blocks we need per entry.
SQL> select emp, start_time, end_time, code,
2 trunc(start_time, 'mi')
3 - (mod(to_char(trunc(start_time, 'mi'), 'mi'), 30) / 1440) start_block,
4 ceil(2*24*(end_time-(trunc(start_time, 'mi')
5 - (mod(to_char(trunc(start_time, 'mi'), 'mi'), 30) / 1440)))) blocks
6 from tab f
7 /
EM START_TIME END_TIME CO START_BLOCK BLOCKS
-- ---------------------- ---------------------- -- ---------------------- ----------
E1 11/01/2012 06:55:00 am 11/01/2012 07:01:00 am C1 11/01/2012 06:30:00 am 2
E1 11/01/2012 06:57:00 am 11/01/2012 08:01:00 am C2 11/01/2012 06:30:00 am 4
E2 11/01/2012 06:57:00 am 11/01/2012 08:00:00 am C2 11/01/2012 06:30:00 am 3
E1 11/02/2012 07:35:00 am 11/02/2012 08:01:00 am C1 11/02/2012 07:30:00 am 2
now, we generate rows to break this into 30 minute periods using the model clause.
SQL> with foo as (select rownum id, emp, start_time, end_time, code,
2 trunc(start_time, 'mi')
3 - (mod(to_char(trunc(start_time, 'mi'), 'mi'), 30) / 1440) start_block,
4 ceil(2*24*(end_time-(trunc(start_time, 'mi')
5 - (mod(to_char(trunc(start_time, 'mi'), 'mi'), 30) / 1440)))) blocks
6 from tab f)
7 select trunc(start_time) thedate, code, emp, range, minutes
8 from foo
9 model partition by(id)
10 dimension by(0 as f)
11 measures(code, emp, start_time, end_time, start_block, blocks,
12 sysdate as start_range,
13 sysdate as end_range,
14 cast(0 as number) minutes,
15 cast('' as varchar2(50)) range)
16 rules (start_range [for f from 0 to blocks[0]-1 increment 1] = start_block[0] + (30*cv(f)/1440),
17 end_range[any] = start_range[cv()] + (30/1440),
18 code[any] = code[0],
19 emp[any] = emp[0],
20 start_time[any] = start_time[0],
21 end_time[any] = end_time[0],
22 range [any] = to_char(start_range[cv()], 'dd/mm/yyyy hh:mi:ss am') || ' - ' || to_char(end_range[cv()], 'dd/mm/yyyy hh24:mi:ss am'),
23 minutes [any] = case
24 when start_time[0] between start_range[cv()] and end_range[cv()]
25 then 1440 *(end_range[cv()] - start_time[0])
26 when end_time[0] between start_range[cv()] and end_range[cv()]
27 then 1440 *(end_time[0] - start_range[cv()])
28 else 1440 * (end_range[cv()] - start_range[cv()])
29 end );
CO EM RANGE MINUTES
-- -- -------------------------------------------------- ----------
C2 E2 11/01/2012 06:30:00 am - 11/01/2012 07:00:00 am 3
C2 E2 11/01/2012 07:00:00 am - 11/01/2012 07:30:00 am 30
C2 E2 11/01/2012 07:30:00 am - 11/01/2012 08:00:00 am 30
C1 E1 11/01/2012 06:30:00 am - 11/01/2012 07:00:00 am 5
C1 E1 11/01/2012 07:00:00 am - 11/01/2012 07:30:00 am 1
C1 E1 11/02/2012 07:30:00 am - 11/02/2012 08:00:00 am 25
C1 E1 11/02/2012 08:00:00 am - 11/02/2012 08:30:00 am 1
C2 E1 11/01/2012 06:30:00 am - 11/01/2012 07:00:00 am 3
C2 E1 11/01/2012 07:00:00 am - 11/01/2012 07:30:00 am 30
C2 E1 11/01/2012 07:30:00 am - 11/01/2012 08:00:00 am 30
C2 E1 11/01/2012 08:00:00 am - 11/01/2012 08:30:00 am 1
11 rows selected.
so we are partitioning by:
partition by(id)
ie by a unique reference. then we are going to generate rows with our dimension
dimension by(0 as f)
in conjuction with part of the rules:
for f from 0 to blocks[0]-1 increment 1
so the start_range column is generated with start_range [for f from 0 to blocks[0]-1 increment 1] = start_block[0] + (30*cv(f)/1440),
start_block[0] is in the first query, eg:
EM START_TIME END_TIME CO START_BLOCK BLOCKS
-- ---------------------- ---------------------- -- ---------------------- ----------
E1 11/01/2012 06:55:00 am 11/01/2012 07:01:00 am C1 11/01/2012 06:30:00 am 2
so for this row, it evaluates to
start_range[0 to 1] = 11/01/2012 06:30:00 am + (30minutes * the value of f)
i.e.
start_range[0] = 11/01/2012 06:30:00 am + (30min*0) = 11/01/2012 06:30:00 am
start_range[1] = 11/01/2012 06:30:00 am + (30min*1) = 11/01/2012 07:00:00 am
the rest is pretty straight forward:
end_range[any] = start_range[cv()] + (30/1440),
means that for end-range on the current row, we take start_range and add 30 minutes.
the range column is a concatenation of start_range and end_range:
range [any] = to_char(start_range[cv()], 'dd/mm/yyyy hh:mi:ss am') || ' - ' || to_char(end_range[cv()], 'dd/mm/yyyy hh24:mi:ss am'),
finally in order to calc minutes in that range:
minutes [any] = case
when start_time[0] between start_range[cv()] and end_range[cv()]
then 1440 *(end_range[cv()] - start_time[0])
when end_time[0] between start_range[cv()] and end_range[cv()]
then 1440 *(end_time[0] - start_range[cv()])
else 1440 * (end_range[cv()] - start_range[cv()])
end );
1440 just gets the answer as minutes.
now we can just group that all up:
SQL> with foo as (select rownum id, emp, start_time, end_time, code,
2 trunc(start_time, 'mi')
3 - (mod(to_char(trunc(start_time, 'mi'), 'mi'), 30) / 1440) start_block,
4 ceil(2*24*(end_time-(trunc(start_time, 'mi')
5 - (mod(to_char(trunc(start_time, 'mi'), 'mi'), 30) / 1440)))) blocks
6 from tab f)
7 select thedate, code, range, sum(minutes) minutes
8 from (select trunc(start_time) thedate, code, emp, range, minutes
9 from foo
10 model partition by(id)
11 dimension by(0 as f)
12 measures(code, emp, start_time, end_time, start_block, blocks,
13 sysdate as start_range,
14 sysdate as end_range,
15 cast(0 as number) minutes,
16 cast('' as varchar2(50)) range)
17 rules (start_range [for f from 0 to blocks[0]-1 increment 1] = start_block[0] + (30*cv(f)/1440),
18 code[any] = code[0],
19 emp[any] = emp[0],
20 end_range[any] = start_range[cv()] + (30/1440),
21 start_time[any] = start_time[0],
22 end_time[any] = end_time[0],
23 range [any] = to_char(start_range[cv()], 'dd/mm/yyyy hh:mi:ss am') || ' - ' || to_char(end_range[cv()], 'dd/mm/yyyy hh24:mi:ss am'),
24 minutes [any] = case
25 when start_time[0] between start_range[cv()] and end_range[cv()]
26 then 1440 *(end_range[cv()] - start_time[0])
27 when end_time[0] between start_range[cv()] and end_range[cv()]
28 then 1440 *(end_time[0] - start_range[cv()])
29 else 1440 * (end_range[cv()] - start_range[cv()])
30 end ))
31 group by thedate, code, range
32 order by thedate, code, range;
THEDATE CO RANGE MINUTES
---------- -- -------------------------------------------------- ----------
11/01/2012 C1 11/01/2012 06:30:00 am - 11/01/2012 07:00:00 am 5
11/01/2012 C1 11/01/2012 07:00:00 am - 11/01/2012 07:30:00 am 1
11/01/2012 C2 11/01/2012 06:30:00 am - 11/01/2012 07:00:00 am 6
11/01/2012 C2 11/01/2012 07:00:00 am - 11/01/2012 07:30:00 am 60
11/01/2012 C2 11/01/2012 07:30:00 am - 11/01/2012 08:00:00 am 60
11/01/2012 C2 11/01/2012 08:00:00 am - 11/01/2012 08:30:00 am 1
11/02/2012 C1 11/02/2012 07:30:00 am - 11/02/2012 08:00:00 am 25
11/02/2012 C1 11/02/2012 08:00:00 am - 11/02/2012 08:30:00 am 1
I am pretty sure this can be cleaned up, and made more legible and more efficient as Oracle is not one of my strong suits, but it works and should give an idea of how to accomplish the task.
The key here is joining to a list of numbers to break up your records into half hour periods.
SELECT "Date",
"Code",
"RangeStart" + ((r - 1) / 48.0) AS "RangeStart",
"RangeStart" + (r / 48.0) AS "RangeEnd",
SUM(CASE WHEN r = 1 THEN "StartMinutes"
WHEN "END_TIME" >= "RangeStart" + ((r - 1) / 48.0) AND "END_TIME" < "RangeStart" + (r / 48.0) THEN "EndMinutes"
ELSE 30
END) AS "TotalMinutes"
FROM ( SELECT "Emp",
"Date",
"START_TIME",
"END_TIME",
"Code",
CASE WHEN EXTRACT(MINUTE from "START_TIME") > 30 THEN 60 ELSE 30 END - EXTRACT(MINUTE from "START_TIME") AS "StartMinutes",
EXTRACT(MINUTE from END_TIME) - CASE WHEN EXTRACT(MINUTE from "END_TIME") > 30 THEN 30 ELSE 0 END AS "EndMinutes",
"START_TIME" - (EXTRACT(MINUTE from "START_TIME") - CASE WHEN EXTRACT(MINUTE from "START_TIME") > 30 THEN 30 ELSE 0 END) / (60 * 24.0) AS "RangeStart"
FROM T
) T
INNER JOIN
( SELECT Rownum r
FROM dual
CONNECT BY Rownum <= 100
) r
ON "END_TIME" > ("RangeStart" + ((r - 1) / 48.0))
GROUP BY "Date", "Code", "RangeStart" + ((r - 1) / 48.0), "RangeStart" + (r / 48.0)
ORDER BY "Code", "Date", "RangeStart";
EXAMPLE ON SQL FIDDLE
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
