Home » SQL & PL/SQL » SQL & PL/SQL » Future PriceCalculation based on 3 columns (Oracle 11)
Future PriceCalculation based on 3 columns [message #681723] Wed, 19 August 2020 22:27 Go to next message
buzzi7
Messages: 1
Registered: August 2020
Junior Member
Hello Greetings Gurus.

Can you please help me with this below

Create Table SampleData (DriverMon DATE, CostPrice Numeric, CurrentPercentage int, MonthtobeConsider Date,expensetype varchar2(20), LessorID int);


Insert Into SampleData Values(TO_DATE('01/01/2021', 'DD/MM/YYYY'),150,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343');
Insert Into SampleData Values(TO_DATE('01/02/2021', 'DD/MM/YYYY'),120,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343');
Insert Into SampleData Values(TO_DATE('01/03/2021', 'DD/MM/YYYY'),110,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343');
Insert Into SampleData Values(TO_DATE('01/04/2021', 'DD/MM/YYYY'),140,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343');
Insert Into SampleData Values(TO_DATE('01/05/2021', 'DD/MM/YYYY'),160,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343');
Insert Into SampleData Values(TO_DATE('01/06/2021', 'DD/MM/YYYY'),120,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343');


Insert Into SampleData Values(TO_DATE('01/01/2021', 'DD/MM/YYYY'),55,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Rent', '4343');
Insert Into SampleData Values(TO_DATE('01/02/2021', 'DD/MM/YYYY'),25,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Rent', '4343');
Insert Into SampleData Values(TO_DATE('01/03/2021', 'DD/MM/YYYY'),10,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Rent', '4343');
Insert Into SampleData Values(TO_DATE('01/04/2021', 'DD/MM/YYYY'),40,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Rent', '4343');

Records in table is unique on DriverMon, ExpenseType, LessorID ---> One Record per month.
MonthtobeConsider column value is same for the LessorID in table --> in other words will see only one value per LessorID in the table
Currentpercentage column value is same for the LessorID in table --> in other words will see only one value per LessorID in the table

I have given small set of samples data

Need to calculate CostPrice value (per month one record per expense type) as costprice + CurrentPercentage. to caulcuate the future value we have to take the max(DriverMon) per expensetype record's costprice

Thank you in adavance
Sitara
Re: Future PriceCalculation based on 3 columns [message #681725 is a reply to message #681723] Thu, 20 August 2020 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the valid test case.
Thanks for proper use of TO_DATE and year with 4 digits.
To improve more your posts, please read How to use [code] tags and make your code easier to read.
Also post the result you want from the data you give, it will to understand your specification.
And always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

Is "CurrentPercentage" a real percentage or a pre-computed percentage from the current cost? Your formula "costprice + CurrentPercentage" seems to indicate the later.

Here the result for both case:
SQL> with
  2    data as (
  3      select expensetype, costprice, currentpercentage,
  4             row_number() over (partition by expensetype order by drivermon desc) rn
  5      from sampledata
  6    )
  7  select expensetype, costprice+currentpercentage newprice
  8  from data
  9  where rn = 1
 10  order by expensetype
 11  /
EXPENSETYPE            NEWPRICE
-------------------- ----------
Lease                       124
Rent                         44

2 rows selected.

SQL> with
  2    data as (
  3      select expensetype, costprice, currentpercentage,
  4             row_number() over (partition by expensetype order by drivermon desc) rn
  5      from sampledata
  6    )
  7  select expensetype, costprice * (1+currentpercentage/100) newprice
  8  from data
  9  where rn = 1
 10  order by expensetype
 11  /
EXPENSETYPE            NEWPRICE
-------------------- ----------
Lease                     124.8
Rent                       41.6

2 rows selected.
Re: Future PriceCalculation based on 3 columns [message #681759 is a reply to message #681725] Thu, 27 August 2020 00:18 Go to previous message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

A feedback would be welcome.

Previous Topic: row_number with duplicity
Next Topic: Oracle Procedure with table name as parameter
Goto Forum:
  


Current Time: Wed May 12 15:46:14 CDT 2021