I have a question regarding view creation.
Here is my question:
I need to generate a report using a view which takes data from two tables lets say Table_A and Table_B.
Sample data in Table_A:
empno empname elem_name
---------------------------------------
1 abc Basic_Salary
1 abc Medical_Bill
2 xyz Basic_Salary
2 xyz Medical_Bill
Note: Element_name should be passed dynamically (I have around 50 element_names)
Sample data in Table_B:
Empno Basic_Salary Medical_Bill
--------------------------------
1 20000 500
2 50000 500
Now, My expected result is
Output:
Empno Empname effective_date Basic_Salary Medical_bill Total_sum
--------------------------------------------------------------------------
1 abc 13-jun-2016 60000 1500 61500
2 xyz 5-may-2017 150000 1500 151500
Note 1:
I am generating a report by passing a parameter like quarterly report or monthly report.
Suppose here lets say, I need a report of 3 months details then I need to sum the basic salary of an employee ie., 20k+20k+20k which is 60000 in output. Similarly for Medical bill also.
Please help me with this.
Many Thanks!
I don't understand how table_a comes into this? Or where you get the effective dates from?
If you want to add up the values in table_b N times, cross join to an N row table. Then group by employee id or whatever and sum up the values:
with emps as (
select 1 id, 20000 sal, 500 med from dual union all
select 2 id, 50000 sal, 500 med from dual
), rws as (
select level rw from dual
connect by level <= 3
)
select id, sum(sal), sum(med), sum(sal+med)
from emps
cross join rws
group by id;
ID SUM(SAL) SUM(MED) SUM(SAL+MED)
1 60000 1500 61500
2 150000 1500 151500
Or you could multiply everything by N:
with emps as (
select 1 id, 20000 sal, 500 med from dual union all
select 2 id, 50000 sal, 500 med from dual
)
select id, sal*3, med*3, (sal+med)*3
from emps;
ID SAL*3 MED*3 (SAL+MED)*3
1 60000 1500 61500
2 150000 1500 151500
But somehow I suspect neither of these are really what you're looking for...
If so, please give us more to work with. Namely:
- create table
- data in the form of "insert into table..."
- A complete description of what you're trying to do. What values are you passing? How? What should the result be for these values based on the data you've given? What happens if you pass different values?