sql query

CREATE VIEW vw_GetDetails
AS
BEGIN
  SELECT a.group, b.job, a.salary
  FROM Table1 a
  INNER JOIN Table2 b ON a.emp_id = b.emp_id
END

Select allOpen in new window

I want to distribute the salary to job based on the working days

Is this a homework question ?

Raj

select salary,
           sum(days_worked) sum_DW
  from Tab1,
          Tab2
  where tab1.emp_id = tab2.emp_id
  group by salary

Is this, what you mean?
Regards
Klaus

no. example one employee worked 3 jobs in a month (30 days), job1 10 days, job2 15 days, job3 5 days. his monthly salary is 5000.  so i have to apportion his salary to 3 jobs means job1=(3000/30)*10, job2=(3000/30)*15, job3=(3000/30)*5. this i wish to get in a query

5000/30*10

You must insert into table2 a jobnumber.
Than it looks like:
select TAB1.emp_id,
          TAB2.jobnumber,
          sum(salary / 30 * days_worked) Job_Salary
  from Tab1,
          Tab2
  where tab1.emp_id = tab2.emp_id
  group by TAB1.emp_id,
                 TAB2.jobnumber

@Klaus, table2 seems to have a job column ?

select a.emp_id, b.job, a.salary / 30 * b.days_worked as Salary
from table1 a
inner join table2 b on a.emp_id = b.emp_id

Select allOpen in new window

Raj

Thank you.

how can i get sum ( days_worked ) instead of 30


select TAB1.emp_id,
          TAB2.jobnumber,
          sum(salary / 30 * days_worked) Job_Salary
  from Tab1,
          Tab2
  where tab1.emp_id = tab2.emp_id
  group by TAB1.emp_id,
                 TAB2.jobnumber

To display the decimals, here is the modified query

select a.emp_id, b.job, round(cast(cast(a.salary as float) / 30 * cast(b.days_worked as float) as float), 2) as Salary
from table1 a
inner join table2 b on a.emp_id = b.emp_id

Select allOpen in new window

emp_id      job      Salary
1      Job 1      1666.67
1      Job 2      2500
1      Job 3      833.33
----------------
Total - 5000

Raj