Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







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 

  


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
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
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
Return Just One XML Tag For Errors In A Large List Of Employee Data
Hi all,
 

I need some help on the SQL symtax to be able to return XML data including one section for an error description if an error is trapped.  I'd like to return data in the following format:
 
<Reply_A>
   <Employee>
       <Name>John Smith</Name>
       <Age>45</Age>
   </Employee>
   <Employee>
       <Name>Joe Doe</Name>
       <Age>37</Age>
   </Employee>
   <Employee>
       <Name>Mary Smith</Name>
       <Age>28</Age>
   </Employee>
   <Errors>
     <ErrorCode>10</ErrorCode>
     <ErrorDescription>Successfull</ErrorCode>
   </Errors>
</Reply_A>

Notice there is only one <Errors> section at the end.  The following [over simplified] SQL I have returns an <ErrorCode> tag for each <Employee> tag as I have the two variables on the SELECT part.  I added them there as I don't know where else I can have them to be able to create a separate group for error handling only rather then have it at the employee level.  Is there a JOIN I can do to split this?

 
SELECT emp.empid     as 'Name',
              emp.age        as 'Age',
             @iReturn        as 'ErrorCode',
             @descError    as 'ErrorDescription'
FROM 
       ...
FOR XML PATH('Employee'), ROOT('Reply_A')

Thanks, again, for your help/comments/suggestions!

ST

View Replies !   View Related
Business To Employee Web Based Application, Is Sqlexpress Allowed
Hello, as a ISV we develop a software product called Ultimo (www.ultimo.net).
I do have a license question: is it allowed for our customers to run our webbased application on IIS and use sql server express as a database.
The employees from the customer will use the application (intranet). For a small number of users sql express may do the job.
Untill now we always advice sqlserver with processor license.

With kind regards,

Erik.

View Replies !   View Related
SSRS Report Question To Calculate Overall Percentage For Individual Employee
Hi All,
 
I have an SSRS reports that uses a list table to display the name of staff and the name of the department that he supports as well as the number of hours they support the department. Below would be a sample of the report:
 
Name  Department  Hours
Staff 1      Dept 1        2
Staff 1      Dept 2        4
Staff 1      Dept 3        1
Staff 2      Dept 1        2
Staff 2      Dept 4        1
 
Now I would like to add 1 more column to show the percentage (%) of time spent at the department for the employee. Therefore each employee % of time spent will be 100% spread over all the supported departments. So the report will be as below:

 
Name  Department  Hours Time(%)
Staff 1      Dept 1        2       28.57
Staff 1      Dept 2        4       57.14
Staff 1      Dept 3        1       14.29
Staff 2      Dept 1        2       66.67
Staff 2      Dept 4        1       33.33
 
Is this possible to be created in SSRS as this is really required. If not is there any thing I can do on the DB or data set to show the time %.
 
Thanks & Regards,
Fadzli

View Replies !   View Related
How To Create Trigger For Multi Insert Employees Update Tb Employee Once
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 Replies !   View Related
955585 - Limited Term Employee / Infrastructure...position At Hewlett Packard
We are currently seeking persons with Enterprise SQL Serveradministration with experience using replication, log shipping, andclustering. Position is based in Bellevue, WA.Thanks,Join Bytes!www.hp.com/go/jobs

View Replies !   View Related
Employee Attendance With In/out Times (was &"Query Help Needed...&")
Dear Friends,
I am in problem & have to solve one query.
I have a one table with the employee time in & time out data, employee can go out & come in fequently in a day.

I want to know that how much time every emp have attend in the company per day.

Kindly, do reply as soon as possible.

I am enclosing data defination in txt file along with the data in the MS Excel file.

Thanx ....

View Replies !   View Related
Dynamic SQL &"sp_executesql&" And Employee ID List &"varchar&"
For the example stored procedure below, lets say I want to use the "sp_executesql" stored procedure instead of "EXECUTE".
 CREATE PROCEDURE [dbo].[spGetEmployees]
@managerId int,
@employeeIdList nvarchar( 200 )
AS

EXECUTE
(
'SELECT *
FROM [dbo].[hrEmployees]
WHERE [ManagerID] = ' + CAST( @managerId AS nvarchar ) + '
AND [EmployeeID] IN (' + @employeeIdList + ')'
)
 
 
I want to rewrite it something like this. Please see MSDN documentation ( http://msdn2.microsoft.com/en-us/library/ms188001.aspx ) for sp_executesql stored procedure usage.DECLARE @selectStatement nvarchar(500)
SET @selectStatement = 'SELECT * FROM [dbo].[hrEmployees] WHERE [ManagerID] = @paramManagerID AND [EmployeeID] IN (' + @employeeIdList + ')'


DECLARE @paramList nvarchar(500)
SET @paramList = '@paramManagerID int'

EXECUTE sp_executesql @selectStatement, @paramList, @paramManagerID = @managerId
 
Reason for using "sp_executesql" is the performance gain.However, as you can see, the @employeeIdList cannot be included as part of the Parameter List ( @paramList )like the @managerId since it **has** to be passed in as a varchar ( example: @employeeIdList = '1,2,3,4' ).
My Question Is there a way to include it as a parameter instead of it being part of the embedded dynamic SQL syntax?

View Replies !   View Related
I Have Created A Table Table With Name As Varchar And Id As Int. Now I Have Started Inserting The Rows Like, Insert Into Table Values ('arun',20).
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50.                 insert into Table values('arun's',20)  My sqlserver is giving me an error instead of inserting the row. How will you solve this problem? 
 

View Replies !   View Related
Moving From One Table To Other Table Automatically For Every 3 Months By Checking The Paticular Value Of The Table Field
Hi
 
I am having a table called as status ,in that table one field is there i.e. currentstatus.
the rows which are having currentstatus as "ticket closed",i want to move those rows into  other table called repository which is having same table structure as status table.
I can do programatically.
but is there any way for every 3 months system has to check and do this action means moving to repository table automatically?
 
Please help me.
 
Thanks.

View Replies !   View Related
Default Table Owner Using CREATE TABLE, INSERT, SELECT && DROP TABLE
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.

View Replies !   View Related
Dbo.Table Of A Database In The .SQLEXPRESS Object Explorer: How To Copy The Dbo.Table To The Another Blank Dbo.Table?
Hi all,
 
The following dbo.Tables of Northwind.mdf in my .SQLEXPRESS (SQL Server Management Studio Express) are missing:
      dbo.Categories
      dbo.CustomerCustomerDemo
      dbo.CustomerDemographics
      dbo.Customers
      dbo.Employees
      dbo.EmployeeTerritories
      dbo.Order Details
      dbo.Orders
      dbo.Products
      dbo.Regions
      dbo.Shippers
      dbo.Suppliers
      dbo.Territories.
 
But, I have these dbo.Tables in a different Database "xyzDatabase".  How can I  copy each of these dbo.Tables to the another blank dbo.Table of Northwind Database? 
 
I right clicked on the dbo.Categories and I saw the following thing:
          dbo.Categories
                   New Table...
                   Modify
                   Open Table
                   Script Table as |>   CREATYE To |>
                                                DROP To |>
                                                SELECT To |>
                                                INSERT To |>   New Query Editor Window
                                                                       File....
                                                                       Clipboard
                                                UPDATE To |>
                                                DELETE to |>
From the above observation,I think it is possible to copy the dbo.Table from the one Database to the Northwind Database that needs to be repaired.    Please help and advise me how to do this task or tell me where I can find the Microsoft document that gives the details of this X-copy thing.
 
Thanks in advance,
Scott Chang
 
P. S.  I am using VB 2005 Express to create a project to learn "Calling Stored Procedures with ADO.NET" (see Paul Kimmel's article in http://www.developer.com/db/article.php/3438221) that needs the dbo.Tables of Northwind Database and my Northwind Database has been screwed up for quite a while and needs a big repair.       

View Replies !   View Related
What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure
 which is more efficient...which takes less memory...how is the memory allocation done for both the types.

View Replies !   View Related
How To Search Multiple Table Which Table Name Is Store In Another Table And Join The Result Together?
I have one control table to store all related table name
 Table ID                   TableName
     1                           TableA
     2                           TableB
 
In Table A:
RecordID                Value
     1                         1
     2                         2
     3                         3
 
In Table B:
RecordID             Value
    1                         1
    2                         2
    3                         3
 How can I get the result by select the Table list first and then combine the data in table A and table B?
 
Thank you!

View Replies !   View Related
Stored Procedure To Copy Table 1 To Table 2 Appending The Data To Table 2.
Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.

View Replies !   View Related
ODBC-linked Table Update On Table ' Table Name ' Failed.
Hello All:Have a problem that I have never seen before and have not found anythingontechnet on how to resolve it. I have a SQL Server 2000 server that Ihavecreated a new database. I then used DTS to import data from a MS Access97database. Before making the import, I made changes to the MS Accessdatabaseand changed the autonumbers to integers. I also removed allrelationships.My front end is still in MS Access 97 and I am linking ODBC tables fromtheSQL Server. Here's the issue, I can not make any changes to any data inanytable. I get the following message:ODBC-linked table update on table 'Registry' failed.According the help for this, the database is in read-only mode. Icheckedthe MS Access front end and all my settings on the SQL Server. Nothingis inread-only. I did not use the Linked Table Manager to relink the tables,which can cause issues.After this message I get the following message:The text, ntext, and image data types cannot be compared or sorted,exceptwhen using IS NULL or LIKE operator.Which is error 306 and has a severity of 16.On the same SQL Server using Northwind I do not have any issues. Anyideaswould greatly be appreciated. Thanks.Katuil*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !   View Related
Newbie-DELETE A Record In A Table A That Is Related To Table B, And Table B Related To Table A
Hi thanks for looking at my question
 
Using sqlServer management studio 2005
 
My Tables are something like this:
 
--Table 1 "Employee"
CREATE TABLE [MyCompany].[Employee](
      [EmployeeGID] [int] IDENTITY(1,1) NOT NULL,
      [BranchFID] [int] NOT NULL,
      [FirstName] [varchar](50) NOT NULL,
      [MiddleName] [varchar](50)  NOT NULL,
      [LastName] [varchar](50)  NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
      [EmployeeGID]
)
GO
ALTER TABLE [MyCompany].[Employee] 
WITH CHECK ADD  CONSTRAINT [FK_Employee_BranchFID]
FOREIGN KEY([BranchFID])
REFERENCES [myCompany].[Branch] ([BranchGID])
GO
ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]
 
-- Table 2 "Branch"
CREATE TABLE [Mycompany].[Branch](
      [BranchGID] [int] IDENTITY(1,1) NOT NULL,
      [BranchName] [varchar](50) NOT NULL,
      [City] [varchar](50)  NOT NULL,
      [ManagerFID] [int] NOT NULL,
 CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED
(
      [BranchGID]
)
GO
ALTER TABLE [MyCompany].[Branch] 
WITH CHECK ADD  CONSTRAINT [FK_Branch_ManagerFID]
FOREIGN KEY([ManagerFID])
REFERENCES [MyCompany].[Employee] ([EmployeeGID])
GO
ALTER TABLE [MyCompany].[Branch]
CHECK CONSTRAINT [FK_Branch_ManagerFID]
 
--Foreign IDs = FID
--generated IDs = GID
Then I try a simple single row DELETE
 
DELETE FROM MyCompany.Employee
WHERE EmployeeGID= 39
 
Well this might look like a very basic error:
I get this Error after trying to delete something from Table €œEmployee€?
 
 
The DELETE statement conflicted with the
REFERENCE constraint "FK_Branch_ManagerFID".
The conflict occurred in database "MyDatabase",
table "myCompany.Branch", column 'ManagerFID'.
 
Yes what I€™ve been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a €œBranch€? entry, basically each entry in €œbranch€? and €œEmployee€? is child of each other which makes things more complicated.
 
My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use €œON DELETE CASCADE€? or something?
 
Thanks
 

View Replies !   View Related
Is A Temp Table Or A Table Variable Used In UDF's Returning A Table?
In a table-valued UDF, does the UDF use a table variable or a temp table to form the resultset returned?
 

View Replies !   View Related
Difference In Creating Temporary Table By #table And ##table
Banti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference.
pls reply.
first:
create table ##temp
(
name varchar(25),
roll int
)
insert into ##temp values('banti',1)
select * from ##temp
second:
create table #temp
(
name varchar(25),
roll int
)
insert into #temp values('banti',1)
select * from #temp

both works fine , then what is the difference
waiting for ur reply
Banti"

View Replies !   View Related
Delete Table And Immediately Crate Table, Error Occur &&"Table Already Exist&&"
'****************************************************************************

Cmd.CommandText = "Drop Table Raj"

 

Cmd.ExecuteNonQuery()


Cmd.CommandText = "Select * Into Raj From XXX"

 

Cmd.ExecuteNonQuery()

'**************************************************************************
 
 

This generates error that Table already exist.
 
If Wait 1 sec then execute statement then it works fine.

 
 
Thanks in Advance
 
Piyush Verma

View Replies !   View Related
How To Create A System Type Table/ Change User Table To System Table.
Is there any Posibility to change a User Table to System Table.

How to create one system table.

I am in Big mess that One of the Table I am using is in System Type.

I cant Index the same. Is there any Mistake we can change a user table to system table.....

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved