August 20, 2020 at 4:23 am
Hello All Good Evening,
Can you please help with this Issue
--- Sample Data Begins
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')
--- Sample Data ENDS
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
in the expected output we want to calculate till 2022 Dec so expected outout as below
Create table #myoutout (DriverMon DATE, CostPrice Numeric, CurrentPercentage int, MonthtobeConsider Date,expensetype varchar2(20), LessorID int)
--- Sample OUTPUT Data
Insert Into #myoutout Values(TO_DATE('01/01/2021', 'DD/MM/YYYY'),150,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/02/2021', 'DD/MM/YYYY'),120,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/03/2021', 'DD/MM/YYYY'),110,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/04/2021', 'DD/MM/YYYY'),140,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/05/2021', 'DD/MM/YYYY'),160,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/06/2021', 'DD/MM/YYYY'),120,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343') --- Upto here will keep in output as it is
Insert Into #myoutout Values(TO_DATE('01/07/2021', 'DD/MM/YYYY'),124.8,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343') --- we will take max(Drivermon) for the each expensetype to calculate future costprice for that Expensetype. which is Jun 21 for expensetype Lease, cost price is 120$ so need to calculate 4% (CurrentInterest) which is 120 + 4.8 = 124.8
Insert Into #myoutout Values(TO_DATE('01/08/2021', 'DD/MM/YYYY'),124.8,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/09/2021', 'DD/MM/YYYY'),124.8,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/10/2021', 'DD/MM/YYYY'),124.8,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/11/2021', 'DD/MM/YYYY'),124.8,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/12/2021', 'DD/MM/YYYY'),124.8,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/01/2022', 'DD/MM/YYYY'),124.8,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/02/2022', 'DD/MM/YYYY'),124.8,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/03/2022', 'DD/MM/YYYY'),124.8,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/04/2022', 'DD/MM/YYYY'),124.8,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/05/2022', 'DD/MM/YYYY'),124.8,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/06/2022', 'DD/MM/YYYY'),124.8,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/07/2022', 'DD/MM/YYYY'),124.8,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/08/2022', 'DD/MM/YYYY'),124.8,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/09/2022', 'DD/MM/YYYY'),124.8,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/10/2022', 'DD/MM/YYYY'),124.8,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/11/2022', 'DD/MM/YYYY'),124.8,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/12/2022', 'DD/MM/YYYY'),124.8,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Lease', '4343')
Insert Into #myoutout Values(TO_DATE('01/01/2021', 'DD/MM/YYYY'),55,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Rent', '4343')
Insert Into #myoutout Values(TO_DATE('01/02/2021', 'DD/MM/YYYY'),25,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Rent', '4343')
Insert Into #myoutout Values(TO_DATE('01/03/2021', 'DD/MM/YYYY'),10,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Rent', '4343')
Insert Into #myoutout Values(TO_DATE('01/04/2021', 'DD/MM/YYYY'),40,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Rent', '4343') --- Till here from the table above as it is
Insert Into #myoutout Values(TO_DATE('01/05/2021', 'DD/MM/YYYY'),41.6,4,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Rent', '4343') -- future costprice calculation starts here max record(drivermon) for expensetype Rent is Apr 2021 which is 40$ so 40 + 4% 41.6
Insert Into #myoutout Values(TO_DATE('01/06/2021', 'DD/MM/YYYY'),41.6,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Rent', '4343')
Insert Into #myoutout Values(TO_DATE('01/07/2021', 'DD/MM/YYYY'),41.6,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Rent', '4343')
Insert Into #myoutout Values(TO_DATE('01/08/2021', 'DD/MM/YYYY'),41.6,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Rent', '4343')
Insert Into #myoutout Values(TO_DATE('01/09/2021', 'DD/MM/YYYY'),41.6,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Rent', '4343')
Insert Into #myoutout Values(TO_DATE('01/10/2021', 'DD/MM/YYYY'),41.6,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Rent', '4343')
.....
....
...
Insert Into #myoutout Values(TO_DATE('01/12/2022', 'DD/MM/YYYY'),41.6,TO_DATE('19/10/2017', 'DD/MM/YYYY'), 'Rent', '4343')
--- Sample OUTPUT ENDS
Thank you in advance
Asita
August 20, 2020 at 4:24 am
Apologize for my Format, I tried several ways to adjust that but no luck.
Please let me know if you need any more info on this
Thank you in advance
Sita
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply