Home » SQL & PL/SQL » SQL & PL/SQL » reg date function (Oracle 9i)
reg date function [message #340601] Wed, 13 August 2008 06:38 Go to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
I need a help for the scenario which is defined below
1.There is a code in which between the start_dt '01-jun-2008' and end_dt '13-aug-2008'

2.I need the count of the code for every week between the start_dt and end_dt

now normaly i am getting the total count of the code,which is given below

SELECT   channel_code, COUNT (*)
    FROM XXX
   WHERE TRUNC (last_updated_dt) BETWEEN to_date('01-06-2008','dd-mm-yyyy')  AND '13-aug-2008'
GROUP BY channel_code;



BKG_CHANNEL_CODE                 COUNT(*)
------------------------------ ----------
BFE                                   352
EZC                                    20
JMC                                   182
PYD                                    12
                                       26


5 rows selected.


In above mentioned query returns the total count,but i need it as weekly count
(ie) for example

1.from 1stjun to 8th jun - count(channel_code)
2.next from 9th jun to 16 jun -count(channel_code)
..............
.............
............

Thanks,



Re: reg date function [message #340605 is a reply to message #340601] Wed, 13 August 2008 06:58 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
You would need to create or generate some sort of week date table and join to that using a between condition on last_updated_dt. You could then add a further grouping to your query.
Re: reg date function [message #340613 is a reply to message #340601] Wed, 13 August 2008 07:38 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
ram anand wrote on Wed, 13 August 2008 07:38

WHERE TRUNC (last_updated_dt) BETWEEN to_date('01-06-2008','dd-mm-yyyy') AND '13-aug-2008'
[/code]



Interesting that you would make the effort to properly use the TO_DATE function on the first part of the WHERE clause, then leave it off on the second.

In this case, you are trying to see if a DATE is between another DATE and a STRING, which may even be worse than checking to see if a DATE is between two strings.

In essence, is August 12, 2008 between June 1, 2008 and and a kumquat?
Re: reg date function [message #340660 is a reply to message #340601] Wed, 13 August 2008 13:27 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
A HINT, USE TRUNC(LAST_UPDATED_DT,'W')

[Updated on: Wed, 13 August 2008 13:28]

Report message to a moderator

Re: reg date function [message #340677 is a reply to message #340660] Wed, 13 August 2008 14:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Bill B wrote on Wed, 13 August 2008 20:27
A HINT, USE TRUNC(LAST_UPDATED_DT,'W')


You mean group by to_char(<yourdate>, 'W')
Re: reg date function [message #340678 is a reply to message #340677] Wed, 13 August 2008 14:11 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Frank wrote on Wed, 13 August 2008 14:09
Bill B wrote on Wed, 13 August 2008 20:27
A HINT, USE TRUNC(LAST_UPDATED_DT,'W')


You mean group by to_char(<yourdate>, 'W')

NO, what happens if the date range is more then one year. Group by the actual beginning of the week and it will always be correct. I know it will work in the example, but I like to suggest something that will always work, not just for the special case.

[Updated on: Wed, 13 August 2008 14:12]

Report message to a moderator

Re: reg date function [message #340682 is a reply to message #340678] Wed, 13 August 2008 14:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry, you are right.
I misread your previous reply; thought you meant to use that in a where clause; the between in the OP must have confused me; mixed up the two
Re: reg date function [message #340683 is a reply to message #340601] Wed, 13 August 2008 14:29 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Reading the topic, I should have been clearer, but I didn't want to hand it on a platter to the op.
Previous Topic: PL/SQL Loop
Next Topic: Query to Translate and Replace: Pls Help
Goto Forum:
  


Current Time: Fri Dec 02 16:55:22 CST 2016

Total time taken to generate the page: 0.19180 seconds