Home » SQL & PL/SQL » SQL & PL/SQL » peak Off Peak
peak Off Peak [message #228898] Wed, 04 April 2007 08:50 Go to next message
deni_sa
Messages: 77
Registered: June 2006
Member
Hi ,

i have to create a query that shows data for the peak hours( from 6 till 23 ) and for off peak hours ( wich are 23 till 6 in the morning)

i have done this query but i don't think is correct , please can you help me ???

----------------------------------------------------------------------------------------------------------------------------------
SELECT   COUNT(uds_record_id), SUM(duration_volume)/60, SUM(rated_flat_amount) 
FROM udr_oct_2006
WHERE tariff_info_sncode =1
AND xfile_ind ='H'
AND call_type=1
AND (TO_CHAR(((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24),'HH24')) NOT BETWEEN 6 AND 23
AND ( o_p_normed_num_address LIKE '39310%'or o_p_normed_num_address  like '39390%' OR o_p_normed_num_address   like '39391%' OR o_p_normed_num_address   like '39392%' OR o_p_normed_num_address like '39393%'  OR o_p_normed_num_address  like '39397%') 



Thank you Denisa

[Updated on: Wed, 04 April 2007 08:51] by Moderator

Report message to a moderator

Re: peak Off Peak [message #228910 is a reply to message #228898] Wed, 04 April 2007 09:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Something like (but it depends on your column datatypes):
SELECT   
case when to_number(TO_CHAR(((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24),'HH24'))) 
            between 6 and 23 then 'Peak'
else 'Off peak' end period, 
COUNT(uds_record_id), SUM(duration_volume)/60, SUM(rated_flat_amount) 
FROM udr_oct_2006
WHERE tariff_info_sncode =1
AND xfile_ind ='H'
AND call_type=1
AND ( o_p_normed_num_address LIKE '39310%'or 
o_p_normed_num_address  like '39390%' OR 
o_p_normed_num_address   like '39391%' OR 
o_p_normed_num_address   like '39392%' OR 
o_p_normed_num_address like '39393%'  OR 
o_p_normed_num_address  like '39397%') 
group by 
case when to_number(TO_CHAR(((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24),'HH24'))) 
            between 6 and 23 then 'Peak'
else 'Off peak' end 

Regards
Michel
Re: peak Off Peak [message #228917 is a reply to message #228910] Wed, 04 April 2007 09:19 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
Dear Michel the first is date the second ( offset ) is number and is 3600
Re: peak Off Peak [message #228935 is a reply to message #228917] Wed, 04 April 2007 10:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

3600 means 3600 seconds or hotdogs? Smile

Regards
Michel
Re: peak Off Peak [message #228938 is a reply to message #228935] Wed, 04 April 2007 10:47 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
Hotdogs Of course ...I 'm very hungry now Smile



Seconds , they are seconds
Re: peak Off Peak [message #228941 is a reply to message #228938] Wed, 04 April 2007 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So try my statement (and check parenthesis I didn't).

Regards
Michel
Re: peak Off Peak [message #229123 is a reply to message #228941] Thu, 05 April 2007 08:28 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
Dear Michael ,

I can use this query instead of that you send me ...i can't execute that one .
Re: peak Off Peak [message #229124 is a reply to message #229123] Thu, 05 April 2007 08:28 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
SELECT COUNT(uds_record_id), SUM(duration_volume)/60, SUM(rated_flat_amount)
FROM udr_sep_2006
WHERE tariff_info_sncode =1
AND xfile_ind ='H'
AND call_type=1
AND TO_CHAR(((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24) ,'Mon-YYYY') = 'sep-2006'
AND (TO_CHAR(((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24),'HH24')) BETWEEN 6 AND 23
AND ( o_p_normed_num_address LIKE '39310%'or
o_p_normed_num_address like '39390%' OR
o_p_normed_num_address like '39391%' OR
o_p_normed_num_address like '39392%' OR
o_p_normed_num_address like '39393%' OR
o_p_normed_num_address like '39397%') ;
Re: peak Off Peak [message #229125 is a reply to message #229124] Thu, 05 April 2007 08:33 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
No, no, no, no, no, no, no!!!!

How can a character string ever be between 6 and 23. It's impossible.
Re: peak Off Peak [message #229127 is a reply to message #229125] Thu, 05 April 2007 08:35 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
Sad(( what shoul i do then ?
Re: peak Off Peak [message #229135 is a reply to message #229127] Thu, 05 April 2007 09:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know why you can't execute my query, you don't tell but note that I use TO_NUMBER to convert strings to numbers and use BETWEEN.

Regards
Michel
To_TIME FUnction [message #231797 is a reply to message #229135] Wed, 18 April 2007 07:51 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
SOrry what is the format of the to_time function .I'm Tring to build a query with this function but i recive an error .

Can you help me please ?
Re: To_TIME FUnction [message #231799 is a reply to message #231797] Wed, 18 April 2007 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no to_time function.
Maybe this is the reason you get an error... that you still don't post.

Regards
Michel
Re: To_TIME FUnction [message #231803 is a reply to message #231797] Wed, 18 April 2007 08:01 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
deni_sa wrote on Wed, 18 April 2007 14:51
SOrry what is the format of the to_time function .I'm Tring to build a query with this function but i receive an error.

Can you help me please ?


I don't think there's a built-in function called 'to_time' in Oracle.

Red: What error?
- ORA-75866: User Hungry.
- ORA-55233: Unable to start car. Check fuel.


MHE
Re: To_TIME FUnction [message #231804 is a reply to message #231797] Wed, 18 April 2007 08:02 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
Thank you !
Re: To_TIME FUnction [message #231816 is a reply to message #231803] Wed, 18 April 2007 08:45 Go to previous message
deni_sa
Messages: 77
Registered: June 2006
Member
Smile)


the error message is to_time = unkown identifier
Previous Topic: Regarding Error with Trigger
Next Topic: SQL CASE statement (revised to follow board rules)
Goto Forum:
  


Current Time: Sun Dec 04 22:56:28 CST 2016

Total time taken to generate the page: 0.04766 seconds