You Asked
Hi.
I have a question about grouping query result in 15 minutes interval from two tables.
I already have the query which gives me quite normal result, but I have two issues
SCHNAME.TABLE1 as T1: NAME, ID2, ID
SCHNAME.TABLE2 as T2: IDNAME, TIME1 as INTERVAL1, NUM as QUANTITY1
SCHNAME.TABLE3 as T3: AID, TIME2 as INTERVAL2, QUANTITY2
T1.ID = T2.IDNAME
T1.ID2 = T3.AID
T2.INTERVAL1=T3.INTERVAL2
The query
SELECT T1.NAME, T1.ID, T3.INTERVAL2, T2.QUANTITY1, T3.QUANTITY2 FROM
(SELECT NAME, ID2, ID FROM SCHNAME.TABLE1) T1
INNER JOIN
(SELECT IDNAME, TRUNC(TIME1,'HH')+((Round((TO_NUMBER(to_char(TIME1,'MI'))/15),0)*15)/1440) AS INTERVAL1, COUNT(DISTINCT NUM) QUANTITY1
FROM SCHNAME.TABLE2
WHERE TIME1 BETWEEN TRUNC(SYSDATE-1)+9/24 AND TRUNC(SYSDATE-1)+20/24
GROUP BY IDNAME, TRUNC(TIME1,'HH')+((Round((TO_NUMBER(to_char(TIME1,'MI'))/15),0)*15)/1440)) T2
ON T1.ID = T2.IDNAME
INNER JOIN
(SELECT AID, TRUNC(TIME2,'HH')+((Round((TO_NUMBER(to_char(TIME2,'MI'))/15),0)*15)/1440) AS INTERVAL2, COUNT(*) QUANTITY2
FROM SCHNAME.TABLE3
WHERE CALLEDNO='132' AND TIME2 BETWEEN TRUNC(SYSDATE-1)+9/24 AND TRUNC(SYSDATE-1)+20/24
GROUP BY AID, TRUNC(TIME2,'HH')+((Round((TO_NUMBER(to_char(TIME2,'MI'))/15),0)*15)/1440)) T3
ON T1.ID2 = T3.AID
WHERE T1.ID='scooter' AND T2.INTERVAL1=T3.INTERVAL2
ORDER BY T3.INTERVAL2
As you can see I use Round function which rounds result to the first occasion of the TIME1:
if the first occassion of TIME1 is 09:09:00, the Round function start to round and count from 09:09:00 minutes but I need 09:00:00
The time intervals are quite correct though, the counting is wrong (I also did counting using TRUNC(TIME1,'MI') which gives me exact minute of the occasion)
That's the first problem I can't resolve
The second problem is
I use T2.INTERVAL1=T3.INTERVAL2 to INNER JOIN two table
But if I have 32 results from first INNER JOIN and 36 results from second INNER JOIN the overall result is 32 rows
I need zeros if the there are no countable items
and Chris said...
It really helps if you can provide sample data. It makes answering questions like this much easier!
I believe the first issue is because you're rounding rather than truncing.
Using round, 9:09 -> 09:15 because this is the nearest 15 minutes.
With trunc 9:09 -> 09:00.
For the second part, if you have rows in one table you want to show when there's nothing in the other, then you need to do an outer join.
create table t1 as
select trunc(sysdate)+548/1440+rownum/1440 d1
from dual connect by level <= 10;
create table t2 as
select trunc(sysdate)+548/1440+rownum/1440 d2
from dual connect by level <= 5;
select d1,
trunc(d1, 'hh24') +
round (
to_char (
d1,'MI'
) / 15
) * 15
/ 1440
rd,
trunc(d1, 'hh24') +
trunc (
to_char (
d1,'MI'
) / 15
) * 15
/ 1440 trnc
from t1;
D1 RD TRNC
----------------- ----------------- -----------------
10-FEB-2016 09:09 10-FEB-2016 09:15 10-FEB-2016 09:00
10-FEB-2016 09:10 10-FEB-2016 09:15 10-FEB-2016 09:00
10-FEB-2016 09:11 10-FEB-2016 09:15 10-FEB-2016 09:00
10-FEB-2016 09:12 10-FEB-2016 09:15 10-FEB-2016 09:00
10-FEB-2016 09:13 10-FEB-2016 09:15 10-FEB-2016 09:00
10-FEB-2016 09:14 10-FEB-2016 09:15 10-FEB-2016 09:00
10-FEB-2016 09:15 10-FEB-2016 09:15 10-FEB-2016 09:15
10-FEB-2016 09:16 10-FEB-2016 09:15 10-FEB-2016 09:15
10-FEB-2016 09:17 10-FEB-2016 09:15 10-FEB-2016 09:15
10-FEB-2016 09:18 10-FEB-2016 09:15 10-FEB-2016 09:15
select trunc(d1, 'hh24') +
trunc (
to_char (
d1,'MI'
) / 15
) * 15
/ 1440 t, count(d2)
from t1
join t2
on t1.d1 = t2.d2
group by trunc(d1, 'hh24') +
trunc (
to_char (
d1,'MI'
) / 15
) * 15
/ 1440;
T COUNT(D2)
----------------- ----------
10-FEB-2016 09:00 5
select trunc(d1, 'hh24') +
trunc (
to_char (
d1,'MI'
) / 15
) * 15
/ 1440 t, count(d2)
from t1
left join t2
on t1.d1 = t2.d2
group by trunc(d1, 'hh24') +
trunc (
to_char (
d1,'MI'
) / 15
) * 15
/ 1440;
T COUNT(D2)
----------------- ----------
10-FEB-2016 09:00 5
10-FEB-2016 09:15 0
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment