[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql query

Posted on 2011-10-17
11
Medium Priority
?
303 Views
Last Modified: 2012-08-13
Dear Experts
I have tables like  (1) emp_id, salary,group. (2) emp_id,job,days_worked
I need a view group,job,salary
regards
0
Comment
Question by:bmkdubai
  • 5
  • 4
  • 2
11 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 36984043
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

Open in new window

0
 

Author Comment

by:bmkdubai
ID: 36984050
I want to distribute the salary to job based on the working days
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 36984063
Is this a homework question ?

Raj
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Expert Comment

by:KlausBarthels
ID: 36985147
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
0
 

Author Comment

by:bmkdubai
ID: 36985196
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
0
 

Author Comment

by:bmkdubai
ID: 36985220
5000/30*10
0
 

Expert Comment

by:KlausBarthels
ID: 36985245
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
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 36985307
@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

Open in new window


Raj
0
 

Author Comment

by:bmkdubai
ID: 36985315
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
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 36985320
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

Open in new window


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

Raj
0
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 2000 total points
ID: 36985330
If you want to sum up days_worked, instead of 30
select a.emp_id, b.job, round(cast(cast(a.salary as float) / c.total_days_worked * cast(b.days_worked as float) as float), 2) as Salary
from table1 a
inner join
(
	select emp_id, sum(days_worked) total_days_worked
	from table2
	group by emp_id
) c on a.emp_id = c.emp_id
inner join table2 b on a.emp_id = b.emp_id

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question