1: SELECT a.NTName, a.Employee, g.FirstName, h.LastName,
2: b.Title, e.Department, a.[Office Phone], c.Email, f.Office, a.Manager
3: ,(select top 1 f.Email from UserProfile_Full f
4: where f.NTName = a.Manager
5: group by f.NTName, f.Email) as 'Manager Email'
6: FROM (select
7: a.NTName,
8: a.RecordID,
9: a.Manager,
10: a.PreferredName as Employee,
11: b.PropertyVal as [Office Phone]
12: from UserProfile_Full a, UserProfileValue b
13: where b.PropertyID=8 and
14: a.RecordID=b.RecordID) a -- WorkPhone
15: left outer join
16: (select
17: a.RecordID,
18: a.PreferredName as Employee,
19: b.PropertyVal as Title
20: from
21: UserProfile_Full a, UserProfileValue b
22: where
23: b.PropertyID=13 and
24: a.RecordID=b.RecordID) b -- Title
25: on a.RecordID=b.RecordID
26: left outer join
27: (select
28: a.RecordID,
29: b.PropertyVal as Email
30: from
31: UserProfile_Full a, UserProfileValue b
32: where
33: b.PropertyID=9 and
34: a.RecordID=b.RecordID) c -- WorkEmail
35: on a.RecordID=c.RecordID
36: left outer join
37: (select
38: a.RecordID,
39: b.PropertyVal as [Cell Phone]
40: from
41: UserProfile_Full a, UserProfileValue b
42: where
43: b.PropertyID=19 and
44: a.RecordID=b.RecordID) d --CellPhone
45: on a.RecordID=d.RecordID
46: left outer join
47: (select
48: a.RecordID,
49: b.PropertyVal as Department
50: from
51: UserProfile_Full a, UserProfileValue b
52: where
53: b.PropertyID=14 and
54: a.RecordID=b.RecordID) e --Department
55: on a.RecordID=e.RecordID
56: left outer join
57: (select
58: a.RecordID,
59: b.PropertyVal as Office
60: from
61: UserProfile_Full a, UserProfileValue b
62: where
63: b.PropertyID=11 and
64: a.RecordID=b.RecordID) f --Office
65: on a.RecordID=f.RecordID
66: left outer join
67: (select
68: a.RecordID,
69: b.PropertyVal as FirstName
70: from
71: UserProfile_Full a, UserProfileValue b
72: where
73: b.PropertyID=4 and
74: a.RecordID=b.RecordID) g --FirstName
75: on a.RecordID=g.RecordID
76: left outer join
77: (select
78: a.RecordID,
79: b.PropertyVal as LastName
80: from
81: UserProfile_Full a, UserProfileValue b
82: where
83: b.PropertyID=5 and
84: a.RecordID=b.RecordID) h --LastName
85: on a.RecordID=h.RecordID
86: order by a.NTName
Comentarios