OraFAQ Forum: SQL & PL/SQL » Date in Group By Clause

Home » SQL & PL/SQL » SQL & PL/SQL » Date in Group By Clause

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Date in Group By Clause [message #192174]

Mon, 11 September 2006 06:22

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)

Report message to a moderator

Re: Date in Group By Clause [message #192181 is a reply to message #192174]

Mon, 11 September 2006 06:45

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

Frank
Messages: 7901
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.

Report message to a moderator

Re: Date in Group By Clause [message #192191 is a reply to message #192174]

Mon, 11 September 2006 07:19

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

Report message to a moderator

Re: Date in Group By Clause [message #192199 is a reply to message #192174]

Mon, 11 September 2006 07:30

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

Report message to a moderator

Previous Topic:	Sql Insert with PK and Fk
Next Topic:	SQL Query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 19:27:47 CST 2024