How To Get All Employees Under Any Perticular Manager Employee !

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

How To Create Trigger For Multi Insert Employees Update Tb Employee Once

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

SQL Server 2012 :: Find All Employee Whose Salary Sum Is 80% Of Sum Of Salary Of All Employees

Aug 14, 2014

Let us assume that there are 100 employee in a company. And sum of salary of all employee is 10000. Find list of highest paid employees whose sum of salary is 8000. Remaining employee will fall in 20% bracket.

View 4 Replies View Related

Find All The Employees Under One Manager (was Need Help With Query)

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

How To Display The Managers And The Employees Working Under That Manager

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

Query To Find Manager Name From Employee Table Without Joins

Oct 25, 2012

Table structure is very simple as below and I know there are solutions with joins (Left outer joins), need to know if it is possible to get o/p without using joins

Note:- also need records who doesn't have manager (null)

table structure
eid------ename------mgrid
1------Nancy------2
2------Andrew------null
3------Janet ------2
4------Margaret------2
5------Steven------4
6------Michael ------5

o/p
Employee------Manager
Nancy------Andrew
Andrew------Null
Janet ------Andrew
.
.

View 9 Replies View Related

Reporting Services :: Employee Not Showing Under Correct Manager

Jan 8, 2015

I have been using this query inside the package that I created to load data into cube. From this cube I am fetching records for my cascaded parameterized report. The issue here is one of the employee is showing under different team manager and site manager. This team manager has left the company and now the employee is back to its previous team manager. here is the code:

Truncate table CallCenterEmployee.dbo.tblCube_Staff;
GO
Insert Into CallCenterEmployee.dbo.tblCube_Staff
SELECT dbo.tblStaff.Emp_N
, CONVERT(varchar, dbo.tblStaff.Emp_N) + CONVERT(varchar, tblDate.ID) AS SurrogateKey
, dbo.tblStaff.StaffId
, dbo.tblStaff.First_M
, dbo.tblStaff.Last_M

[code]...

View 5 Replies View Related

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

Mar 23, 2006

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

How To Find A Perticular Row?

Jul 4, 2007

Hi all,

I want to select last but one row in a table. I shouldn't use pk_id of the table ,because the entries in a table are jumbled.

I want the last but one row that as entered.

Thanks in advance
venkat

View 14 Replies View Related

How To Get One Perticular Date Of Each Month

Oct 9, 2007

Hi All,

I want to include a column to my report. The column name is Payment Due date. Payment due date is 05th of the each month. There is no field in data base tables called payment due date. Can anyone help me how to write the code to get pay ment due date for different customers.

As a example

Payment Due date can be 01/05/07 or 12/05/06 or 03/05/07. But its always 5th of each month.

Thanks

View 3 Replies View Related

Set The UserId And Password For Perticular Database In Sql Server 2000

Jul 27, 2006

I want to set userID and Password for perticular database without affecting sql server userid and password in sql server 2000 :)

View 3 Replies View Related

URL Access Directly To Subscription Page Of A Perticular Report.

Dec 18, 2007

Hi,

I have a .net application in this application I want to call directly subscription page of a perticular Report through URL.
And i want to hide the header section too.Is it possible
If not how can i provide this feature of subscription in my application

Regards

View 1 Replies View Related

How To Make A Table To Display Perticular Number Of Rows Per Page??

Nov 29, 2007

Hi,
Can anybody tell how to ristrict the number of rows per page in SSRS?
I have a table in my report.I know we have to use" =Int(RowNumber(Nothing)/25)" in group expression.But I already have 3 group expression in my table.Where to write??
Thanks.

View 9 Replies View Related

Visio: Employees And Managers

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

How To Sum All Salary In A Year Given To Employees

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

Employees And Their Department Who Is Top Salary

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

Selecting Top Employees From Each Company

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

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

Rank The Employees By Count And Amount (was Query Help)

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

How To Get Employees Attendance As Desired Output From The Input

Jun 27, 2013

I am uploading input sample data and desired output data, can get the desire output.

View 4 Replies View Related

Select Employees By Start Day For 8 Month Working

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

Transact SQL :: How To Calculate Number Of Employees In Each Month

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

PSI: How To Update Custom Field Value For A Perticular Project Using PSI(Project Server 2007)

May 6, 2007

Hi,

I want to update value of a custom field for a perticular project in Project Server 2007 using PSI.

I have created 5 enterprise custom fields(A,B,C,D,E) through PWA/Server Settings.

I want to search all Projects on Server. If any project is having value for custom field A then I want to update rest of the custom fields(B,C,D,E) for that perticular project.

I dont know how to do it please help.

Thanks in Advance

Madhukar

View 5 Replies View Related

SQL Server 2008 :: Filter List Of Employees Where Sum Of VALIDATED Hours Is Less Than 80?

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

Database Of Employees - Find Highest Pay Rate For One Single Person

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

How To Check If Employees Have Full Month - If Not Move To Temp Table

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

How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?

Apr 23, 2008

Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?

How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.

View 1 Replies View Related

SQL Server 2012 :: SELECT Qualified Employees Based On Program Requirements

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

TOP 1 For Each Employee

Jul 19, 2013

I need to select last order for each employees for homework. I use northwind database for testing. I can solve it by correlated subquery but the professor said me it is not optimized.

select orderid, customerid, employeeid, orderdate
from orders as o1
where orderdate = (select max(orderdate) from orders as o2 where o1.employeeid = o2.employeeid);

View 6 Replies View Related

T-SQL (SS2K8) :: Generate Working Schedule For Employees For X-days Ahead Based On Starting Date

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

Update Employee ID Throughout Database

Aug 15, 2012

I have a database that has dozens of tables. Many of these tables reference the employee ID.For example tblDaysOff has a column employeeID that is matched on tblEmployees.ID, and there are many such tables.

Now the employee IDs are changing the way they are generated. Instead of a alphanumeric value being stored as a text value, all employee IDs will be uniqueidentifiers stored as text values.The question is, how can I change every instance of "somevalue" in every record in every column where the column name is "employeeID" in every table in the database to "differentvalue" where employeeID = "somevalue"?This is what I have cobbled together from multiple sources ... but there is a syntax error where @max is located.

Code:
USE CsDB
DECLARE @t TABLE(tRow int identity(1, 1), tSchemaName nvarchar(max), tTableName nvarchar(max))
INSERT INTO @t
SELECT SCHEMA_NAME(schema_id), t.name
FROM sys.tables AS t
JOIN sys.columns c ON c.object_id = t.object_id
WHERE c.name LIKE '%employeeID%'

[code]...

Obviously I don't want to run this and then have to try and recover the database when things go away.

View 14 Replies View Related

Sum Quantity With Like Employee And Itemname

Nov 8, 2014

I have a table Item_used like this

Itemname Employee Quantity
pencil samlopez 10
pencil samlopez 5

All I want is to make a report that sum all the quantity of the same items with the same employee like this

Itemname Employee Quantity
pencil samlopez 15

View 1 Replies View Related

How To Get Top Three Salary Getters From Table Employee

Mar 1, 2007

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,1500then my query return me 1500,1300,1200 whereas i want to 1500,1300,1300,1200
how can i do it
please help
thanks

View 1 Replies View Related