Syntax Error - SQL 2005

Server: Msg 207, Level 16, State 3, Line 11
Invalid column name 'ResourceId'.

I keep getting the enclosed error.

If I run the top section apart from the other Union's it returns fine. Idea's?
/*Schedule Summary*/
SET NOCOUNT ON
 
--Patient Appointments
 
DECLARE @Today DATETIME
DECLARE @Tomorrow DATETIME
SET @Today = '10/25/2007'
SET @Tomorrow = dateadd(d, 1, '10/25/2007')
 
SELECT DISTINCT
	Date=convert(datetime,convert(char(12),a.ApptStart,1)), 
 	convert(datetime,a.ApptStart) AS ResourceStart,
 	convert(datetime,a.ApptStop) AS ApptStop,
	ApptTime = datediff(minute, a.ApptStart, a.ApptStop),
	dfr.Listname AS Resource,
	a.ResourceId,
	Facility= dff.Listname,
	dff.facilityid AS FacilityId,
	dfd.Listname AS DoctorName,
	ISNULL(at.NAME,'No Appointment Type') AS Type,
	RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) + ', '+ ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,'')) AS 'Patient Name',
	CASE WHEN a.canceled = '1' THEN NULL ELSE a.appointmentsid END AS AppointmentsID,
	ApptKind = 1,
	a.canceled AS Canceled,
	CASE WHEN a.canceled IS NULL THEN NULL ELSE ml.Description END as [Cancel Status],
	CASE WHEN aps.ListOrder IS NULL THEN 'Overbooked' ELSE CAST(aps.ListOrder AS Varchar(10)) END AS [Column]
	
FROM 	Appointments a
	JOIN DoctorFacility dff ON a.FacilityId = dff.DoctorFacilityId
	JOIN PatientProfile pp ON a.OwnerId = pp.PatientProfileId
	JOIN DoctorFacility dfr ON a.ResourceId = dfr.DoctorFacilityId
	LEFT JOIN DoctorFacility dfd ON a.DoctorId = dfd.DoctorFacilityId
	LEFT JOIN ApptType at ON a.ApptTypeId = at.ApptTypeId
	LEFT JOIN ApptSlot aps ON a.AppointmentsId = aps.ApptId
	LEFT JOIN Medlists ml ON a.ApptStatusMId = ml.MedlistsId
 
WHERE 	ApptKind = 1 /*AND ISNULL(Canceled,0)  = 0*/ AND
	a.ApptStart >= ISNULL('10/25/2007','1/1/1900') AND a.ApptStart < dateadd(d, 1, ISNULL('10/25/2007','1/1/3000'))
	AND  --Filter on doctor
	(
	('7' IS NOT NULL AND a.ResourceID IN (7)) OR
	('7' IS NULL)
	)
	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND a.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
	
UNION ALL
 
--Resource Appointments
 
SELECT DISTINCT
	Date=convert(datetime,convert(char(12),a.ApptStart,1)), 
 	convert(datetime,a.ApptStart) AS ResourceStart,
 	convert(datetime,a.ApptStop) AS ApptStop,
	ApptTime = datediff(minute, a.ApptStart, a.ApptStop),
	dfr.Listname AS Resource,
	dfr.ResourceId,
	Facility= dff.Listname,
	dff.facilityId AS FacilityId,
	NULL AS DoctorName,
	ISNULL(at.NAME,'No Appointment Type') AS Type,
	CASE WHEN ApptKind = 3 THEN '<Doctor/Resource>' 
                          WHEN ApptKind = 5 THEN '<Block Out>' 
                          ELSE '<Other>' END AS 'Patient Name',
	NULL AS AppointmentsID,
	a.ApptKind,
	NULL AS Canceled,
	NULL as [Cancel Status],
	CASE WHEN aps.ListOrder IS NULL THEN 'Overbooked' ELSE CAST(aps.ListOrder AS Varchar(10)) END AS [Column] 
 
FROM 	Appointments a
	JOIN DoctorFacility dff ON a.FacilityId = dff.DoctorFacilityId
	JOIN DoctorFacility dfr ON a.ResourceId = dfr.DoctorFacilityId
	LEFT JOIN ApptType at ON a.ApptTypeId = at.ApptTypeId
	LEFT JOIN ApptSlot aps ON a.AppointmentsId = aps.ApptId
	LEFT JOIN Medlists ml ON a.ApptStatusMId = ml.MedlistsId
 
WHERE 	ApptKind <> 1 AND
	a.ApptStart >= ISNULL('10/25/2007','1/1/1900') AND a.ApptStart < dateadd(d, 1, ISNULL('10/25/2007','1/1/3000'))
	AND  --Filter on doctor
	(
	('7' IS NOT NULL AND a.ResourceID IN (7)) OR
	('7' IS NULL)
	)
	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND a.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
	
UNION ALL
 
SELECT  DISTINCT
	Date=convert(datetime,convert(char(12),Start,1)), 
	convert(datetime,aps.Start) AS ResourceStart,
 	convert(datetime,aps.Stop) AS ApptStop,
	ApptTime = case when tmp.appttypeid in (select appttypeid from appttype where name like '%lunch%'OR name LIKE '%meeting%') then 0 
	else datediff(minute, aps.Start, aps.Stop) end,
	df.Listname AS Resource,
	df.ResourceId,
	Facility= f.ListName,
	f.FacilityId AS FacilityId,
	d.Listname AS DoctorName,
	case 
	case when tmp.appttypeid in (select appttypeid from appttype where name like '%lunch%') then 0 
	when tmp.appttypeid in (select appttypeid from appttype where name like '%meeting%') then -1 
	else datediff(minute, aps.Start, aps.Stop) end when 0 then 'Lunch' when -1 then 'Meeting' else isnull(apt.Name,'Available / No Appt Type') END as Type,
	case 
	case when tmp.appttypeid in (select appttypeid from appttype where name like '%lunch%') then 0
	when tmp.appttypeid in (select appttypeid from appttype where name like '%meeting%') then -1 
	else datediff(minute, aps.Start, aps.Stop) end when 0 then 'Lunch' when -1 then 'Meeting' else '**Available**' END as 'Patient Name',
	a.appointmentsid AS AppointmentsID,
	1 as ApptKind,
	a.canceled AS Canceled,
	ml.Description as [Cancel Status],
	CASE WHEN aps.ListOrder IS NULL THEN 'Overbooked' ELSE CAST(aps.ListOrder AS Varchar(10)) END AS [Column] 
	
FROM	ApptSlot aps
	JOIN Schedule s ON aps.ScheduleId = s.ScheduleId
	JOIN DoctorFacility df ON s.DoctorResourceId = df.DoctorFacilityId
	JOIN DoctorFacility f ON aps.FacilityId = f.DoctorFacilityId
	JOIN DoctorFacility d ON s.DoctorResourceId = d.DoctorFacilityId
	LEFT JOIN Appointments a ON aps.apptid = a.appointmentsID
	LEFT JOIN Medlists ml ON a.ApptStatusMId = ml.MedlistsId
	left join 
	(
	select sta.timestart,sta.timestop,st.scheduleid,sta.appttypeid
	from scheduletimesalloc sta
	join scheduletimes st on st.scheduletimesid=sta.scheduletimesid
	) tmp on tmp.scheduleid=s.scheduleid 
	and cast('1/1/1900 '+cast(datepart(hour,aps.start) as varchar(10))+':'+cast(datepart(minute,aps.start) as varchar(10)) as datetime) between tmp.timestart and tmp.timestop
	and cast('1/1/1900 '+cast(datepart(hour,aps.stop) as varchar(10))+':'+cast(datepart(minute,aps.stop) as varchar(10)) as datetime) between tmp.timestart and tmp.timestop
	left join ApptType apt on apt.ApptTypeId = tmp.ApptTypeId
 
WHERE	--Filter on resource
	(
	('7' IS NOT NULL AND df.DoctorFacilityId IN (7)) OR
	('7' IS NULL)
	)
	AND
	(
	(NULL IS NOT NULL AND aps.FacilityId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND (Start >= @Today OR @Today IS NULL)
	AND (Start < @Tomorrow OR @Tomorrow IS NULL)
	AND ApptId IS NULL
	AND APS.ListOrder <> -1
 
ORDER BY [Resource], [ResourceStart]

Open in new window

LVL 7
Jeff SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JimBrandleyCommented:
I think the problem is either the alias or the column name in this block:
--Resource Appointments
 
SELECT DISTINCT
   Date=convert(datetime,convert(char(12),a.ApptStart,1)),
   convert(datetime,a.ApptStart) AS ResourceStart,
   convert(datetime,a.ApptStop) AS ApptStop,
   ApptTime = datediff(minute, a.ApptStart, a.ApptStop),
   dfr.Listname AS Resource,
   dfr.ResourceId,
   Facility= dff.Listname,
   dff.facilityId AS FacilityId,
   NULL AS DoctorName,
   ISNULL(at.NAME,'No Appointment Type') AS Type,
   CASE WHEN ApptKind = 3 THEN '<Doctor/Resource>'
                          WHEN ApptKind = 5 THEN '<Block Out>'
                          ELSE '<Other>' END AS 'Patient Name',
   NULL AS AppointmentsID,
   a.ApptKind,
   NULL AS Canceled,
   NULL as [Cancel Status],
   CASE WHEN aps.ListOrder IS NULL THEN 'Overbooked' ELSE CAST(aps.ListOrder AS Varchar(10)) END AS [Column]
 
FROM  Appointments a
   JOIN DoctorFacility dff ON a.FacilityId = dff.DoctorFacilityId
   JOIN DoctorFacility dfr ON a.ResourceId = dfr.DoctorFacilityId
   LEFT JOIN ApptType at ON a.ApptTypeId = at.ApptTypeId
   LEFT JOIN ApptSlot aps ON a.AppointmentsId = aps.ApptId
   LEFT JOIN Medlists ml ON a.ApptStatusMId = ml.MedlistsId

I suspect it should be  a.ResourceId or dfr.DoctorFacilityId
in the list of columns you are selecting.

Jim
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeff SAuthor Commented:
Doh! Thanks
0
JimBrandleyCommented:
My pleasure. Good luck.

Jim
 
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.