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?
I run into a problem when asking to show a query of employee vacation days.
table 1: column1 Â is dates e.g. 2015-01-01 2015-01-02 2015-01-03Â . . . 2015-12-31
table2: employeeID vacation_date Tom 2015-01-03 Tom 2015-01-04 David 2015-01-04 John 2015-01-08 Mary 2015-01-012
My query output need to be:
2015-01-01 2015-01-02 2015-1-03 Tom 2015-01-04 Tom 2015-01-04 David 2015-01-05 2015-01-06 2015-01-07 2015-01-08 John 2015-01-09 2015-01-10 2015-01-11 2015-11-12 Mary
... etc... all the way to 2015-12-31
when i use left outer join, i only record one employee per date.
For example, the table below, has a foreign key (ManagerId) that points to EmployeeId (primary key) of the same table. -------Employees table-------- EmployeeID . . . . . . . . . . int Name . . . . . . . . . . . nvarchar(50) ManagerID . . . . . . . . . . . int
If someone gave you an ID of a manager, and asked you to get him all employee names who directly or indirectly report to this manager. How can that be achieved?
I thought I'm pretty good with sql. Was presented with a littlechallenge the other day, a colleague asked me to create a parent tochild relationship for a table without changing db schema. The tablelooks like thispkID parentID altPKfield1 null abc2 1 defIt's obvious parent to child relationship via pkID and parentID columnswould do a perfect job, however, he specifically asked not to use pkIDbut altPKfield instead.My first thought was to create a sort of 'ghost column' for 'alt parentfield', then, it goes like using a UDF to get pkID value, use temptable or table variable, it worked but took quite a bit of time.The next day, the colleague told me our boss has another solution, thatis, he joins this same table twice to geneate a fourth column ofanother pkID, and it works perfect and it's very elegant compared to myapproach.Question here is, would you know/use his approach without learning thistechque? Second question is, would there be any downside on usingthis alternative approach of creating a parent to child relationshipfor the same table not using PK column?Thanks.
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
I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.
Result must be:
EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to
I have a table called ADSCHL which contains the school_code as Primary key and other two table as
RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. I have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables.
I tried using Merge Update function not sure if this is going to work.
Update dbo.ADSCHL SET dbo.ADSCHL.SCHOOL_CODE = FD.SCHOOL_Code FROM dbo.ADSCHL AD INNER JOIN TESTCEP FD ON AD.SCHOOL_NAME = FD.School_Name
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)
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.
I have one table that has unique id's associated with each row of information. I want to delete rows of information in one table that have a unique ID that references information in another table.
Here is a basic breakdown of what I am trying to do:
Table1 (the table where the rows need to be deleted from) Column_x (Holds the id that is unique to the various rows of data - User ID)
Table2 (Holds the user information & has the associated ID) Column_z (holds the User ID)
I tried this on a test set of tables and could not get it to work. What I am trying to do is skip all rows of Table1 that have ID's present in Table2, and delete the rows of ID's that are not present in Table2.
Code:
SELECT Column_z FROM dbo.Table2 DELETE FROM dbo.Table1 WHERE Column_z <> Column_x
This did not seem to do what I needed, it did not delete any rows at all.
I wanted it to delete all rows in Table1 that did not have a reference to a user ID that matched any ID's in Column_z of Table2
Then I tried another scenerio that I also needed to do:
Code:
SELECT Column_z, Column_a FROM dbo.Table2 DELETE FROM dbo.Table1 WHERE Column_z = Column_x AND Column_a='0'
'0' being the user id is inactive so I wanted to delete rows in Table1 and remove all references to users that were in an inactive status in Table2.
Neither one of the Queries wanted to work for me in the Query Analyzer when I ran them. It just said (0) rows affected.
CREATE TABLE Folder ( iD int NOT NULL IDENTITY (1, 1) PRIMARY KEY, folderName varchar(50) NOT NULL, parentFolderID int NULL FOREIGN KEY REFERENCES Folder (iD) ) GO
if I add an ON DELETE CASCADE to the foreign key, then i get an error... which is annoying. If a folder is deleted, then all its sub-folders should also be automatically deleted.
The error is: 'Introducing FOREIGN KEY constraint 'FK__Folder__parentFo__7D78A4E7' on table 'Folder' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.'
Im trying to create an update statement which references two tables (join) and has a CASE clause attached. Not sure where im going wrong...
Using T-sql!!!
update import set import.gone = from import inner join stat ON stat.id = import.id CASE WHEN stat.A = import.field2 THEN import.gone = sec.A WHEN stat.B = import.field2 THEN import.gone = sec.B WHEN stat.C = import.field2 THEN import.gone = sec.C WHEN stat.D = import.field2 THEN import.gone = sec.D WHEN stat.E = import.field2 THEN import.gone = sec.E WHEN stat.F = import.field2 THEN import.gone = sec.F ELSE import.gone = null END
With reason of the enormous effort carried out to visit the Argentineancity of Córdoba, to participate in the Meeting of MERCOSUR, in theclosing of the Summit of the Towns in the historical University ofCórdoba and in the visit to Altagracia, the city where Che lived inits childhood and together to this to attend immediately thecommemoration of the 53 anniversary from the assault to the barracksMoncada and Carlos Manuel of Grasses, July 26, 1953 in the counties ofGranma and Holguín, days and nights of continuous work withoutsleeping hardly gave place that my health that has resisted all thetests, underwent to an extreme stress and it was smashed.This provoked me an intestinal sharp crisis with sustained spill ofblood that forced me to face a surgical complicated operation. All thedetails of this accident of health consist in the x-rays, endoscopiesand filmed materials. The operation forces me to remain several weeksof rest, far from my responsibilities and positions.As our country it is threatened in circumstances like this by theGovernment from the United States, I have made the following decision:1) I delegate with provisional character my functions as FirstSecretary of the Central Committee of Communist Party of Cuba in theSecond Secretary, partner Raúl Castro Ruz.2) I delegate with provisional character my functions as Major in Bossof the Revolutionary heroic Armed Forces in the mentioned partner,General of Army Raúl Castro Ruz.3) I delegate with provisional character my functions as President ofthe Council of State and of the Government of the Republic of Cuba inthe First Vice-president, partner Raúl Castro Ruz.4) I delegate with provisional character my functions as maininstigator of the National and International Program of Public Healthin the Member of the Political Desk and Secretary of Public Health,partner José Ramón Balaguer Goatherd.5) I delegate with provisional character my functions as maininstigator of the National and International Program of Education inthe partners José Ramón Machado Ventura and Esteban Lazo Hernández,Members of the Political Desk.6) I delegate with provisional character my functions as maininstigator of the National Program of the Energy Revolution in Cuba andof collaboration with other countries in this environment in thepartner Carlos Lage Dávila, Member of the Political Desk and Secretaryof the Executive Committee of the Council of Secretaries.The corresponding funds for these three programs, Health, Education andEnergy, they will continue being negotiated and prioritized, like Ihave come making it personally, for the partners Carlos Lage Dávila,Secretary of the Executive Committee of the Council of Secretaries,Francisco Soberón Valdés, Secretary President of the Central Bank ofCuba, and Felipe Pérez Roque, Secretary of External Relationships whothey accompanied me in these managements and they will constitute acommission for that objective.Our Communist glorious Party, supported by the organizations of massesand the whole town, has the mission of assuming the task commended inthis it Proclaims.The meeting Summit of the Movement of Not Aligned Countries, to becarried out among the days 11 and 16 of September, will receive thebiggest attention in the State and the Cuban Nation to take place withthe maximum of brightness in the agreed date.The 80 anniversary of my birthday that so generously thousands ofpersonalities agreed to celebrate the next one August 13, I request toall to postpone it for December 2 of the present year, on the 50anniversary of the Landing of Granma.I request to the Central Committee of Party and the National Assemblyof the Popular Power the firmest support to this it Proclaims.I don't house the smallest doubt that our town and our Revolution willfight until the last drop of blood to defend these and other ideas andmeasures that are necessary to safeguard this historical process.The imperialism will never be able to squash Cuba.The Battle of Ideas will continue ahead.Alive the Homeland!Alive the Revolution!Alive the Socialism!Until Victoria Always!Fidel Castro RuzMajor in Boss First Secretary of Party and President of the StateAdviceAnd of Secretaries of the Republic of Cuba. July 31, 2006 6:22 p.m
I have a table that holds a ParentID and the RecordID. There is a column called IsEnabled which is a bit field indicating if a folder can be displayed or not. 0 = NO, 1 = YESThe table is for a directory structure which is virtual and displays folders on a web page.Root----- 1---------- 1-1---------- 1- 2----------------- 1-2-1----------------- 1-2-2----------------------- 1-2-2-1----------------- 1-2-3----------------- 1-2-4---------- 1- 3---------- 1- 4I need a query that will not select any children that are under a Parent that is disabled. So if ' 1- 2 ' is disabled then:---------- 1- 2----------------- 1-2-1----------------- 1-2-2----------------------- 1-2-2-1----------------- 1-2-3----------------- 1-2-4SHOULD NOT SHOW.Can anyone give me a query that will overcome this problem i have.-J
I've recently finished an application for a small company with perhaps two hundred employees. Each employee was set up in a Users table in the database, against which application logins were processed.
For just about every other table in the database, other than pure lookup tables, we created columns to indicate the user who created the entry, and the user who last modified the entry. This was done using FK references back to the Users table. Each table contains two references back to the Users table, and there are over 150 tables now that follow this scheme. At first I was not concerned, other than the fact that it makes a visual picture of the data model look very confusing (almost every table has a pair of links back to the Users table), until I encountered an issue where I could no longer delete from the Users table. Upon surpassing 253 FK references to Users, I can no longer delete users, as the Query Optimizer can't complete the query.
Now, all of that so far is really not a big deal. Deleting users was never my intent anyway. The only real question I have is whether this is the standard way of maintaining history for table records. Have others used this method? Is there a better way?
ID INT PK Name VARCHAR RefID FK ID Name RefID 1 Test1 <NULL> 2 Test1a 1 3 Test1b 1 4 Test1b1 3 5 Test1b1a 4
CREATE TRIGGER Update_ID ON TableA INSTEAD OF UPDATE AS IF UPDATE(ID) BEGIN DECLARE @old_ID INT DECLARE @new_ID INT
SELECT @old_ID = ID FROM DELETED SELECT @new_ID = ID FROM INSERTED
UPDATE TableA SET ID = @new_ID WHERE ID = @old_ID
UPDATE TableA SET RefID = @new_ID WHERE RefID = @old_ID END
SPROC: UPDATE TableA SET ID=6 WHERE ID=2
RefID is the FK to ID ID is also the PK to another table and has the relationship use cascading Delete and Update.
Problem: "The UPDATE statement conflicted with the SAME TABLE REFERENCE constraint" This is referring to the first UPDATE of the Trigger. It was my understanding that Instead Of checks the constraints after it is all done yet it errs with the first Trigger UPDATE. I was expecting to overcome the self-reference constraint issue by using Instead Of and with the first Trigger UPDATE, change the ID(PK) and then change the RefID(FK) with the second Trigger UPDATE. Once that was done, it should have not had any constraint problems. Thoughts?
We are in the conversion process of making the database ints.This is a change from a guid PK to an integer based PK that uses an int Identity. The program still uses the guid, and we are trying to map that guid to the databases int.We insert using TVPs passed from code. Since the identity is being set upon insert I have three things to accomplish:
1) Insert all the data into the dbo table 2) Update the parent Id in the table 3) Pass the SetsId guid, Sets_Id int, ParentSets_Id int back to the program
This is a high transaction table that will have a lot of records (millions).
--Sample table creation. There is a FK between Sets_Id to ParentSets_Id, Clustered PK on the Sets_Id IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JSets]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[JSets]( [SetsID] [uniqueidentifier] NOT NULL, [Sets_Id] bigint Identity (1,1) NOT NULL,
Hi everybody, I know that it is possible for one column to reference two different parent tables by defining two foreign keys on a same column, I have done it and SQL Server does give any error. But I want to know that is it advisable to define multiple foreign key on a column referncing two differnt parent table's primary key in differnt case... ? Means If Case 1 then It should reference to column1 or table1 otherwise it should reference to table2? How is it practicle...?
An example of what I am talking about is the employee table in the Adventureworks database. This has employeeID and then ManagerID, ManagerID just being the EmployeeID of the person whom the original reports to.
I know the queries for querying this type of data and even making recursive common table expressions. What I cannot seem to find is how one goes about BUILDING said table. I see all sorts of examples where people are just doing INSERT table VALUES () manually to load the table. The problem is, I need to create a table that has potentially thousands of records.
It will essentially be a dimensional map. Don't even get me started as to they why, I will just suffice to say that is what the client and project want . I have a process that will do this now, but it is not very dynamic and very hard coded. To me, there seems like there should be some sort of standardized methodology for handling this.
I have the following problem running an sp with a table variable (sql server 2000) - the error which occurs at the end of the query is: "must declare the variable @THeader" . @THeader is the name of the variable table and the error occurs with such references as @THeader.ApplyAmt, @THeader.TransactionHeaderID, etc.
declare @THeader TABLE ( TransactionHeaderID [int] NOT NULL , PatientID [int] NOT NULL , TransactionAllocationAmount [money] NOT NULL , ApplyAmt [money] NULL ) - create table variable
insert into @THeader select TransactionHeaderID,PatientID,TransactionAllocationAmount,ApplyAmt from mtblTransactionHeader where PatientID = 9 - fill the table variable
UPDATE @THeader set TransactionAllocationAmount = (SELECT isnull(Sum(mtblTransactionAllocation.Amount),0) FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID or mtblTransactionAllocation.CRID = TransactionHeaderID) from @THeader, mtblTransactionAllocation - do the updates on the table variable
Update @THeader set ApplyAmt = (SELECT mtblTransactionAllocation.Amount FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID and mtblTransactionAllocation.CRID = 187 and PatientID = 9) from @THeader, mtblTransactionAllocation - do the updates on the table variable
- below is where the problems occur. It occurs with statements referencing columns in the table variable, i.e. @THeader.ApplyAmt
UPDATE mtblTransactionHeader SET mtblTransactionHeader.TransactionAllocationAmount = @THeader.TransactionAllocationAmount, mtblTransactionHeader.ApplyAmt = @THeader.ApplyAmt FROM @THeader, mtblTransactionHeader WHERE @THeader.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID - put the values back into original table
I'm creating a new Integration Services Project that copies data out of a SQL 7 server, transforms it, and places the data on a SQL 2005 (SP 2) Server. When defining a lookup transformation, if I specify an OLE DB Connection to my server running SQL 7 as the reference table, as soon as I click on the Colums tab, Visual Studio closes / crashes and dumps me to windows. I don't get an error message. If however I specify a connection to a server running SQL 8, or SQL 2005, no problems.
Is this supposed to happen?
My workstation is running Windows XP Pro SP2, Visual Studio 2005 Pro.
Microsoft SQL Server Integration Services Designer Version 9.00.1399.00
The server that doesn't work for a reference table is running Windows 2000 Server SP4 SQL 7.00.623
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);
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.
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.
