Hi everyone, I have an scenario where i want to join data and limit the result set to certain point. let me explain further. consider the following data:
with data as (
select 1 id , 1 groupId, 'weekly' frequency, to_date('2/14/2021','mm/dd/yyyy') dt, 123 amt from dual union all
select 2 id , 1 groupId, 'montly' frequency, to_date('2/28/2021','mm/dd/yyyy') dt, 44 amt from dual union all
select 3 id , 2 groupId, 'weekly' frequency, to_date('2/21/2021','mm/dd/yyyy') dt, 345 amt from dual
)
,data2 as (
select 'weekly' frequency, 1 freq_num from dual union all
select 'weekly' frequency, 2 freq_num from dual union all
select 'weekly' frequency, 3 freq_num from dual union all
select 'monthly' frequency, 1 freq_num from dual union all
select 'monthly' frequency, 2 freq_num from dual
)
i want to join data table with data2 by frequency and add freq_num to the dt column. for example, when frequency is weekly and freq_num=2 then 2 weeks should be added to dt column in data table. the trick here is that if you have multiple rows with same group id then the data should be derived until the next row dt is reached. for example. i am looking for the following output:
id groupId dt
1 1 2/21/2021
2 1 3/28/2021
2 1 4/28/2021
3 2 2/28/2021
3 2 3/7/2021
3 2 3/14/2021
here I am joining data with data2 by frequency and taking the value in freq_num and adding that to dt column in data. for example, the first row in data table has 2/14/2021 dt, when join with data2 , oracle will take first row in data2 and add freq_num=1 week to dt and that produce 2/21/2021. when oracle move to second row in data2, it will add freq_num=2 weeks to 2/12/2021 which produce 2/28/2021. however, there is another row with groupID=1 and frequency=monthly and the dt=2/28/2021 with is equal to 2/28/2021 produce by the first row in data table. in this case, the output from row one in data should stop and the join should continue with row two of data table.
in row 3 of data table, it only has one row with same group id and therefore when joining with data2 it should produce 3 rows.
my started writing the query which looks like this
select dt + freq_num from data a join data2 b on (a.frequency = b.frequency )
but i am stuck. i dont know how to check if there is another row with the same groupID and how to limit the output of row one in data table to < than dt in row 2 which has the same groupID.
im using oracle 11g. can someone help me write a query that produce the output above? i hope i didnt confuse anyone. If any questions, let me know and i can clarify. thanks in advance
)