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  |
 |
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  |
 |
Michel Cadot
Messages: 68421
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. |
|
|
|
|
|
Goto Forum:
Current Time: Fri Mar 31 02:20:34 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
