Google-Map/Assignment 8.sql at main · jameslivingstone/Google-Map · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112

EXEC SP_changedbowner [DESKTOP-FGQ1OBR\fired]
CREATE FUNCTION uf_LNFN (@FN nvarchar(50), @LN nvarchar(50)) RETURNS nvarchar(102)
BEGIN
RETURN RTRIM(@LN) + ', ' + @FN
END

SELECT dbo.uf_LNFN('John','Smith')


SELECT dbo.uf_LNFN('John', 'Smith') AS EmployeeName, HRD.Name AS Department, JobTitle
FROM HumanResources.Employee AS HRE
	JOIN HumanResources.EmployeeDepartmentHistory AS HREDH ON HRE.BusinessEntityID = HREDH.BusinessEntityID
	JOIN HumanResources.Department AS HRD ON HREDH.DepartmentID = HRD.DepartmentID;

SELECT *
FROM Person.Address;

SELECT *
FROM Person.AddressType;

SELECT *
FROM Person.Person;

SELECT *
FROM HumanResources.Employee;

SELECT *
FROM HumanResources.EmployeeDepartmentHistory;

SELECT *
FROM HumanResources.Department;

SELECT dbo.uf_LNFN(LastName, FirstName) AS EmployeeName, HRD.Name AS Department, JobTitle
FROM HumanResources.Employee AS HRE
	JOIN HumanResources.EmployeeDepartmentHistory AS HREDH ON HRE.BusinessEntityID = HREDH.BusinessEntityID
	JOIN Person.Person AS PP ON HREDH.BusinessEntityID = PP.BusinessEntityID
	JOIN HumanResources.Department AS HRD ON HREDH.DepartmentID = HRD.DepartmentID
WHERE EndDate IS NULL;

SELECT dbo.uf_LNFN(LastName, FirstName) AS EmployeeName, HRD.Name AS Department, JobTitle, City, PostalCode, SpatialLocation
FROM HumanResources.Employee AS HRE
	JOIN HumanResources.EmployeeDepartmentHistory AS HREDH ON HRE.BusinessEntityID = HREDH.BusinessEntityID
	JOIN Person.Person AS PP ON HREDH.BusinessEntityID = PP.BusinessEntityID
	JOIN HumanResources.Department AS HRD ON HREDH.DepartmentID = HRD.DepartmentID
	JOIN Person.BusinessEntityAddress AS PBEA ON PP.BusinessEntityID = PBEA.BusinessEntityID
	JOIN Person.AddressType AS PAT ON PBEA.AddressTypeID = PAT.AddressTypeID
	JOIN Person.Address AS PA ON PBEA.AddressID = PA.AddressID
WHERE EndDate IS NULL AND
	 PAT.Name = 'Home';
Order By EmployeeName;

SELECT dbo.uf_LNFN(LastName, FirstName) AS EmployeeName, HRD.Name AS Department, JobTitle, City, PostalCode, SpatialLocation
FROM HumanResources.Employee AS HRE
	JOIN HumanResources.EmployeeDepartmentHistory AS HREDH ON HRE.BusinessEntityID = HREDH.BusinessEntityID
	JOIN Person.Person AS PP ON HREDH.BusinessEntityID = PP.BusinessEntityID
	JOIN HumanResources.Department AS HRD ON HREDH.DepartmentID = HRD.DepartmentID
	JOIN Person.BusinessEntityAddress AS PBEA ON PP.BusinessEntityID = PBEA.BusinessEntityID
	JOIN Person.AddressType AS PAT ON PBEA.AddressTypeID = PAT.AddressTypeID
	JOIN Person.Address AS PA ON PBEA.AddressID = PA.AddressID
WHERE EndDate IS NULL AND
	 PAT.AddressTypeID = 2
	 Order By EmployeeName;

SELECT dbo.uf_LNFN(LastName, FirstName) AS EmployeeName, HRD.Name AS Department, JobTitle, City, PostalCode, SpatialLocation
FROM Person.Person AS PP
	JOIN HumanResources.EmployeeDepartmentHistory AS HREDH ON PP.BusinessEntityID = HREDH.BusinessEntityID
	JOIN HumanResources.Employee AS HRE ON HREDH.BusinessEntityID = HRE.BusinessEntityID
	JOIN HumanResources.Department AS HRD ON HREDH.DepartmentID = HRD.DepartmentID
	JOIN Person.BusinessEntityAddress AS PBEA ON PP.BusinessEntityID = PBEA.BusinessEntityID
	JOIN Person.AddressType AS PAT ON PBEA.AddressTypeID = PAT.AddressTypeID
	JOIN Person.Address AS PA ON PBEA.AddressID = PA.AddressID
WHERE EndDate IS NULL AND
	 PAT.Name = 'Home';

SELECT dbo.uf_LNFN(LastName, FirstName) AS EmployeeName, HRD.Name AS Department, JobTitle, City, PostalCode, SpatialLocation, SpatialLocation.Lat AS Latitude, SpatialLocation.Long AS Longitude
FROM HumanResources.Employee AS HRE
	JOIN HumanResources.EmployeeDepartmentHistory AS HREDH ON HRE.BusinessEntityID = HREDH.BusinessEntityID
	JOIN Person.Person AS PP ON HREDH.BusinessEntityID = PP.BusinessEntityID
	JOIN HumanResources.Department AS HRD ON HREDH.DepartmentID = HRD.DepartmentID
	JOIN Person.BusinessEntityAddress AS PBEA ON PP.BusinessEntityID = PBEA.BusinessEntityID
	JOIN Person.AddressType AS PAT ON PBEA.AddressTypeID = PAT.AddressTypeID
	JOIN Person.Address AS PA ON PBEA.AddressID = PA.AddressID
WHERE EndDate IS NULL AND
	 PAT.Name = 'Home';

SELECT dbo.uf_LNFN(LastName, FirstName) AS EmployeeName, HRD.Name AS Department, JobTitle, City, PostalCode, SpatialLocation, SpatialLocation.Lat AS Latitude, SpatialLocation.Long AS Longitude
INTO #T1
FROM HumanResources.Employee AS HRE
	JOIN HumanResources.EmployeeDepartmentHistory AS HREDH ON HRE.BusinessEntityID = HREDH.BusinessEntityID
	JOIN Person.Person AS PP ON HREDH.BusinessEntityID = PP.BusinessEntityID
	JOIN HumanResources.Department AS HRD ON HREDH.DepartmentID = HRD.DepartmentID
	JOIN Person.BusinessEntityAddress AS PBEA ON PP.BusinessEntityID = PBEA.BusinessEntityID
	JOIN Person.AddressType AS PAT ON PBEA.AddressTypeID = PAT.AddressTypeID
	JOIN Person.Address AS PA ON PBEA.AddressID = PA.AddressID
WHERE EndDate IS NULL AND
	 PAT.Name = 'Home';

ALTER TABLE #T1
	ADD Distance float

DECLARE @g geography
SET @g = geography::STGeomFromText('POINT(-122.136626 47.642275)', 4326)
UPDATE #T1
SET Distance = SpatialLocation.STDistance(@g)/1609.344

SELECT EmployeeName, Department, City, Latitude, Longitude, Distance
FROM #T1