• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

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

0
Jeff S
Asked:
Jeff S
  • 2
1 Solution
 
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
 
Jeff SAuthor Commented:
Doh! Thanks
0
 
JimBrandleyCommented:
My pleasure. Good luck.

Jim
 
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now