Another tricky PL/SQL or sql plus question (percentage)

[liliya13]

Hi,
I am trying to calculate a percent (%) of increase or decrease of numbers from week to week
I would do it something like the following query:

select a.numb, b.date, a.percent
from tableA a, tableB b
where a.id=b.id and
b.date>startdate and
b.date<enddate

I am wondering if I have to do it though a LOOP ************************
run_Date number percent%
08/06 10
08/13 5 -50%
08/20 10 +50%
.
.
***********************
I assume it can be implemented though a block of build in PL/SQL code which I am not familiar with or otherwise(?). Could anybody help me with that?

Any assistance is greatly appreciated!!!!

Thanks in advance,

Liliya

 

[SantaMufasa]

Liliya,

Help me understand a few things first. Here is the sample output you listed:

run_Date  number   percent%
08/06     10             
08/13     5        -50%
08/20     10       +50%

1) From 06 Aug to 13 Aug, I can see where going from 10 to 5 is a -50%, but the next week, in what way is going from 5 to 10 a +50% increase? Is it not +100%?

2) Your query shows "a.percent"; is that number already part of TableA or are you trying to calculate it as part of the query?

3) Would you choose to do this in pure SQL (without using PL/SQL) if you had the choice, or is there an overriding reason to use PL/SQL?

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:42 (10Aug04) UTC (aka "GMT" and "Zulu"), 09:42 (10Aug04) Mountain Time)

 

[liliya13]

1. I was wrong.It is 100%. Sorry for the confusion
2. a.perset is not a part of tableA ...trying to calculate and want it in the output
3. if I had the choice I would not use pl/sql. There is no reason for that. It was just an assumption that it couldn't be done otherwise.

Thank you!!!

Liliya

 

[SantaMufasa]

Liliya,

Sorry for another followup question. While creating the SQL code for your solution, I noticed that your columns seem a bit scrambled: TableB has "dates", yet TableA has the "numbers"? This seems bogus.

Is this a contrived example for real tables? If so, could you please post a "describe" of the relevant columns from the real tables? That would probably clarify things for us while we produce a solution for you. Also, is there a row in one of the tables that contains a "date" and "number" for each week of your output?

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:10 (10Aug04) UTC (aka "GMT" and "Zulu"), 10:10 (10Aug04) Mountain Time)

 

[sem]

Not sure, but try this (at least as an idea)

select  b.date, a.numb, a.numb/(lag(a.numb) over (order by b.date)) percent
   from tableA a, tableB b
where a.id=b.id and
      b.date>startdate and
      b.date<enddate

Regards, Dima

http://seminihin.narod.ru

 

[liliya13]

It looks like I confused you with dates.
Run_date s in Report table(B) are not linked directly to numbers (bikes_sold). There is no row in Reports (tableB) which containd a date for a particular bike_sold number in tableA.

It is my fault as I should have break a problem into a small pieces. 1st of all my concern is :
how I can calculate a percent from row to row (considering only tableA :

bikes_num percent%(calculated)
10
5 -100%
10 +100%

2nd part is:
how can I get a percent within a date range.
TableB (REPORTS) has pk b.id(seq). TableA (Bikes) has foreign key a.id accordingly

Sorry for my poor English

 

[SantaMufasa]

Liliya,

I'm certain we can produce a "SQL-only" solution for you, but before we do, I'm still puzzled: You said, "...[rows in] table(B) are not linked directly to numbers (bikes_sold)." There seems to be no data (such as a date) in the "Bikes_Sold" table to "ORDER BY". Without an "ORDER BY" mechanism, to then "calculate a percent from row to row" is virtually meaningless. You cannot depend upon physical order of rows in Oracle to infer "data-entry order". You must assume that rows are in random order in an Oracle table.

So, if you can provide a rational "ORDER BY" clause for your bikes_sold table, I believe we can help you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:58 (10Aug04) UTC (aka "GMT" and "Zulu"), 10:58 (10Aug04) Mountain Time)

 

[liliya13]

The only order would be a primary key for TableA based on sequence increased by 1

So, for step 1 I have:
1. one table
2.two columns:
col1 =id pk increase 1
col2 =bikes_sold (this is a number)

I think it can be ordered by pk if needed.
3. My question would be : how to tell difference line by line in %?

Thank you for your help!

Liliya

 

[SantaMufasa]

Liliya,

Here, then, is code that will do what you want:

Section 1 -- Contents of base table:

select * from liliya_a;

 ID BIKES_SOLD
--- ----------
  1         10
  2          5
  3         10

Section 2 -- Query code:

col a heading "Bikes|Sold" format 999
col b heading "Percent|Change" format 999
select  This_Period.bikes_sold a
 ,((This_period.bikes_sold-Last_Period.bikes_sold)/last_Period.bikes_sold)*100 b
from liliya_a This_Period, liliya_a Last_Period
where This_period.id-1 = Last_Period.id(+)
/

Bikes Percent
 Sold  Change
----- -------
   10
    5     -50
   10     100

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:52 (10Aug04) UTC (aka "GMT" and "Zulu"), 11:52 (10Aug04) Mountain Time)