[SQL Server] Want to categorize the salaries of employee. |
| Want to categorize the salaries of employee. Posted: 05 Apr 2013 07:36 AM PDT Hi Team,I've Table People Where there are n no of employees involved.The salaries of the employee are present in Column 'Salary'So now, i wanted to count the no of Employees in the range of Salaries as below1) >10000 & <300002) >30000 & <800003) >800000Im trying the above logic as per the below code, can any one please help me understand this, why IM getting '0' for the counts even for no error in the query?'Select Count(p2.SALARY)As Range1, Count(p3.SALARY)As Range2,Count(p4.SALARY)As Range3From PEOPLE p1JOIN PEOPLE p2 ON ((p1.pID = p2.pID) AND (p1.SALARY BETWEEN 10000 AND 20000)) JOIN PEOPLE p3ON ((p1.pID = p3.pID) AND (p1.SALARY BETWEEN 20000 AND 80000))JOIN PEOPLE p4ON ((p1.pID = p4.pID) AND (p1.SALARY > 80000))' |
| Posted: 04 Apr 2013 11:39 PM PDT I created a table using the statement below in one of my databases (database1). The table is to be updated with information from a table in another database (database2) at a regular interval using an Insert Into statement. When creating the table, in addition to the fields I specified, it created a foreign key called PK_Test_ELE_761abed01e1455b5. I got my intial set of data into the table without a problem using the insert statement below without the where clause, but after that, every time I try to update it using the where clause I would get the following error message: "Violation of PRIMARY KEY constraint 'PK__ELEVATIONS__7B5130AA'. Cannot insert duplicate key in object 'Test_ELEVATIONS'." To get around it for testing in Beta I just deleted the key from the table. Before moving to prod, I wanted to get some additional information. Why was the key created and whats the best way to handle this? Thank in advance for any input.CREATE TABLE Test_ELEVATIONS(ProjectID nvarchar(30) not null Primary Key,ParcelID nvarchar(24) null,Ukey nvarchar(15) null,Location nvarchar(75) not null,AppStatus nvarchar(30) null,AppStatusDate datetime null,Type nvarchar(30) null,Category nvarchar(30) null,Community_Number nvarchar(30) null,Panel nvarchar(30) null,Datum nvarchar(30) null,Base_Flood_Elevation nvarchar(30) null,Lowest_Adjacent_Grade nvarchar(30) null,Lowest_Machinery_Elevation nvarchar(30) null,Date_of_Firm nvarchar(30) null,Firm_Zone nvarchar(30) null,License_Number nvarchar(30) null,Floor_Elevation nvarchar(30) null)Use Database2Insert Into database1.Test_ELEVATIONS(ProjectID,ParcelID,UKEY,Location,AppStatus,AppStatusDate,Type,Category,Community_Number ,Panel ,Datum,Base_Flood_Elevation,Lowest_Adjacent_Grade,Lowest_Machinery_Elevation,Date_of_Firm,Firm_Zone,License_Number,Floor_Elevation)Select .......From ....Where ProjectID NOT IN (SELECT PROJECTID FROM database1.Test_ELEVATIONS) |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
