Help needed

Database
Enthusiast

Help needed

We have a table which has a time field. I need to run a query which gives result for each month. At present i am running query using where condition

start_tv_date between '2000-06-25 00:00:00' and '2000-07-24 00:00:00'

and changing condition for each month like

start_tv_date between '2000-07-25 00:00:00' and '2000-08-24 00:00:00'

But i want to run single query that will give the complete set or results for each month.

Will be gr8 to get qucik reply.
8 REPLIES
Junior Contributor

Re: Help needed

Hi Sakthi,

this is an example using sys_calendar:

SELECT
EXTRACT(YEAR FROM calendar_date - 24),
EXTRACT(MONTH FROM calendar_date - 24),
MIN(calendar_date),
MAX(calendar_date)
FROM sys_calendar.calendar
GROUP BY 1,2

Dieter
Enthusiast

Re: Help needed

Hi sakthi,
i am not much experienced but i have small suggestion for you

create a volatile table with single column where you store all 12 months start date i.e. lower limit of start_tv_date in between clause.

use within where clause
start_tv_date between lower_limit and add_months(lower_limit + 1)

which may serve your job.
if not please provide the means with which you are deciding the lower limit of your start_tv_date.

thanks
jagdish
Enthusiast

Re: Help needed

yup sorry in my last response

add_months(lower_limit + 1)
should be add_months(lower_limit,1)

jagdish
Enthusiast

Re: Help needed

Hi ,

Thanks for your replies.

Am sorry i couldnt get my requirement done.

My table looks like,

A B
1 '2000-06-25 00:00:00'
2 '2000-06-25 00:00:00'
3 '2000-07-25 00:00:00'
4 '2000-08-25 00:00:00'
5 '2000-08-25 00:00:00'
6 '2000-08-25 00:00:00'

I need to find the row count for each month (ie from B column) like as below,

output
------
2
1
3

ie for month 06 -2
07 -1
08 -3

Any way other than using volatile table will be useful.
Thanks in advance :-)
Enthusiast

Re: Help needed


SELECT EXTRACT(MONTH FROM B) "Month", COUNT(*) "Count"
FROM MyTable
GROUP BY 1

Enthusiast

Re: Help needed

Hi Joe,

Gr8 work.It worked.

It needs some modifications. I want the count for each month (from '2000-06-25 00:00:00' and '2007-02-24 00:00:00') but not the aggregate for each month.

Suppose jan comes 7 times in my timeslot. I need the counts induvidually for each time jan comes.

Enthusiast

Re: Help needed

:o

Is it something like this that you are looking for ?

SELECT EXTRACT(MONTH FROM B) "Month", COUNT(*) OVER(PARTITION BY "Month") "Count"
FROM MyTable
;

Month Count
6 2
6 2
7 1
8 3
8 3
8 3

Else probably you can give another sample output ...
Enthusiast

Re: Help needed

hey
i got it...
i had extract year along with month and things worked
thans a lot joe