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
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 :(
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 ?
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
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.
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
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] .....
I've got a big problem that I'm trying to figure out:
I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like:
[AddressID] [int]
[LocationID] [int]
[Type] [nvarchar](10)
[Address] [varchar](50)
[City] [varchar](50)
[State] [char](2)
[Zip] [varchar](5)
[AddDate] [datetime]
[EditDate] [datetime]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.
So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
Thank you in advance for any and all replies...
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?
I am uploading input sample data and desired output data, can get the desire output.
View 4 Replies View RelatedI 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?
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 RelatedI 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 .
I need some help devising my tables.
I have a company table and a address table. I have created a linked table with two foreign keys from the company table and the address table respectively.
This set up allows me to apply more than one address per company which is fine. My problem is that I need a way to prevent a company from entering exactly the same address twice.
In the address table i have the following fields:
AddressKey - PK
CompanyKey - FK
Address 1 - 3
Town
County
Country
PostCode
In the company table I have the following fields.
CompanyKey - PK
FirstName
Secondname
And finally in the link table I have the following:
CompanyKey - FK
AddressKey - FK.
Now, if i entered the following into the address table Assuming that company id of 1 was already entered into the company table.
AddressKey - 1, CompanyKey = 1, Address1 = 11 Address2= Taylor
I need a way of preventing this from happening.
AddressKey - 2, CompanyKey = 1, Address1 = 11 Address2= Taylor
As can be seen the PK - FK values are unique and are correct for referential integrity, but the actual address is the same.
Any help we be mostly appreciated.
Cheers
Wayne
Dear All,How to store a separated company-calendar for each client-company (about500).Company-calendar: workingdays, non-workingdays, meetings, etc.Thanks,Filips
View 1 Replies View RelatedWe deal with multiple vendors who provide us information via text/xml files. Vendor A may provide financial data, vendor b provides litigation data, vendor c provides ratings data. Our current structure has databases for each vendor with its own company table which basically makes all this data disconnected. Of course each vendor has its own proprietary company id to make records unique.
All of the data is based on companies so the grain of data would be at a company level. I would like to be able to link this information together by creating a dimensional model that has a single company table (DimCompany) and has facts populated based on the type of data we receive. Would this be the right sequence of events?
1. My initial load (historical) would have to look at all these data sources and create one company record in my DimCompany table. This table would then link to all other fact tables to provide a single view of company info. I would imagine this would have to be a fuzzy lookup since one company will be in all sources.
2. On subsequent loads (incremental) I would probably have to do a lookup of companies in the dimension via the proprietary code and add if the company wasn't there.
Any advice on tackling this issue would be greatly appreciated especially if SSIS was used in the process.
I'm developing an application that needs to handle several companies data one at a time.
I have a database called ROOT with the common tables like companies, users, etc. and all the stored procedures that I need.
Then I have a unique database per company containing all their info.
How can I apply the stored procedures that I have on database ROOT to the tables on each company's databases ?
The TSQL USE command do not accept variables.
Thanks for your help,
MOshe
I have a database that allows a client to have multiple addresses, could be a home, business, vacation, etc... I'm trying to deside if I should put the Company Name and Title fileds in the tblClientAddresses table or keep it with the tblClient table. We do mailings to clients and if the mail is going to a home address the company and title should be excluded. If I put them in the tblClients table I really have no way of knowing if we are mailing to a home or a company. If i put the company and title with the address it makes sence that this would solve the issue.
Looking for any thoughts or suggestions?
Thank you,
Are there any forseeable problems with only moving one of my company's departments SQL database server to sp2 and leaving the others on sp1? From an admin tools perspective? Our internet groupd would like to move on to sp3 and the others would like to wait?
thanks,
My Name is Nick Soutter, I am the owner of a small game company, Aepox Games(We're in the middle of a name change from "Lamar Games"),www.lamargames.net.Our first commercial game, Andromeda Online (www.andromedaonline.net) isgoing into beta soon. It runs on an evaluation edition of SQL Server 2000(our intention is, when it launches, we earn the money to buy a copy beforethe evaluation expires).We have been testing Andromeda Online, and found that saves to the databasetake about 10 seconds (we were anticipating less than 1). We felt we needsomebody experienced in optimizing sql databases to help us optimize thedatabase, and get it running in the best method for our particularapplication.Our program accesses the database in Java, and people with understanding inhow to optimize java connections would be a tremendous help.My company is small, and we honestly cant afford much. Everybody on thisproject, from the sound guys to the graphic artist, has worked for 1/10 to1/100 of the value of the job. We're simply a starting company looking fordedicated people who are willing to work more for credit than money.We can offer credit on our website(http://www.andromedaonline.net/credits.html) to anybody who helps us, butlittle more (maybe $100, but we're very over budget, and in desperate needof help). Because of how we intend the game to run (with maybe 100-200concurrent games running online), a 10 second save time is simplyunacceptable.Anybody who would be willing to help us, please send a resume toJoin Bytes!. Experience would be nice, but not a requirement.We're looking for someone who can talk with our programmer about the typesof calls made to our SQL database, and then can log into the DB and optimizeit to run as fast as possible considering our specific needs.Thank you for your time.Nick SoutterAepox (Lamar) Games
View 14 Replies View RelatedI plan to use Visual Web Developer 2005 Express Edition create web application which access SQL database(Express Edition). Is there any license require for this 2 software if I use its in company? Please provide information to me. Thank you!
View 1 Replies View RelatedFor example: company_name: ABC company, inc.
I want to get rid of the comma, replace by a space. How the query should be write?
Thanks
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.
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?
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
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
I must be missing something, and its starting to fusterate me. Bear with me here.
I created a site for a ...client I guess you'd call it, and made this really slick newletter generator thing. The people from the web enter in their info, and if they want, they sign up for a newsletter -- all tied into a db, 1 table, 4 stored procedures, REALLY simple stuff. They insisted I used a certain webhost which, on paper, looks like it will fit the bill. I'm starting to question that.
On top of the newsletter thing, I created an aspnetdb for the "administration" side of it for her to log into and send out the newsletter so total, there's 2 dbs in the app_data folder. Locally, it works GREAT and on my test box (iis6) that is running 2k5 express. The webhost runs sql2k in (what I consider) a bastardized way. Can't use the management studio, can't use anything except a really weak web-based interface which adds to my fusteration.
Anyway, my questions : 1, is there a way to make the mdf files work with sql2k without having to re-do the whole thing and 2, if I have to redo it, does anyone have an example connection string that might help out?
Hi I need for my hosting company to send my db that I have convert from SQL EXR.2005 to 2000 and send them a backup file.
I have back up it (task> backup )and sand it over they cant open (restore) it and I do not understand why
What did I do wrong? (this time:- )
Thanks assaf
I am not seeing the problem with this. I have a button at the bottom of this form that does this...
protected void Button1_Click(object sender, EventArgs e)
{
SqlDataSource1.Insert();
}
And the SqlDataSource code...<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:calldbConnectionString2 %>"
ProviderName="<%$ ConnectionStrings:calldbConnectionString2.ProviderName %>"
InsertCommand="INSERT INTO IDcall(IDcompany, enteredBy, likelihood6mo, liklihood12mo, stamp, objective, dateOfCall, applications, specifics, developmentStage, results, nextStep, salesStrategy, potentialFirstYear, other, valueOfSale, alternateContacts, IDcontact1) VALUES (@company, @entered, @mo6, @mo12, @stamp, @objective, @dateOfCall, @applications, @specifics, @development, @results, @next, @strategy, @potential, @other, @value, @alternate, @IDcontact1)">
<InsertParameters><asp:ControlParameter ControlID="DropDownList_Company" Name="@company"
PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox_signature" Name="@entered"
PropertyName="Text" />
<asp:ControlParameter ControlID="DropDownList_6mo" Name="@mo6"
PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="DropDownList_12mo" Name="@mo12"
PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox_submittedOn" Name="@stamp"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_Purpose" Name="@objective"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_dateOfCall" Name="@dateOfCall"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_applications" Name="@applications"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_specifics" Name="@specifics"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_development" Name="@development"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_results" Name="@results"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_nextStep" Name="@next"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_strategy" Name="@strategy"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_potentialFirstYear" Name="@potential"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_other" Name="@other"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_Value" Name="@value"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_alternateContacts" Name="@alternate"
PropertyName="Text" />
<asp:ControlParameter ControlID="DropDownList_Contact" Name="IDcontact1"
PropertyName="SelectedValue" />
</InsertParameters>
</asp:SqlDataSource>
When I click the button I get Must declare the scalar variable "@company". How is @company not declared?
And here's my stackStack Trace:
[SqlException (0x80131904): Must declare the scalar variable "@company".]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1005
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +149
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +404
System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +447
System.Web.UI.WebControls.SqlDataSource.Insert() +13
Calls_CallNew.Button1_Click(Object sender, EventArgs e) in c:InetpubwwwrootCallsCallNew.aspx.cs:88
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
Can I use database that i develop in SQL Server 2005 in hosting company that has SQL server 2000 installed?
Thanks guys !!
I would like to handover a SQL server 2005 database that I've been using for testing/development to a client for use in a production environment. Besides deleting my test records from the database and making a copy of the MDF file, do I have to handover the datase's LDF file too? I guess I should also compress the database before providing a copy. Please advise of "gotchas" I should be a aware of. Also, I guess the client's IT group would need to run my "create user" script first before mounting the database, since the created user is the owner of the database.
Thanks,
Phillip
I've looked around, but can't find this anywhere. Does anyone know of a way to add a footer to all reports (including new ones being created via report builder)? So that when users create new reports, the global footer is there.
View 4 Replies View RelatedBasically 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] .....