Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

How to generate a row?

user13168644Mar 14 2024

Hi,

I have a table that stores the salary supplement for employees. Usually, the supplement is for one year ( there is a start date and end date). But for some employees there is no end date. That means they will get the supplement each month till there is an end to it. I want to calculate the annual supplement for each employee. But for the employees, who do not have an end date, how do I generate rows for each year? Here is a sample data and what the output should look like

select 1 emp_id, 500 amount, to_date('01/01/2022','mm/dd/yyyy') start_date, to_date('12/01/2022','mm/dd/yyyy') end_date from dual union all

select 1 emp_id, 300 amount, to_date('01/01/2023','mm/dd/yyyy') start_date, to_date('06/01/2023','mm/dd/yyyy') end_date from dual union all

select 2 emp_id, 400 amount, to_date('01/01/2023','mm/dd/yyyy') start_date, to_date('12/01/2023','mm/dd/yyyy') end_date from dual union all

select 3 emp_id, 600 amount, to_date('01/01/2022','mm/dd/yyyy') start_date, null end_date from dual;

id year amount
1 2022 6000
1 2023 1800
2 2023 4800
3 2022 7200
3 2023 7200
3 2024 7200

since there is no end date for employee id 3, the year 2023 and year 2024 rows also should be generated.

Thanks

This post has been answered by Frank Kulash on Mar 14 2024
Jump to Answer
Comments
Post Details
Added on Mar 14 2024
5 comments
236 views