Home » SQL & PL/SQL » SQL & PL/SQL » Date in Group By Clause
Date in Group By Clause [message #192174] Mon, 11 September 2006 06:22 Go to next message
rahul.priyadarshy
Messages: 28
Registered: December 2005
Junior Member
I'm getting a problem while using the date in Group by clause. When i'm executing it for a single day its working fine however, if i'm executing it for a month its fetching up all the data regardless of time in where clause:-

SELECT TO_CHAR(TRUNC(CALL_DATETIME),'DDMMYY') CALL_DATE,
ORIG_NO,
DEST_NO,
SUM(DECODE(CALL_COMPLETION,'001',1,0)) ANSWERED,
SUM(DECODE(CALL_COMPLETION,'006',1,0)) RINGING,
SUM(DECODE(CALL_COMPLETION,'007',1,0)) BUSY,
SUM(DECODE(CALL_COMPLETION,'016',1,0)) OTHER,
AVG(TTANS) AVG_TTANS,
MAX(TTANS) PEAK_TTANS,
AVG(TTABN) AVG_TTABN,
MAX(TTABN) PEAK_TTABN
from T1
WHERE call_datetime between to_date('01-aug-2006 12:00:00' , 'DD-MON-YYYY HH24:MI:SS') and to_date('31-aug-2006 12:59:59' , 'DD-MON-YYYY HH24:MI:SS')
GROUP BY ORIG_NO,DEST_NO,trunc(CALL_DATETIME)
Re: Date in Group By Clause [message #192181 is a reply to message #192174] Mon, 11 September 2006 06:45 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

Plz give the table description and the insert statement and explain your problem with some examples.

And one more thing plz have a look at this statement(time)

to_date('31-aug-2006 12:59:59' , 'DD-MON-YYYY HH24:MI:SS') 


With Regards
Venkat.

[Updated on: Mon, 11 September 2006 06:47]

Report message to a moderator

Re: Date in Group By Clause [message #192188 is a reply to message #192174] Mon, 11 September 2006 07:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
WHERE call_datetime between to_date('01-aug-2006 12:00:00' , 'DD-MON-YYYY HH24:MI:SS') and to_date('31-aug-2006 12:59:59' , 'DD-MON-YYYY HH24:MI:SS') 


Do you expect only records with a time-component between 12:00 and 13:00? If so, this is NOT the way to do that.
Re: Date in Group By Clause [message #192191 is a reply to message #192174] Mon, 11 September 2006 07:19 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
WHERE call_datetime between to_date('01-aug-2006', 'DD-MON-YYYY')
and to_date('31-aug-2006 12:59:59' , 'DD-MON-YYYY HH24:MI:SS')
AND to_char(call_datetime,'HH24MISS') between '120000'
and '125959'

the "where" limits which days you'll look at. the "and" limits which hours of those days

- shoblock
Re: Date in Group By Clause [message #192199 is a reply to message #192174] Mon, 11 September 2006 07:30 Go to previous message
goudelly
Messages: 52
Registered: August 2006
Location: India
Member

Hi,

The query like this:

SELECT TO_CHAR(TRUNC(CALL_DATETIME),'DDMMYY') CALL_DATE,
ORIG_NO,
DEST_NO,
SUM(DECODE(CALL_COMPLETION,'001',1,0)) ANSWERED,
SUM(DECODE(CALL_COMPLETION,'006',1,0)) RINGING,
SUM(DECODE(CALL_COMPLETION,'007',1,0)) BUSY,
SUM(DECODE(CALL_COMPLETION,'016',1,0)) OTHER,
AVG(TTANS) AVG_TTANS,
MAX(TTANS) PEAK_TTANS,
AVG(TTABN) AVG_TTABN,
MAX(TTABN) PEAK_TTABN
from T1
WHERE call_datetime between to_date('01-aug-2006 12:00:00' , 'DD-MON-YYYY HH24:MI:SS') and to_date('31-aug-2006 12:59:59' , 'DD-MON-YYYY HH24:MI:SS')
GROUP BY CALL_DATE, ORIG_NO, DEST_NO
This will work for you,

Mohan Reddy G
Previous Topic: Sql Insert with PK and Fk
Next Topic: SQL Query
Goto Forum:
  


Current Time: Thu Dec 08 02:13:02 CST 2016

Total time taken to generate the page: 0.06774 seconds