Home » SQL & PL/SQL » SQL & PL/SQL » Rounding to hour problem
Rounding to hour problem [message #22227] Mon, 30 September 2002 06:31 Go to next message
Jaap
Messages: 1
Registered: September 2002
Junior Member
Hello there,

Already for a long, long time I'm wrestling with a query. I need to retrieve the hour (of a day) which is most popular to make a call.

Therefore I use a Time-column in which the time is stored as MM-DD-YY HH24:MI:SS. I need to round the hours of the records, but this is not working.

I succeed in retrieving the hour in which most calls are made, however... the hours are not rounded. For example. 15:35:00 should be rounded to 16 (and that is exactly what does not work). I thought it should work with a ROUND(TIME, 'HH24'), but somehow it just does not work.

This is my query.. can anyone see what I'm doing wrong?

SELECT TO_CHAR(StartTime, 'HH24') as HourMostCalled
FROM Calls
GROUP BY TO_CHAR(StartTime, 'HH24')
HAVING COUNT(ROUND(TO_DATE(StartTime), 'HH24')) =
(SELECT MAX(COUNT(ROUND(TO_DATE(StartTime), 'HH24'))) as MAXTotalCallsHourMostCalled
FROM Calls
GROUP BY TO_CHAR(StartTime, 'HH24'));

Thanks for your help!

Report message to a moderator

Re: Rounding to hour problem [message #22231 is a reply to message #22227] Mon, 30 September 2002 11:02 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
I'm not sure why you want to round up your hours. If a call is made at 3:35pm, that is a call made in the 3:00pm hour - not the 4:00pm hour. I believe what you want to do is TRUNC the times to the hour (round down within the hour).

3:15pm => 3:00pm
3:35pm => 3:00pm


select to_char(trunc(starttime, 'hh'), 'hh24') hour_most_called
  from calls
 group by to_char(trunc(starttime, 'hh'), 'hh24')
 having count(*) = (select max(count(*))
                      from calls
                     group by to_char(trunc(starttime, 'hh'), 'hh24'));

Report message to a moderator

Re: Rounding to hour problem [message #22236 is a reply to message #22227] Mon, 30 September 2002 19:13 Go to previous messageGo to next message
jiltin
Messages: 44
Registered: September 2002
Member
The following query works.
===================================
SELECT (round(starttime,'HH')-trunc(starttime))*24 as HourMostCalled
FROM Calls
GROUP BY (round(starttime,'HH')-trunc(starttime))*24
HAVING COUNT(ROUND(TO_DATE(StartTime), 'HH24')) =
(SELECT MAX(COUNT(ROUND(TO_DATE(StartTime), 'HH24'))) as MAXTotalCallsHourMostCalled
FROM Calls
GROUP BY TO_CHAR(StartTime, 'HH24'));
==========================

jiltin

Report message to a moderator

Re: Rounding to hour problem [message #22253 is a reply to message #22227] Tue, 01 October 2002 10:49 Go to previous messageGo to next message
jiltin
Messages: 44
Registered: September 2002
Member
SELECT MAX((round(starttime,'HH')-trunc(starttime))*24) as MAXTotalCallsHourMostCalled
FROM Calls

this also works

Report message to a moderator

Re: Rounding to hour problem [message #22255 is a reply to message #22253] Tue, 01 October 2002 11:22 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
jiltin, neither of your posted queries are correct.

Your first statement is rounding every time up or down. Yes, a call made at 10:15 is a call in the 10:00 hour, but a call made at 10:45 is NOT a call in the 11:00 hour - it is still a call in the 10:00 hour.

Your second statement - with the same rounding error - simply returns the latest hour in which a call was made regardless of how many calls were placed in that hour.

Report message to a moderator

Re: Rounding to hour problem [message #22260 is a reply to message #22253] Tue, 01 October 2002 19:15 Go to previous messageGo to next message
jiltin
Messages: 44
Registered: September 2002
Member
I tested the following and it gave me 20 (8 PM)
You can also try using the values.

Jiltin

++++++++++++++++++++++++++++++
DROP TABLE CALLS CASCADE CONSTRAINTS ;

CREATE TABLE CALLS (
STARTTIME DATE) TABLESPACE USERS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE;


INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 07:42:43 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 07:43:05 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 07:43:07 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 07:43:08 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 07:45:04 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 07:45:05 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 07:43:12 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 07:43:13 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 07:42:19 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 07:42:20 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 07:46:26 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 07:46:26 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 09:46:21 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 09:46:23 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 09:46:24 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 05:46:29 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 05:46:30 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 05:46:32 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 06:46:46 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 06:46:47 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 08:46:52 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 08:46:52 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 08:00:09 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO CALLS ( STARTTIME ) VALUES ( TO_Date( '09/30/2002 08:00:10 AM', 'MM/DD/YYYY HH:MI:SS AM'));

SELECT (round(starttime,'HH')-trunc(starttime))*24 as HourMostCalled
FROM Calls
GROUP BY (round(starttime,'HH')-trunc(starttime))*24
HAVING COUNT(ROUND(TO_DATE(StartTime), 'HH24')) =
(SELECT MAX(COUNT(ROUND(TO_DATE(StartTime), 'HH24'))) as MAXTotalCallsHourMostCalled
FROM Calls
GROUP BY TO_CHAR(StartTime, 'HH24'));

++++++++++++++++++++++++++++++

Report message to a moderator

Re: Rounding to hour problem [message #22281 is a reply to message #22260] Wed, 02 October 2002 11:57 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
jiltin, add these values to your table and see what your query returns:

insert into CALLS ( STARTTIME ) values ( to_date( '09/30/2002 12:12:00 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
insert into CALLS ( STARTTIME ) values ( to_date( '09/30/2002 12:13:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
insert into CALLS ( STARTTIME ) values ( to_date( '09/30/2002 12:14:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
insert into CALLS ( STARTTIME ) values ( to_date( '09/30/2002 12:15:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
insert into CALLS ( STARTTIME ) values ( to_date( '09/30/2002 12:16:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
insert into CALLS ( STARTTIME ) values ( to_date( '09/30/2002 12:17:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
insert into CALLS ( STARTTIME ) values ( to_date( '09/30/2002 12:18:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
insert into CALLS ( STARTTIME ) values ( to_date( '09/30/2002 12:19:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
insert into CALLS ( STARTTIME ) values ( to_date( '09/30/2002 12:20:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
insert into CALLS ( STARTTIME ) values ( to_date( '09/30/2002 12:21:00 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
insert into CALLS ( STARTTIME ) values ( to_date( '09/30/2002 12:22:00 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
insert into CALLS ( STARTTIME ) values ( to_date( '09/30/2002 12:23:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
insert into CALLS ( STARTTIME ) values ( to_date( '09/30/2002 12:24:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
insert into CALLS ( STARTTIME ) values ( to_date( '09/30/2002 12:25:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
insert into CALLS ( STARTTIME ) values ( to_date( '09/30/2002 12:26:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
insert into CALLS ( STARTTIME ) values ( to_date( '09/30/2002 12:27:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
insert into CALLS ( STARTTIME ) values ( to_date( '09/30/2002 12:28:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
insert into CALLS ( STARTTIME ) values ( to_date( '09/30/2002 12:29:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
insert into CALLS ( STARTTIME ) values ( to_date( '09/30/2002 12:30:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));


It does not handle calls in that hour. We also disagree on whether a time should be TRUNCed or ROUNDed - I still vote for TRUNC. Whether a call is in the first half-hour or the second, it is still in that hour.

I will still stick with:

select to_char(trunc(starttime, 'hh'), 'hh24') hour_most_called 
  from calls 
 group by to_char(trunc(starttime, 'hh'), 'hh24') 
 having count(*) = (select max(count(*))                      
                      from calls                     
                     group by to_char(trunc(starttime, 'hh'), 'hh24'));

Report message to a moderator

Previous Topic: not existing table when compile a package
Next Topic: INSTR Help for Exact matching
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon May 06 14:46:21 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.