How To Get Top Three Salary Getters From Table Employee

Dear All,
i want to know how to get top three salary getters from the employee(eid , ename, salary) table

i tried this
select  top 3  salary from employee order by salary desc       

but it gives me top three salary record say there is salary 1000,1200,1300,1300,1500
then my query return me 1500,1300,1200 whereas i want to 1500,1300,1300,1200

how can i do it

please help

thanks 

  



ADVERTISEMENT

Update The Salary Of Each Manager To Be Double The Average Salary Of The Employees He/she Manages

create table employee(empid int,empname varchar(20),managerid int notnull, sal int)insert into employee values(1,'ranga',22,5000)insert into employee values(2,'satish',22,8000)insert into employee values(3,'sunil',11,4500)insert into employee values(4,'sridhar',22,2000)insert into employee values(5,'ramesh',33,12000)insert into employee values(6,'srini',22,16000)insert into employee values(7,'sashi',33,54000)insert into employee values(8,'rajani',22,71000)insert into employee values(9,'praveen',11,6060)insert into employee values(10,'bhaskar',22,11120)insert into employee values(11,'baba',33,9000)create table employment (managerid int,managername varchar(20),sal int)insert into employment values(11,'rob',2500)insert into employment values(22,'babu',5000)insert into employment values(33,'ram',6000)now my problem isUpdate the salary of each manager to be double the averagesalary of the employees he/she managespls helpsati

View Replies View Related

Using Employee/Boss Self Referencing Table

I have an Employee table that has
EmployeeID (PK)
SupervisorID (which is really just another EmployeeID)
..random junk...


Now that part makes sense, everyone gets one and only one boss.

Their boss can change, and therefore the SupervisorID would be updated.

Now I have an EmployeeEvals table that has quarterly evaluation data.

I want to relate these two tables.

Eval table has
EvalID (PK)
ReviewedEmployeeID (the one being evaluated)
SupervisorID (the one doing the evaluation)

Now I need to link this back to the employee table (at least I think I do).

So I would want to relate it by the ReviewedEmployeeID going back to EmployeeID in the employee table and I also want the SupervisorID to do the same...

But of course that won't work because that would seem to indicate that a single record on the Employees table (say EmployeeID 55) should have a matching (or could) record in the Eval table that would look like
EvalID: 12345
ReviewedEmployeeID: 55
SupervisorID: 55

which of course wouldn't happen as an employee wouldn't evaluate themself.

How do I handle the relationships for this properly?

Do I just not link the SupervisorID back to anything?

View Replies View Related

TSQL Statement For Adding Employee Numbers To A Table

What is the easiest way to fill a column with sequencial numbers. The name of the column is empno an is 4 characters long. I would like to start with '0001' and continue to add 1 to the empno until the end of the table.

Thanks for your help in advance.

View Replies View Related

I'm Trying To Get The Last Activity Date From W/in A Table Where There Are Multiple Rows Per Employee

Hi, I have been struggling trying to design a query that will alow be to select the most recent date in a table
and I'm obviously not having much luck
 
This is basically the table layout, note each employee can have multiple rows with different dates
 





Employee_ID

Last_Name

First_Name

Evaluation_Date

Evaluation_Score


1

Jones

Tom

01/04/07

40


1

Jones

Tom

01/.12/07

50


1

Jones

Tom

04/01/08

60


2

Smith

Ed

02/14/05

70


2

Smith

Ed

03/18/06

80


3

Brown

John

06/23/04

80


3

Brown

John

12/23/04

79


3

Brown

John

01/07/06

50


3

Brown

John

10/22/08

69

 
What I'd like to do would be to write some thing that would return the following, just the last date of the evaluation & whatever relevant data is in the table
 





Employee_ID

Last_Name

First_Name

Evaluation_Date

Evaluation_Score


1

Jones

Tom

04/01/08

60


2

Smith

Ed

03/18/06

80


3

Brown

John

10/22/08

69

 
I've looked at select distinct and the date operatives with out any success.
 
Thanks Much
Vince

View Replies View Related

Need -&> Max(sum(salary)) Query

hi

i have a table employee:

dept ename salary
---- --------- -------
10 A 2500
20 B 3500
30 C 4000
20 D 5500
10 E 4500
30 F 5200


FIRST QUERY:
select dept,sum(salary) from employee group by dept

the above one is working fine..

after working the first query output,
i want to select the dept,max(sum(salary)) from the table...

how?? could any one send me immediately...

thanks in advance

View Replies View Related

SQL Server DBA Salary

How much a senior Sql Server DBA makes per year as an employee?
How much a senior Sql Server DBA makes per year as a contractor?

What job title makes the most application developer consultant or DBA sql server.

Is there a site where there is a salary reference for those jobs:


Thanks for your response.

Ahmed

View Replies View Related

SQL To Increment Salary

i want to write a SQL statement to increment the salary by 10% for technicians who have done three tests on a particular date.

there are two employee types.(1)technicians (2)traffic controllers.
employee category is defined in "Type" attribute of Employee table. the increment should happen only to technicians.thank you in advance.

Employee (EmployeeID,Name,Salary,Tpye)

TestEmployee(TestNo,EmployeeID,Hrs)

Test(TestNo,TestDate,Result)

View Replies View Related

Max Salary Query

Hi

How to get the maximum salry from a table without using top and
aggreate function

thanks

asm

View Replies View Related

Hiked Salary...

--Table Empmaster:-
create table Empmaster
(
empid int identity(1,1) constraint pkempid primary key clustered,
empname varchar(10),
empsalary numeric
)

insert Empmaster(empname,empsalary)values('Imran',5000)
insert Empmaster(empname,empsalary)values('Raja',5000)

--Table Salary:-
create table Salary
(
salid int identity(1,1) constraint pksalid primary key clustered,
empid int constraint fkempid foreign key references Empmaster(empid),
dos varchar(10),
salary numeric
)

insert Salary(empid,dos,salary)values('1','2005-08-01','5000')
insert Salary(empid,dos,salary)values('2','2005-08-01','5000')
insert Salary(empid,dos,salary)values('1','2005-09-01','5000')
insert Salary(empid,dos,salary)values('2','2005-09-01','7000')
insert Salary(empid,dos,salary)values('1','2005-10-01','7000')
insert Salary(empid,dos,salary)values('2','2005-10-01','7000')

i have two tables with relations. how do i find out whose salary has been hiked for the 9th month?

View Replies View Related

SQL Server DBA Salary Survey

I am going into salary negotiationyearly review next month after my week off and I am trying to determine what to ask for. I am thinking another 10K and an extra week off would not be unreasonable, but I wanted to get some idea of what you guys think I should ask for in terms of pay.I have been developing software for seven years and I have been a dba for a little over five. I live in pricey northern Virginia. On a fairly regular basis I do about 55 to 60 hour weeks. On my DBA team, I am the only one who can handle both development and production tasks. The others are strictly developers. Although I have been relieved of most of my customer support tasks by our newbie, the customer support manager still brings the nastier bits to me. It is my perception that the more complex tasks get assigned to me. I get told on a regular basis that I am the best dba this place has ever had and other embarrassing accolades are regularly thrown my way. After a year, other than my boss I am the dba that has been here the longest in high turnover high burnout company. This year as we try to move to a SAP model, it looks like we will be going to 24/7 support on a disaster recovery model I am designing and implementing, so I guess I am getting the pager.So how much money should I be asking for?you can PM me with a number if you want.

View Replies View Related

How To Write A Query For 1st, 2nd, 3rd Max Salary

employee table having columns employeeid, salary

i want to write a query to get 1st, 2nd and 3rd max salary ?

View Replies View Related

Calculating Salary Per Hour?

is there a way to create a SELECT clause which counts the accumulate hours from tw columns in same row (entering hour and leaving hour) and then calculating the total price according to a parameter?

Shimi

View Replies View Related

Trigger To Increment The Salary By 10%

i want to write a database trigger to increment the salary by 10% for technicians who have done three tests on a particular date.

there are two employee types.(1)technicians (2)traffic controllers.
employee category is defined in "Type" attribute of Employee table. the increment should happen only to technicians.thank you in advance.

Employee (EmployeeID,Name,Salary,Tpye)
TestEvent(TestNo,EmployeeID,TestDate)

Hussain

View Replies View Related

Employees And Their Department Who Is Top Salary

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 Replies View Related

TRigger To Increment Salary By 10%

aa

View Replies View Related

What Is The Query To Find A 5th Highest Salary With Sqlserver.

What is the query to find a 5th highest salary.in emp table.i also use top1,top2,..but i don't get a result.what is new in sql server 2005.

View Replies View Related

Stored Procedure To Calculate Month Salary(urgent)

i want to calculate the month salary of an employee.which will be calculated on the basis of previous available leaves and present available leave(i.e) 2 per month.

View Replies View Related

Show 1 Page Per Every Employee

I have report created in SQL 2005 Reporting Services, which needs to show 1 page of information per every Employee in the company. Master info (Employee Name, Address) should be at the top of every page and  Details info should come below Master info.
There should be 2 ways to run this report:
1. per specific Employee.
2. for All Employees in the company.
Report has 2 parameters €“ prmAllEmployees and prmEmployeeName.
prmAllEmployees can have 2 values (No or Yes). prmAllEmployees is non-queried parameter and default value is No.
prmEmployeeName values are coming From query (from Dataset). prmEmployeeName value should be  irrelevant when prmAllEmployees value is Yes.
I checked boxes €˜Allow null value€™ and €˜Allow blank value€™ for prmEmployeeName.
Report is working fine for specific Employee when I select €˜No€™ value for prmAllEmployees and select specific Employee Name for prmEmployeeName.
I have 3 questions and the critical for me is to find solution for the last question (#3).
My questions are:
1. When I select Yes for prmAllEmployees and try to run report, I get Parameter Error massage saying €˜Please select a value for the parameter Employee Name€™ despite that check boxes €˜Allow null value€™ and €˜Allow blank value€™ are checked for prmEmployeeName. Why this happens?
2. Let say I want to get multiple copies of report for specific Employee, but I don€™t want to run report multiple times. How can I run report once and get three copies of report for specific Employee?
3. Running report for All Employees doesn€™t come up right. If prmAllEmployees is Yes then report should produce 1 page per every Employee in the company. What happens now - it is showing 1 page total with random Employee Name and all records related to all employees combined on this page. It looks to me that what I need here is to find out how to put some kind of Loop code on top of this report to make it run for each Employee inside this loop. The simplified query for Dataset that feeds Detail info for Employee looks like this:
Select fldDetail1, fldDetail2, fldDetail3 From TblName Where (TblName.EmployeeID = CASE WHEN @prmAllEmployees = 'No' THEN @prmEmployeeName ELSE TblName. EmployeeID END)
I understand that this query brings details for All Employees when prmAllEmployees value is Yes, but again I don€™t see how to make Details related to specific Employee stay on 1 page and Details for another Employee go to another page.
Please email your comments directly to svaysband@swinerton.com

View Replies View Related

How To Get All Employees Under Any Perticular Manager Employee !

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 Replies View Related

Finding Designation Of An Employee On A Given Date

Hi all,I have two tablesCREATE TABLE [JEMP] ([EMPID] [int] NOT NULL ,[DESIGID] [int] NULL , -- CURRENT DESIGNATION OF EMPLOYEE[DOB] [smalldatetime] NOT NULL) ON [PRIMARY]GOCREATE TABLE [JPRO] ([PromoID] [int] IDENTITY (1, 1) NOT NULL ,[EmpID] [int] NOT NULL ,[EffectiveDate] [smalldatetime] NOT NULL ,[NewDesigID] [int] NOT NULL , -- PROMOTED TO DESIGNATION[DesigID] [int] NULL -- PROMOTED FROM DESIGNATION) ON [PRIMARY]GOINSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(1,1,'1962-03-11 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(2,25,'1980-10-7 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(3,8,'1978-04-05 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(4,7,'1962-07-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(5,22,'1973-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(6,55,'1971-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(7,11,'1973-09-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(8,22,'1975-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(9,22,'1977-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(10,23,'1984-07-11 00:00:00')INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(3,'2002-15-11 00:00:00',7,20)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(3,'2003-03-01 00:00:00',8,7)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(4,'2002-01-04 00:00:00',20,22)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(4,'2005-05-01 00:00:00',7,20)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(5,'2001-10-01 00:00:00',22,23)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(6,'2001-08-01 00:00:00',55,NULL)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(7,'2003-10-01 00:00:00',11,8)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(8,'2001-09-01 00:00:00',22,23)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(9,'2002-01-05 00:00:00',22,23)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(10,'2002-11-01 00:00:00',24,25)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(10,'2003-11-15 00:00:00',23,24)--I wish to find the designation of employee on given date by usingpromotion and master table . I am using the following query to get theresultselect isnull( ( select top 1 newdesigid from JPRO whereempid=1 and effectivedate<'anygivendate' order by effectivedate desc ), (select desigid from empmast where empid=1) )It did give the result but looking for better method to solve this.With regardsJatinder

View Replies View Related

Employee Suggestions Workflow Template

There is an example on the page mentioned below of using the workflowdesigner for Access / SQL server to manage an Employee Suggestion databaseand workflow:http://msdn.microsoft.com/library/e...erDirectory.aspIs there a team solution template readily available for the Suggestionssolution? Or does anybody have a template which can be used for a quickstart?Sriram

View Replies View Related

Where Can I Get Phony Employee Test Data?

Hi all,

Can anyone tell me where I can find phony data for an employee HR database?
I need at least a hundred records or so for demo purposes.

any advice would be greatly appreciated.

Alrene

View Replies View Related

Advantages/Disadvantages B/w Being Consultant Vs Employee

Hi, I would appreicate your opinion/ feed back about Being consultant vs Being employee.

What are the advantages/disadvantages.
Does consultant makes more money than an employee and why so


Thanks for your response
Ali

View Replies View Related

Month Wise Employee Count

Hi Folks,

Please help me in writing a query. I need to get current strength of employee's, monthwise (till each month).

Table Structure
----------------
Employee_Master

ED_EmpId ED_DOJ ED_Name
-------- ------- ---------
2006-01-02 SRINIVASA
2006-01-02 KAVITA
2006-01-02 VIVEK
2006-02-20 CHANDRA
2006-02-25 PARIMAL
2006-02-30 SATISH
........
2007-01-10SANJEEV SHARMA
2007-01-14 JITENDRA PRATAP SINGH
2007-03-15 SANDEEP
2007-03-02 SUNIL KUMAR SHARMA


I require the data in the below format..

For the year '2007-2008'

Month Count
----- -----
April(07) 10
May . 10
June . 10
July . 10
August . 10
Sept 10
Oct 10
Nov 10
Dec 10
Jan (08) 10
Feb . 10
March . 10



Thanks.

View Replies View Related

How To Combine 2 Tables Employee And Holiday

 how to
how to combine 2 tables but like this

table 1
table Employee on work
------------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1

98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
---------------------------------------
table 2

table Employee on holiday
------------------------
empid start_date end_date shift
-----------------------------------------
12345678 11/04/2007 31/04/2007 10

98765432 01/04/2007 19/04/2007 10

------------------------------------------
how to create a view that show me and combine the 2 tables
all month from first day of the month until the end of the month like this
-----------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
12345678 11/04/2007 10
12345678 12/04/2007 10
12345678 13/04/2007 10
12345678 14/04/2007 10
.................................
.................................... ...................add the missing date until the end of the month
12345678 31/04/2007 10



98765432 01/04/2007 10
.................................... ...................add the missing date from the start of the month

98765432 02/04/2007 10
................................
..............................
98765432 16/04/2007 10
12345678 17/04/2007 10
98765432 18/04/2007 1
98765432 18/04/2007 10
98765432 19/04/2007 10
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
---------------------------------------


TNX

View Replies View Related

Employee Count By Two Distinct Indicators

 

This seems simple enough, I'm trying to get a count of employees by different indicators. First total by job code, second by Union_Code.

So I'm looking to count every one by distinct job codes inside a distinct Union_code first, then total the number for that distinct union.

Any help would be appreciated.

 
 
 
SELECT  emp.job_code,
            tbljob.job_title, 
            emp.union_code

FROM         emp

 INNER JOIN
 empjob ON empjob.emp_id = emp.emp_id
 AND empjob.company = emp.company
 AND empjob.job_code_id = 'PRIM'

 INNER JOIN
 tbljob ON tbljob.job_code = empjob.job_code
 AND tbljob.company = empjob.company

WHERE     (emp.company = 'ABC')
ORDER BY emp.union_code, empjob.job_code

View Replies View Related

Deciphering Employee Shift Data

Here is my current challenge that I realize I could do a few different ways but nothing efficient or flexible comes to mind.  Hoping one of you guys can help me out with an elequent efficient T-SQL script.
 
Employee workstate information in our system is stored by segment.  Segment 1 cooresponds to the entire shift while segments greater than 1 coorespond to subsequent breaks during the shift (Segment 1).  i.e.
 
SegmentNumber     SegmentStart     SegmentEnd

1                           10:00am             06:00pm
2                           12:00pm             13:00pm
3                           03:15pm             04:00pm

 
What I would like to do is generate continuous nonoverlapping records.  i.e.
 
SegmentNumber     SegmentStart     SegmentEnd

1                           10:00am             12:00pm        
2                           12:00pm             13:00pm
3                           13:00pm             03:15pm
4                            03:15pm            04:00pm
5                            04:00pm            06:00pm

 
*Notice End has changed to match start of first break and so on.  Also records have been added to fill gaps between breaks during the shift.
 
What adds to the challenge is that the segment number for a given employee/report day can be 1 (meaning no breaks) to any number (lots of breaks).  The segment start and end times can be any increment.  In addition Breaks can be divided into paid time and unpaid time but I imagine given a solution to the above I could apply it to another level down on my own. 
 
Thank you in advance for your help!

View Replies View Related

SQL Count Number Of Tickets Employee Has Open

Hi, I am new to ASP and SQL. I have a sql database that has two tables. One is Employee Table and the other is Ticket Table and they are joined on EmployeeID.
Employee Table:
EmployeeID, FirstName, Lastname,
Tickets Table:
TicketID, EmployeeID, Status,Priority
I need a SQL statement that will list employee with the number of tickets he has open. For Example:
John Doe (10) . This list employee John Doe with 10 Tickets that have a status of Open.
I have tried count(*) , Max and everything I could find on web but I can not put the two together. I can get the total number of tickets that have a status of Open, I can even do a distinct statement that will show all employee's, however I can not figure out how to retreive both at the same time for each employee. Please help.

View Replies View Related

SQL Statement To Compute Employee Pay For A Year At Different Pay Rates

I would like a single SQL to return all employee's total billablecompensation for a year. Their billable rates change throughout the year sounder the employee table (one), there is a compensation table (to many)which has the employee id, effective date, billable hourly rate. So in agiven year calendar year they could have many different (though usually 2 atmost) rates. These rates then have to correspond to and e multiplied bytheir corresponding billable hours from the time sheet table.I know I could create a series of UNIONs and hard code the effective dates,i.e.select from time sheets where employee=john and timesheet.task_date betweenjan 1 and jun 1, compensation.billable rate * timesheet.billable hoursUNIONselect from time sheets where employee=john timesheet.task_date between jun1 and dec 31 compensation.billable_rate * timesheet.billable_hoursI'd have to do that for every employee in a very large SQL.Is there an easier way using straight SQL? If not could it be done with astored procedure?Thanks for any insight.

View Replies View Related

How To Insert Employee Record Along With Multiple Email Ids Into The Sql Database?

Hello Everyone,
I am bit confused.
I am using vwd2005 express,c# and sql express.
I have a webform that registers new employee(title,name,age,address,phone,email) and inserts those data into the sqldatabase.
The problem is there might exists 'N' number or email ids and phone nos for a single user.
for eg: user A might have 3 email address where has user B might have 5 email address.
so in that case its not appropriate to create 5 textboxes in the webform and create 5 column like email1,email2,email3,email4,email5 in the database for both users..
i hope your getting the point.
I tried to create a separate table for emails and phone.
But i am new to relational database.
So if i need to use Relational database then could anyone help me here to :-
1.create a table structure here.(what would be the structure of tbl_employee, tbl_email and tbl_phone)
2.How to write insert query if i am using RD here?
plz help explaining the concept with a simple running example.
Thanks in advance.
Jack.
 
 
 
 

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved