Hello Everyone I want to get greatest Date record and multiply to that dynamically below is sample structure of my table and here is DB fiddle https://dbfiddle.uk/?rdbms=oracle_18&fiddle=cde3fdc07915a2e8c23195be646c5a20
+-----+-------------+-----------+--------+----------------+
| ID | Sequence Id | Date | Amount | Frequency |
+-----+-------------+-----------+--------+----------------+
| 123 | 1 | 01-Jan-20 | 50 | Monthly |
| 123 | 2 | 01-Feb-20 | 50 | Monthly |
| 123 | 3 | 01-Mar-20 | 150 | Monthly |
| 123 | 4 | 01-Apr-20 | 200 | Monthly |
| 123 | 5 | 01-May-20 | 510 | Monthly |
| 123 | 1 | 01-Jan-20 | 510 | Quarterly |
| 123 | 2 | 01-Apr-20 | 300 | Quarterly |
| 123 | 1 | 01-Jan-20 | 600 | Semi-Annually |
+-----+-------------+-----------+--------+----------------+
I want to retrieve data dynamically with the help of filter and want to multiply amount according to Frequency. Get greatest record according to Date and multiply amount with 12 if frequency monthly or multiply 4 if frequency Quarterly or multiply 2 if frequency Semi-Annually
Ex. 1. If we run query select ID, Rent from Table where Date is greater than or equal 01-jan-2020 and less than or equal to 01-may-2020 and frequency equal to Monthly then out put should be like below -
+-----+-------------+
| ID | Rent |
+-----+-------------+
| 123 | 6,120 |
+-----+-------------+
2. If we run query select ID,Rent from Table where Date is greater than or equal 01-jan-2020 and less than or equal to 01-may-2020 and frequency equal to Quarterly then out put should be like below -
+-----+-------------+
| ID | Rent |
+-----+-------------+
| 123 | 1200 |
+-----+-------------+
3. If we run query select ID,Rent from Table where Date is greater than or equal 01-jan-2020 and less than or equal to 01-may-2020 and frequency equal to Semi-Annually then out put should be like below -
+-----+-------------+
| ID | Rent |
+-----+-------------+
| 123 | 1200 |
+-----+-------------+
If you want this over multiple ids and frequencies at once, then you can use row_number() like so:
select id,
amount * case frequency
when 'Monthly' then 12
when 'Quaterly' then 4
when 'Semi-Annually' then 2
end as rent
from (
select t.*, row_number() over(partition by id, frequency order by StartDate desc) rn
from table1 t
where StartDate between date '2020-01-01' and date '2020-05-01' and frequency = 'Monthly'
) t
where rn = 1