Mega Code Archive
Get the 3 employees with the most RegionPlace
<![CDATA[
2>
3>
4> CREATE TABLE EmployeeRegion
5> (EmployeeID int NOT NULL,
6> RegionID nvarchar (20) NOT NULL
7> )
8> GO
1>
2> Insert Into EmployeeRegion Values (1,'06897')
3> Insert Into EmployeeRegion Values (1,'19713')
4> Insert Into EmployeeRegion Values (9,'48084')
5> Insert Into EmployeeRegion Values (9,'48304')
6> Insert Into EmployeeRegion Values (9,'55113')
7> Insert Into EmployeeRegion Values (9,'55439')
8> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE TABLE Employees (
4> EmployeeID int NOT NULL ,
5> LastName nvarchar (20) NOT NULL ,
6> FirstName nvarchar (10) NOT NULL ,
7> Title nvarchar (30) NULL ,
8> TitleOfCourtesy nvarchar (25) NULL ,
9> BirthDate datetime NULL ,
10> HireDate datetime NULL ,
11> Address nvarchar (60) NULL ,
12> City nvarchar (15) NULL ,
13> Region nvarchar (15) NULL ,
14> PostalCode nvarchar (10) NULL ,
15> Country nvarchar (15) NULL ,
16> HomePhone nvarchar (24) NULL ,
17> Extension nvarchar (4) NULL ,
18> Photo image NULL ,
19> Notes ntext NULL ,
20> ReportsTo int NULL ,
21> PhotoPath nvarchar (255) NULL
22>
23> )
24> GO
1>
2>
3> CREATE FUNCTION dbo.udf_EmpTerritoryCOUNT (
4> @EmployeeID int
5> ) RETURNS INT
6> AS BEGIN
7> DECLARE @RegionPlace int
8> SELECT @RegionPlace = count(*)
9> FROM EmployeeRegion
10> WHERE EmployeeID = @EmployeeID
11>
12> RETURN @RegionPlace
13> END
14> GO
1>
2>
3>
4> SELECT TOP 3 LastName, FirstName
5> , dbo.udf_EmpTerritoryCOUNT(EmployeeID) as RegionPlace
6> FROM Employees
7> WHERE dbo.udf_EmpTerritoryCOUNT(EmployeeID) > 3
8> ORDER BY dbo.udf_EmpTerritoryCOUNT(EmployeeID) desc
9> GO
LastName FirstName RegionPlace
-------------------- ---------- -----------
(0 rows affected)
1>
2>
3> drop FUNCTION dbo.udf_EmpTerritoryCOUNT;
4> GO
1>
2>
3> drop table EmployeeRegion;
4> GO
1>
2> drop table Employees;
3> GO
]]>