1. Registered User
Join Date
Feb 2004
Posts
35

I have a table called

user which has user_id, customertype and date_created.

I need to find out the '% of change in the # of users registered' over the previous month.

So, Is there anyway, that this % can be calculated in a Query?

Thanks,

2. Registered User
Join Date
Oct 2003
Location
St.Louis,MO
Posts
120
yes

3. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
YES, you'll get different answers depending upon
whether you use last month's total or this month's total
as the basis for the resultant % change calculation.
The solution is SMOP; a Small Matter Of Programming.

4. Registered User
Join Date
Feb 2004
Posts
35
I can definitely program it in the application, but because I am new to any such calculations in Oracle, I am interested in knowing how I would do that..

Basically, the formula is

Step1:
Select count(user_id) as Curr_Month from up_user where date_created between '1-JAN-2004' AND '31-JAN-2004'

Step2:
Select count(user_id) as Prev_Month from up_user where date_created between '1-DEC-2003' AND '31-DEC-2003'

Step3:
result (in%) = (count(Curr_Month) - count(Prev_Month))/count(Curr_Month) * 100

Any help in writing a query for this would be appreciated.

Thanks,

5. Registered User
Join Date
Mar 2004
Posts
14
sorry , I don't check it, but if you neet do this only by query so something like this can help

select b.cnt_/a.cnt_ * 100, a.month_ , a.customertype from

( select customertype , trunc( date_created,'mm') month_, count ( * ) cnt_
group by trunc( date_created,'mm') , customertype ) month_a

( select customertype , trunc( add_month(date_created,1),'mm') month_, count ( * ) cnt_
group by trunc( date_created,'mm') , customertype ) previous_b

where a.month_ = b.month_(+)
and a. customertype = b.customertype

but I think too that at least view or function will work quikly

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•