troubleshooting Question

sql server 2008 query

Avatar of maqskywalker
maqskywalker asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
4 Comments1 Solution424 ViewsLast Modified:
Hi experts,

I'm using SQL Server 2008.

I need help with a query

I have a table called TestTable1 that looks like this:

TestTable1
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.

logic diagram

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.

desired query result
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros