sql server 2008 query

Hi experts,

I'm using SQL Server 2008.

I need help with a query

I have a table called TestTable1 that looks like this:


This is the script to create the table:

CREATE TABLE [dbo].[TestTable1](
	[FirstName] [varchar](20) NULL,
	[LastName] [varchar](30) NULL,
	[Address] [varchar](30) NULL,
	[City] [varchar](30) NULL,
	[State] [varchar](2) NULL,
	[PositionNumber] [varchar](20) NULL,
	[PositionDescription] [char](50) NULL,
	[WorkLocationID] [varchar](6) NULL,
	[WorkLocationDescription] [varchar](30) NULL,
	[ReportsToID] [int] NULL,
	[ReportsToIDType] [varchar](10) NULL,
	[Team1ID] [int] NULL,
	[Team2ID] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[TestTable1] ([FirstName], [LastName], [Address], [City], [State], [PositionNumber], [PositionDescription], [WorkLocationID], [WorkLocationDescription], [ReportsToID], [ReportsToIDType], [Team1ID], [Team2ID]) VALUES (N'Ted', N'Simpson', N'345 W. Main', N'Springville', N'MO', N'101', N'Clerk III', N'161', N'Finance Dept', 0, N'', 17487, 106903)
INSERT [dbo].[TestTable1] ([FirstName], [LastName], [Address], [City], [State], [PositionNumber], [PositionDescription], [WorkLocationID], [WorkLocationDescription], [ReportsToID], [ReportsToIDType], [Team1ID], [Team2ID]) VALUES (N'John', N'Smith', N'765 W. 3rd Street', N'Springville', N'MO', N'121', N'Clerk II', N'155', N'HR Dept', 16694, N'T1', 17851, 95380)
INSERT [dbo].[TestTable1] ([FirstName], [LastName], [Address], [City], [State], [PositionNumber], [PositionDescription], [WorkLocationID], [WorkLocationDescription], [ReportsToID], [ReportsToIDType], [Team1ID], [Team2ID]) VALUES (N'Jane', N'Doe', N'2566 W. Maple', N'Springville', N'MO', N'300', N'Manager', N'133', N'Accounting Dept', NULL, NULL, 16694, 2106)
INSERT [dbo].[TestTable1] ([FirstName], [LastName], [Address], [City], [State], [PositionNumber], [PositionDescription], [WorkLocationID], [WorkLocationDescription], [ReportsToID], [ReportsToIDType], [Team1ID], [Team2ID]) VALUES (N'Barney', N'Swanson', N'6467 E. 2nd Street', N'Springville', N'MO', N'301', N'Manager', N'133', N'Accounting Dept', 16706, N'T1', 18285, 106335)
INSERT [dbo].[TestTable1] ([FirstName], [LastName], [Address], [City], [State], [PositionNumber], [PositionDescription], [WorkLocationID], [WorkLocationDescription], [ReportsToID], [ReportsToIDType], [Team1ID], [Team2ID]) VALUES (N'Allen', N'Williams', N'987 W. Sunset', N'Springville', N'MO', N'131', N'Clerk I', N'139', N'Business Dept', 17487, N'T1', 16914, 5218)
INSERT [dbo].[TestTable1] ([FirstName], [LastName], [Address], [City], [State], [PositionNumber], [PositionDescription], [WorkLocationID], [WorkLocationDescription], [ReportsToID], [ReportsToIDType], [Team1ID], [Team2ID]) VALUES (N'William', N'Hurts', N'6732 E. Cedar', N'Springville', N'MO', N'500', N'CEO', N'139', N'Business Dept', NULL, NULL, 16706, 7281)
INSERT [dbo].[TestTable1] ([FirstName], [LastName], [Address], [City], [State], [PositionNumber], [PositionDescription], [WorkLocationID], [WorkLocationDescription], [ReportsToID], [ReportsToIDType], [Team1ID], [Team2ID]) VALUES (N'Eileen', N'Dover', N'1873 W. Beverly', N'Springville', N'MO', N'135', N'Clerk III', N'133', N'Accounting Dept', 106335, N'T2', 17854, 2605)
INSERT [dbo].[TestTable1] ([FirstName], [LastName], [Address], [City], [State], [PositionNumber], [PositionDescription], [WorkLocationID], [WorkLocationDescription], [ReportsToID], [ReportsToIDType], [Team1ID], [Team2ID]) VALUES (N'Heidi', N'Johnson', N'837 W. Cleo', N'Springville', N'MO', N'129', N'Secretary', N'154', N'HR Dept', 16694, N'T1', 17855, 3651)

I want to create a column called BossName.

The logic for the value in this column is as follows.

There are 2 cases I care about.

When the column called ReportsToIDType =  T1 and when  ReportsToIDType = T2
Disregard any other values in the ReportsToIDType column.


Whenever ReportsToIDType = T1 then look at the ReportsToID column for that person and use that value to lookup that employee from Team1ID column.

Whenever ReportsToIDType = T2 then look at the ReportsToID column for that person and use that value to lookup that employee from Team2ID column.

Case 1
So for example  If I look at John Smith on the diagram below.  
Since ReportsToIDType = T1 for him then I will use the value of the ReportsToID column which in this case is 16694 and look up this id from the Team1ID column. So the person with a Team1ID column value of 16694 is Jane Doe as shown in blue in the diagram below. So Jane Doe is John Smith’s boss.

Case 2
So for example  If I look at Eileen Dover on the diagram below.  
Since ReportsToIDType = T2 for her then I will use the value of the ReportsToID column which in this case is 106335 and look up this id from the Team2ID column. So the person with a Team2ID column value of 106335 is Barney Swanson as shown in pink in the diagram below.
So Barney Swanson is Eileen Dover’s boss.



So my desired result set of my query should look something like this picture I drew in excel.
Notice the last column called BossName that is the one I want to add.


Can anyone help with this query?

I started to write something like shown for the column I'm adding but not sure the syntax in query. Below is a mixture of English and sql syntax.


IF  [ReportsToIDType] = 'T1'  THEN
               [ReportsTo] = [FirstName]+ ' ' + [LastName]
       WHERE      [ReportsToID] of the employee = [Team1ID] pertaining to the boss

ELSE IF  [ReportsToIDType] = 'T2'  THEN
               [ReportsTo] = [FirstName]+ ' ' + [LastName]
       WHERE    [ReportsToID] of their employee = [Team2ID] pertaining to the boss
TestTable1.jpg