Department Wise Max And Min Salaried Employees
Sep 14, 2014
I have the employees and department tables(structure below).
Write a sql to get the department name,employee earning maximum salary,employee earning minimum salary for each department
result set:
dname |max_salaried_employee|min_salaried_employee
------------------------------------------------------
Accounts | Blakes | Miller
HR | King | James
Structure :
create table dept(
deptno number(2,0),
dname varchar2(14),
loc varchar2(13),
constraint pk_dept primary key (deptno)
[Code] .....
View 1 Replies
ADVERTISEMENT
Dec 4, 2006
i have 2 tables emp and dept
emp has columns:
empid(pk),empname,deptid(fk),salary
dept has columns:
deptid(pk),deptname
now my aim is:
List of the employees and their department who is top salary earner of the department.
wht i can think of is:
select distinct empname,deptname,max(salary) as 'max salary'
from emp e,dept d
where e.deptid=d.deptid
group by empname,deptname
but it gives unexpected result...
help appreciated
cheers
View 13 Replies
View Related
Aug 16, 2007
I have to Make Department Table.
But this there names can be in multi language.
--------------------------------------------DepartmentGroup--------------------------------------------DepartmentGroupId (P.K.)UniqueNameCreatedDateModifiedDate--------------------------------------------
--------------------------------------------Department--------------------------------------------DepartmentId (P.K.)NativeNameNavigateUrlIsFavoriteLanguageId (F.K.)DepartmentGroupId (F.K.)IsVisibleCreatedDateModifiedDate--------------------------------------------
From this design we can move to same department in different language.
is it right?
View 2 Replies
View Related
Jan 17, 2015
I would like to find the average salary for each department which has min salary
In my case I will have 3 departments which have min salary.
select distinct d.department_name, E.SALARY, avg(E.salary)
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)
WHERE E.SALARY = (SELECT MIN(E.SALARY)
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID))
GROUP BY D.DEPARTMENT_NAME, E.SALARY;
View 1 Replies
View Related
Nov 27, 2006
How to display the paper number that is co authored by authors from the same department?
The database structure:
Department(DeptNum, Descrip, Instname, DeptName, State, Postcode)
Academic(AcNum, DeptNum, FamName, GiveName, Initials, Title)
Paper(PaNum, Title)
Author(PaNum, AcNum)
Field(FieldNum, ID, Title)
Interest(FieldNum, AcNum, Descrip)
Thanks for help a lot
View 3 Replies
View Related
Sep 28, 2015
I have an issue related to SSAS security. We have an SSAS multidimensional cube which needs 3 types of security:
- Access to the entire cube => OK, based upon a role
- Restricted access to one department (= dimension) => OK, based upon a role
- Access to the entire cube, but with dynamic security on 2 measures.
Let's say, we have 2 departments (food and non-food). Users within food are allowed to see sales and pieces from the food department, but not from the non-food department.
It is not an option to restrict access to the non-food department because there are other measure which they have access to. I tried cell security, but this is very slow and generates multiple empty rows on my selections.
View 3 Replies
View Related
May 29, 2015
I need to prepare a roster rotating persons within each Department for the available dates
Please refer to the below sample tables and data
CREATE TABLE Roster
(
StartDate Date,
EndDate Date
)
CREATE TABLE Department
[Code] ....
Expected Output
SELECT '01/01/2015' StartDate, '01/05/2015' EndDate, 'A' Department,'P1' Person
UNION
SELECT '01/01/2015' StartDate, '01/05/2015' EndDate, 'B' Department,'P3' Person
UNION
SELECT '01/01/2015' StartDate, '01/05/2015' EndDate, 'C' Department,'P6' Person
[Code] .....
View 9 Replies
View Related
Nov 15, 2007
Ok, I have the following table
CREATE TABLE employees (
employee_number char(6) NOT NULL
, known_as char(20)
, surname char(20)
, job_title char(20)
, manager_number char(6) NULL
, unique_identifier char(6) NOT NULL PRIMARY KEY
)
--unique_identifier is in the format 000123456789
Now I have a conundrum when trying to create organization charts in Visio, so I figured I'd try reproduce the format that a bunch of walkthroughs suggest, which is with the first column being an int identity(1,1) column as the employeeID with the managerID being an int column also, referencing the employeeID.
Hope I've not lost you just yet ;)
So here's what I figured - create a new table with the new integer columns, slap my current data into it and then update the managerID as necessary...
Except I can't work out the update statement for this!
CREATE TABLE gvVisioTest (
employeeID int identity(1,1)
, employee_number char(6)
, job_title char(40)
, department_reference char(10)
, managerID int
, manager_number char(6)
)
GO
INSERT INTO gvVisioTest(employee_number, job_title, department_reference, manager_number)
SELECT employee_number, job_title, department_reference, manager_number
FROM employees
GO
--Update managerID with relevant employeeID
GO
DROP TABLE gvVisioTest
Any ideas?
Oh and this is legacy so the design is flawed, modified over time (the manager field is a bodge put in 5 years ago), so yeah unfortunately I have to work with what I've got :(
View 14 Replies
View Related
Dec 8, 2011
I need to calculate the salary given to all employees in a year
Code:
select sum(emp_total_sal)from emp_salary
How to modify this code to get what i need ?
View 3 Replies
View Related
May 26, 2008
Dear all,
I have a table that contains the following columns:
COMPANY | EMPLOYEE | SALARY
I want to select the TOP 5 PERCENT employees from each company, ordered by salary. Is this possible?! Thanks!
Pedro Martins
View 7 Replies
View Related
Jan 31, 2008
I am using SqlServer 2000 with asp.net 2.0, I have a table tbl_employees, with fields (empId, empName, empManagerId), with following data...
empId
empName
empManagerId
1
A
2
B
1
3
C
2
4
D
2
5
E
4
Now the question is that what should be the single line query or best solution if i want to get all employess under a perticular manager ?For example; Employees under 'A' are (B,C,D,E) //(C,D,E are also indirectly under A)Emplloyess under 'B' are (C,D & E; E is also under B as his because his managwer 'D' is himself under 'B')
Please advise..Thanks alot.
View 7 Replies
View Related
Apr 5, 2007
I have an employee table with manager id and employee ids , i need to find all the employee ids for a manager id . Each employee can be a manager in turn . So I need to find all the employees under one manager and if any of the employee is in turn a manager , i need to find the employees under him as well .
The table structure is defined and i cannot edit it .
Please let me know if we could have a single query to do this .
Thank you
kishore
View 14 Replies
View Related
May 11, 2006
I have a query that returns a result that looks like this:
amount count
steve 122000 12
jim 145213 13
paul 62325 7
I need to add 2 columns to this query that rank the employees by count and amount with a number, so for example:
amount amount_rank count count_rank
steve 122000 2 12 2
jim 145213 1 13 1
paul 62325 3 7 3
I was going to paste the entire query in but I thought I'd try this first to see if I could get by with a hint. So essentially I need to evaluate with an order by or something and then insert a number as amount_rank and number as count_rank.
Any ideas?
View 3 Replies
View Related
Jun 27, 2013
I am uploading input sample data and desired output data, can get the desire output.
View 4 Replies
View Related
Jul 15, 2013
I have this column Sdate in my Employees table. This value represent the start date working.
I need to get all of the employees that between 8 and 10 month of work from today. How can I do it?
View 3 Replies
View Related
Sep 8, 2006
Hello,I'm facing with a problem though it may look very simple for many ofu it is a bit complicated for me So kindly please help me outi have a table emp with employee id,name and mgridI need to display the managers and the employee working under thatmanager . How should this be done.Thanking uBroken Arrow
View 6 Replies
View Related
Jun 17, 2015
I have a table which has name,Speciality,start date and end date. So each person may have 1/more rows .They will have more if they change their specialities. For example if you look at the data below.
AdjusterNameSpecialtyDatestartDateEnd
Test Inside Property2009-08-29 2010-07-31
Test Management2010-08-012012-07-31
If we see at the data above Test has 2 rows because he changed his specialty in the middle.My requirement is to calculate the total number of employees in each month for last 2 years in each speciality. For example if we look at the example above, Test was in Inside property from 2009 Aug to 2010 Aug but if i use just the date start and take the month for each adjuster it gives me the number of adjusters started in that year and month but what i want is Test should be counted in all the months for Inside property until 2010 07 month. Which means i want to have the total number of adjusters present by each speciality for each month of last 2 years .
View 2 Replies
View Related
Mar 12, 2015
How do I filter a list of Employees where the Sum of "VALIDATED" hours is less than 80? For example.
Here is the flat table
SELECT EMP_NO, hours, IsValidated, rate_type
FROM Pay_Records
WHERE pay_period_id = 2
Order by EMP_NO
Output will be something like this
12345 | 2 |true |REG
12345 | 15 |false |OVR
12345 | 30 |true |OVER
33334 |2| true |REG
Total Validated hours for the Employee 12345 will be 32 NOT 47. How do I list employees who worked less than 80 validated hours. The hours are validated only when it is true.
View 2 Replies
View Related
Jul 10, 2014
I have a database of employees and pay rates.
One employee has two pay rates for two different jobs:
Job A: Rate $10.00
Job B: Rate $15.00
I will be updating their record so that they only have one job going forward, Job C. I need Job C to equal their HIGHER of the two existing jobs.
I have a select statement to find what the higher rate is. However, I am not sure how I can apply the rate to be the new job's rate. Here's what I used to find the highest rate for one single person:
SELECT max(rate), employeeID
FROM JobsTable
inner join IDTable
on JobsID2 = IDID2
WHERE JobCode in ('JOBA','JOBB')
and EmployeeID = '12345'
GROUP BY EmployeeID
(this returns the employee ID from one table, and the highest rate from Jobs A and B from another table)
I can get it to update to add JobC -- how can I get it to assign the result from the above query to be the rate used for Job C?
View 1 Replies
View Related
Jan 24, 2008
how to do this
i have table of employee ,evry employee have a unique ID "empid"
empid VAL_OK
--------------------------
111 0
222 0
333 0
now insert multiple insert to my work_table shifts for all month for evry employee
like this
(this is work_table)
empid date val
--------------------------------------------------
111 01/02/2008 1
111 02/02/2008 2
...............
111 29/02/2008 5
--next employee
222 01/02/2008 1
222 02/02/2008 4
...............
222 29/02/2008 6
--next employee
333
--next employee
444
--next employee
555
-------------------------------------------------------------
now i need for evry OK insert (for all month) each employee
go to the TB_Employee
and update each employee once !!
from VAL_OK=0 to VAL_OK=1
like this
empid VAL_OK
--------------------------
111 1
222 1
333 1
----------------------
like this i know who is the employee have shift for all month and who NOT !
i think it like this
Code Snippet
Create trigger for_insert on tb_work
For insert
begin
if @@rowcount = 1
Update tb_employee
Set
val_ok= 1
else
/* when @@rowcount is greater than 1,
use a group by clause */
Update tb_employee
set
val_ok= 1
select empid from tb_work
group by tb_work.empid
End
TNX
View 4 Replies
View Related
Jan 10, 2008
need help
how to check in table if all the employees have Full month
if it double days fix it
if the employees have less days ! > move to temp table
for eny problem with this employee (check continuity or error) move to temp table !
i have stored procedure that insert employees for next month "Full month"
from start of the month until end of the month
how to check continuity and if i don't give less days OR double days in month
like if the month is 29 days the employee must have 29 not more
for eny problem with this employee move to temp table !
situation 1 employees have less days !
sn empid ShiftDate day
-------------------------------------------------
1 111111 2008-02-01 Tuesday
2 111111 2008-03-02 Wednesday
3 111111 2008-04-03 Thursday
4 111111 2008-03-04 Friday
5 111111 2008-03-05 Saturday
6 111111 2008-03-06 Sunday
7 111111 2008-03-07 Monday
. ----------------------------------------------
8 111111 2008-03-09 Wednesday
9 111111 2008-03-10 Thursday
.......................................................................until end of the month
99 222222 2008-02-01 Tuesday
100 222222 2008-03-02 Wednesday
101 222222 2008-04-03 Thursday
102 222222 2008-03-04 Friday
. ----------------------------------------------
104 222222 2008-03-06 Sunday
105 222222 2008-03-07 Monday
106 22222 2008-03-09 Wednesday
108 22222 2008-03-09 Wednesday
109 22222 2008-03-10 Thursday
.......................................................................until end of the month
situation 2 employee have double days!
sn empid ShiftDate day
-------------------------------------------------
1 111111 2008-02-01 Tuesday
2 111111 2008-03-02 Wednesday
3 111111 2008-04-03 Thursday
4 111111 2008-03-04 Friday
5 111111 2008-03-05 Saturday
6 111111 2008-03-06 Sunday
7 111111 2008-03-07 Monday
8 111111 2008-03-09 Wednesday
9 111111 2008-03-09 Wednesday
10 111111 2008-03-10 Thursday
.......................................................................until end of the month
99 222222 2008-02-01 Tuesday
100 222222 2008-03-02 Wednesday
101 222222 2008-04-03 Thursday
102 222222 2008-03-04 Friday
103 222222 2008-03-04 Friday
104 222222 2008-03-05 Saturday
105 222222 2008-03-06 Sunday
106 222222 2008-03-07 Monday
107 22222 2008-03-09 Wednesday
108 22222 2008-03-09 Wednesday
109 22222 2008-03-10 Thursday
.......................................................................until end of the month
TNX
View 3 Replies
View Related
Jul 31, 2015
Basically I'm trying to produce a report that shows qualified employees for each program. Each employee can possess many qualifications. There will be no programID parameter submitted by the user. I just want to produce the report which shows the programs and the qualified employees for each. I thought I had a query that was working but once I added a different ProgramID into the ProgramModules table things went south.
Here are my tables:
tblEmployees (table of employees)
- EmployeeID
- EmployeeName
tblQualifications (table of qualifications)
- Qualification_ID
- QualificationTitle
tblEmployeeQualification (table of all employees qualifications)
-EmpQualificationID
-EmployeeID (fk for tblEmployees)
-QualificationID (fk for tblQualifications)
tblPrograms (table of programs)
-ProgramID
-ProgramTitle
tblProgramModules (table of qualifications required by each program)
-ProgramModuleID
-ProgramID (fk for tblPrograms)
-QualificationID (fk for tblQualifications)
Here is the query I was working with that works when there are only records in the ProgramModules table that use the same ProgramID
SELECT
tblProgramModules.TrainingProgramID,
tblEmployees.EmployeeID,
tblEmployees.EmployeeName
FROM
tblEmployees
[Code] .....
View 6 Replies
View Related
May 6, 2014
I would like to generate a working schedule for employees for x-days ahead based on a starting date that the user can enter.
I have got 3 relevant tables:
1. Table X with (1) resourcenumber, (2) starting date working schedule and (3) the daynumber representing the starting date (this is ISO so 1 for Monday, 2 for Tuesday etc.)
2. Table Y has the schedule itself and can hold a 7-days schedule or a 14-days schedule. In case of 7 days schedule there a 14 (!) records with (1) resourcenumber, (2) daynumber, (3) starting hour a.m. (4) ending hour a.m (5) starting hour p.m and (6) ending hour p.m. In case of a 14-days schedule there are 28 records (a.m. and p.m. records)
3. Table Z with resource data.
An example to clarify (for fake employee 100):
Table X:
Resource: 100
Starting date: 2012-03-01 (from this date the schedule will be effective)
Daynumber: 4 (2012-03-01 was a Thursday)
Table Y (Resource has a 14 days schedule because per 2 weeks Monday is an off-day):
Record 1 shows: Resource: 100, Daynumber: 1 (= Monday, working day), AM-Starting hour: 09:00, AM-Ending hour: 13:00, PM-starting hour: 13:30, PM-ending hour: 17:30
Record 2: same but daynumber is 2
Record 3: same but daynumber is 3 etc.
...
Record 8 shows: Resource: 100, Daynumber: 8 (= Monday, off-day), AM-Starting hour: 00:00, AM-Ending hour: 00:00, PM-starting hour: 00:00, PM-ending hour: 00:00
Record 9: same as record 2 but daynumber is 9.
etc.
...
Record 14: same as record 7 but day is 14 (= last day)
The weekend days show as 00:00 for the hours (same as day 8 in example)
I generated the working schedule with a CROSS APPLY function based on the starting date and the x-number of days ahead.
I then evaluate the actual daynumber corresponding with that date with the daynumber in table Y. That works fine with a 7-days schedule but I can't get it fixed with a 14-days schedule. Day 8 in that schedule represents an actual day 1 but how do I know what actual date day 8 is ... I think I have to start with the starting date in table X ...
I think ideally I would like to have the generated days as follows (as an example in case of a 14-days schedule starting 2014-05-01 for 30 days ahead):
2014-05-01 = day 4 (= actual daynumber)
2014-05-02 = day 5
2014-05-03 = day 6
...
2014-05-10 = day 13
2014-05-11 = day 14
2014-05-12 = day 1
2014-05-13 = day 2
2014-05-14 = day 3
...
2014-05-24 = day 13
2014-05-25 = day 14
2014-05-26 = day 1
2014-05-27 = day 2
...
2014-05-31 = day 6
With this done I can compare the actual daynumber with the daynumber in Table Y.
The rownumber that the CROSS APPLY function generates has to be reset to 1 after day 14. I tried PARTITION BY in THE ROW_NUMBER function but to no avail ... The only field I can partition by is the maximum value of the daynumber (14 is the example) but that is not allowed in the rownumber function.
View 0 Replies
View Related
Feb 22, 2007
Guys,
I've been asked to properly schedule some jobs on our system for a database following a full recovery model. I realized that the order for some of the jobs might matter and came up with the solution, which is described below.
Please let me know if this ordering makes sense and/or if you have any general comments/suggestions:
1. Backup all dbs [in case defragmentation messes something up]
2. Defragmentation [this will grow the transaction log]
3. Backup transaction log [backs up trans log and truncates inactive portion of it]
4. Shrink log file
Also, I've been previously suggested not to shrink the log file, unless absolutely necessary, as it is resource intensive. In my experience, however, this operation does not seem to be resource intensive at all. It took me 1 second to shrink the transaction log file from 25GB to 2GB.
Is it regrowing of the log file that will affect performance?
Also, if I do shrink the transaction log file, is there a suggested value to shrink it to - possibly a function of the database file size?
Thanks a lot
View 6 Replies
View Related
Jan 25, 2015
I have a table having 3 coulmns.
amnt1 amnt2 amnt3
100 200 230
200 170 120
290 188 299
800 170 120
i need to get max amount row wise like
amnt
230
200
299
800
View 4 Replies
View Related
Oct 29, 2006
Hi,
I have the following three tables :
Account (Id int, AccountName nvarchar(25))
Role (id int, Rights int)
AccountRole (AccountID, RoleID)
In Role table - Rights Column is a bit map where in each bit would refer to access to a method.
One account can be associated with multiple roles - AccountRole table is used for representing the N:N relation.
I want to develop a store procedure - which would return all AccountName and their Consolidated Rights.
Basically I want to do a BitWise OR operation for all the Rights in the Aggregation instead of the SUM as shown in the following statement.
SELECT Account.Name, SUM(Role.Rights) FROM Account WITH (NOLOCK)
JOIN RoleAccount ON RoleAccount.AccountID = Account.Id
JOIN Role ON RoleAccount.RoleId = Role.Id
GROUP BY Account.Name
Thanks,
Loonysan
View 6 Replies
View Related
Apr 18, 2008
My Table(collection) Structure consist of below:
TypeDate ItemAmount
REVENUE2007-04-01serv002***
REVENUE2007-04-01Serv004***
REVENUE2007-04-01serv005***
REVENUE2007-04-01AMC-***
ROYALTY2007-04-01serv002***
ROYALTY2007-04-01Serv004***
ROYALTY2007-04-01serv005***
ROYALTY2007-04-01AMC***
O/p should come as:
====================
Revenue:
MONTH AMC serv002 SER004 serv005
2007-04-01 *** **** *** ****
Royalty:
MONTH AMC serv002 SER004 serv005
2007-04-01 *** **** *** ****
View 6 Replies
View Related
Apr 2, 2007
Hi,
Need to write a query which would select data based on weekly wise based on the column Date.And week starts from Monday.
Hence would reqiure a function or a query which would select only those records which would fall under respective Weeks.
Help!!!
Regards,
Navneet
View 11 Replies
View Related
Dec 4, 2007
Dear All,
is there any difference performance wise using
select * from my_table
and
select mycol1,mycol2....mycoln from my_table
actually i've read from one article the there is big difference....
please clear my doubt...
thanks in advance
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 8 Replies
View Related
Feb 12, 2008
Dear All,
is there any way to generate daywise scripts in database? i mean all the transactions wether insert update or delete....
i need all the changes on that particular date(with in 24 hours)....
is there any procedure or function for this?
i've almost 1200 tables in that database.
note : due to replication is not possible for my scenario, i'm thinking in different ways to achieve my purpose.
this is my requirement
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96959
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 1 Replies
View Related
Jul 23, 2005
Hi All,I want to give sequence no group wise, as follows:Section EmpCode-----------------------A 1001A 1003A 1009B 1023B 1030C 1044-----------------------I want to set sequence no. group wise like this for above data.Seq. Section EmpCode------------------------------1 A 10012 A 10033 A 10091 B 10232 B 10301 C 1044----------------------------Suggestion in this regard will be appreciatedThanks in Advance,T.S.Negi
View 3 Replies
View Related
Apr 25, 2008
I have a group and a subgroup in a report. in the subgroup there are repetitive rows. But i want to display them at only once within the subgroup. So I hide them. Now I want to have the sum of a field viz. "quantity" group wise, having the only single value of the subgroup. But problem is in the sum the values which are hidden also included. But i don't want them to include in the sum of the group.
Can anybody give the suggestions how to resolve the problem..
I am fighting with it from a long back but i am not able to solve the issue.. plz help me out..
location invoice no. date quantity item
delhi inv/del/1 1/1/2008 3 item-1
item-2
item-3
inv/del/2 2/1/2008 2 item-4
item-5
Location Total 5
This is what i want... for invoice inv/del/1 there are 3 items, i hide rest of two rows using "Hide Duplicates", But the group sum i.e. Location Total in the report is coming out as '13' as it includes the quantities of hidden rows also. But in actual I want '5'.
thanx you..
View 3 Replies
View Related
Sep 9, 2015
i want to know how to use order by clause in row wise
for example, suppose the rows is
10 20 30 40
so the output is should be
10
20
30
40
View 3 Replies
View Related
