quote:

---

Originally posted by ostinoh

ok let me see if I can provide what is needed. Here is the script broken down with no filters added. The new column name is in bold with the Inner Join.

SELECT r.name as Resource_Name,rw.name as Business_Unit,t.name
FROM Resources r WITH(NOLOCK)
INNER JOIN Workgroupmember wgm WITH(NOLOCK) ON wgm.ResourceID = R.ResourceID and wgm.historical = 0
INNER JOIN Workgroup rw WITH(NOLOCK) ON rw.workgroupid = wgm.workgroupid
INNER JOIN Tasks t WITH(NOLOCK) ON t.taskid = ta.taskid
WHERE r.deleted = 0

When I run the above script I get the following error in SSMS.

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "ta.taskid" could not be bound.

If the above script runs currently without the Task two columns are returned with users name and what department they work in. The new column Task would return if they are missing a PTO or Hoilday entry.

Please let me know what else you might need.

Thank you

---

USE [Changepoint]
GO
/****** Object:  Table [dbo].[Resources]    Script Date: 12/07/2011 09:25:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Resources](
	[ResourceId] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](200) NOT NULL,
	[FirstName] [nvarchar](50) NULL,
	[MiddleName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[Description] [ntext] NULL,
	[UserDefinedResourceId] [nvarchar](255) NULL,
	[Title] [nvarchar](255) NULL,
	[Language] [varchar](3) NULL,
	[BaseCurrency] [varchar](3) NULL,
	[WebPassword] [varchar](255) NULL,
	[MailURL] [nvarchar](255) NULL,
	[Signature] [varchar](50) NULL,
	[CreatedOn] [datetime] NOT NULL,
	[CreatedBy] [uniqueidentifier] NOT NULL,
	[UpdatedOn] [datetime] NULL,
	[UpdatedBy] [uniqueidentifier] NULL,
	[Deleted] [bit] NOT NULL,
	[LastLogin] [datetime] NULL,
	[ResourceType] [varchar](3) NOT NULL CONSTRAINT [DF__Resource__Resour__1DFCE517]  DEFAULT ('E'),
	[Licensed] [bit] NOT NULL,
	[ReportsTo] [uniqueidentifier] NULL,
	[DisableEmail] [bit] NOT NULL CONSTRAINT [DF__Resource__Disabl__1FE52D89]  DEFAULT (0),
	[Location] [nvarchar](255) NULL,
	[IndustryExperience] [nvarchar](255) NULL,
	[EmployeeType] [varchar](3) NULL,
	[License] [int] NOT NULL CONSTRAINT [DF__Resource__Licens__16F4B8DF]  DEFAULT (0),
	[AlternateName] [nvarchar](200) NULL,
	[AllowGaps] [bit] NOT NULL CONSTRAINT [DF__Resource__AllowG__42C9197C]  DEFAULT (1),
	[LCID] [smallint] NOT NULL CONSTRAINT [DF__resource__LCID__4D46A7EF]  DEFAULT (1033),
	[FailedLoginAttempts] [tinyint] NULL,
	[DisableLogin] [bit] NOT NULL CONSTRAINT [DF__Resource__Disabl__5C88EB7F]  DEFAULT (0),
	[PasswordSet] [datetime] NOT NULL CONSTRAINT [DF__Resource__Passwo__5D7D0FB8]  DEFAULT (1 / 1 / 1900),
	[PrimaryFunctionId] [uniqueidentifier] NOT NULL,
	[HireDate] [datetime] NULL,
	[TerminationDate] [datetime] NULL,
	[UserMetaTags] [nvarchar](255) NOT NULL DEFAULT (''),
	[SystemMetaTags] [nvarchar](255) NOT NULL DEFAULT (''),
	[APILoginAttempts] [tinyint] NULL,
	[CostCenterId] [uniqueidentifier] NOT NULL DEFAULT ('{00000000-0000-0000-0000-000000000000}'),
	[CUAManaged] [bit] NOT NULL DEFAULT (0),
 CONSTRAINT [PK__Resource__21A0F6C4] PRIMARY KEY CLUSTERED 
(
	[ResourceId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

INSERT INTO [Changepoint].[dbo].[Resources]
           ([ResourceId]
           ,[Name]
           ,[FirstName]
           ,[MiddleName]
           ,[LastName]
           ,[Description]
           ,[UserDefinedResourceId]
           ,[Title]
           ,[Language]
           ,[BaseCurrency]
           ,[WebPassword]
           ,[MailURL]
           ,[Signature]
           ,[CreatedOn]
           ,[CreatedBy]
           ,[UpdatedOn]
           ,[UpdatedBy]
           ,[Deleted]
           ,[LastLogin]
           ,[ResourceType]
           ,[Licensed]
           ,[ReportsTo]
           ,[DisableEmail]
           ,[Location]
           ,[IndustryExperience]
           ,[EmployeeType]
           ,[License]
           ,[AlternateName]
           ,[AllowGaps]
           ,[LCID]
           ,[FailedLoginAttempts]
           ,[DisableLogin]
           ,[PasswordSet]
           ,[PrimaryFunctionId]
           ,[HireDate]
           ,[TerminationDate]
           ,[UserMetaTags]
           ,[SystemMetaTags]
           ,[APILoginAttempts]
           ,[CostCenterId]
           ,[CUAManaged])
     VALUES
           (<ResourceId, uniqueidentifier,>
           ,<Name, nvarchar(200),>
           ,<FirstName, nvarchar(50),>
           ,<MiddleName, nvarchar(50),>
           ,<LastName, nvarchar(50),>
           ,<Description, ntext,>
           ,<UserDefinedResourceId, nvarchar(255),>
           ,<Title, nvarchar(255),>
           ,<Language, varchar(3),>
           ,<BaseCurrency, varchar(3),>
           ,<WebPassword, varchar(255),>
           ,<MailURL, nvarchar(255),>
           ,<Signature, varchar(50),>
           ,<CreatedOn, datetime,>
           ,<CreatedBy, uniqueidentifier,>
           ,<UpdatedOn, datetime,>
           ,<UpdatedBy, uniqueidentifier,>
           ,<Deleted, bit,>
           ,<LastLogin, datetime,>
           ,<ResourceType, varchar(3),>
           ,<Licensed, bit,>
           ,<ReportsTo, uniqueidentifier,>
           ,<DisableEmail, bit,>
           ,<Location, nvarchar(255),>
           ,<IndustryExperience, nvarchar(255),>
           ,<EmployeeType, varchar(3),>
           ,<License, int,>
           ,<AlternateName, nvarchar(200),>
           ,<AllowGaps, bit,>
           ,<LCID, smallint,>
           ,<FailedLoginAttempts, tinyint,>
           ,<DisableLogin, bit,>
           ,<PasswordSet, datetime,>
           ,<PrimaryFunctionId, uniqueidentifier,>
           ,<HireDate, datetime,>
           ,<TerminationDate, datetime,>
           ,<UserMetaTags, nvarchar(255),>
           ,<SystemMetaTags, nvarchar(255),>
           ,<APILoginAttempts, tinyint,>
           ,<CostCenterId, uniqueidentifier,>
           ,<CUAManaged, bit,>)

SELECT r.name AS Resource_Name, rw.name AS Business_Unit, t.NAME
FROM Resources r 
INNER JOIN Workgroupmember wgm ON wgm.ResourceID = R.ResourceID and wgm.historical = 0
INNER JOIN Workgroup rw ON rw.workgroupid = wgm.workgroupid
INNER JOIN Tasks t ON t.taskid = ta.taskid
WHERE r.deleted = 0

SELECT r.name AS Resource_Name, rw.name AS Business_Unit, t.NAME
FROM Resources r 
INNER JOIN Workgroupmember wgm ON wgm.ResourceID = R.ResourceID and wgm.historical = 0
INNER JOIN Workgroup rw ON rw.workgroupid = wgm.workgroupid
INNER JOIN Tasks t ON t.taskid = ta.taskid
WHERE r.deleted = 0