Below is the query SP I'm trying to write. I need to determine how many minutes in advance a case was cancelled. The system stores the Begintime as the scheduled start of a case. The canceldate field hold the datetime it was cancelled. Sometimes cases are cancelled after the scheduled start time (begintime), these I want to return as a zero. Otherwise, I want to return the number of minutes that the case was cancancelled before it's original begin time.

I can't figure out the write way to use the case. I keep getting an error message on the > portion of the statement.

Thanks for any help.

Kirk

CREATE PROCEDURE lu_CancelledCaseReview
@ttStart datetime,
@ttTo datetime,
@tiDeptid int

as

SELECT     dbo.patients.lastname, dbo.patients.firstname, dbo.patients.homenumber, 
	dbo.patients.worknumber, 
	dbo.codept.deptdesc, 
	dbo.meetproc.description, 
                 isnull(dbo.canccode.canceldesc,' ') as canceldesc, 
                 isnull(dbo.meetings.CancelDesc,' ') AS PatientCancelReason, 
                dbo.meetings.CancelDate,
	 ISNULL(dbo.schref.docname, ' ') AS Referring, 
                RTRIM(dbo.patients.lastname) + ', ' + RTRIM(dbo.patients.firstname) AS PatientName, 
	dbo.meetings.CancelCode, 
	dbo.meetings.iscancelled,
	dbo.patients.mpinumber,dbo.meetings.accountnumber,dbo.meetings.begintime,
                dbo.meetings.meetingnumber,
	MinutesInAdvanceCancelled=
		Case datediff(minute,canceldate,begintime)
		WHEN > 0 THEN 0
		ELSE	datediff(minutes,canceldate,begintime)
		END
	datediff(minute,dbo.meetings.CancelDate, dbo.meetings.begintime) as MinutesBeforeCase,
                datediff(hour,dbo.meetings.canceldate, dbo.meetings.begintime) as  HoursBeforeCase
FROM         dbo.patients INNER JOIN
                      dbo.meetings ON dbo.patients.patientid = dbo.meetings.patientid INNER JOIN
                      dbo.meetproc ON dbo.meetings.meetingnumber = dbo.meetproc.meetingnumber INNER JOIN
                      dbo.corooms ON dbo.meetings.roomid = dbo.corooms.roomid INNER JOIN
                      dbo.codept ON dbo.meetings.deptid = dbo.codept.deptid LEFT OUTER JOIN
                      dbo.canccode ON dbo.meetings.CancelCode = dbo.canccode.cancelcode LEFT OUTER JOIN
                      dbo.schref ON dbo.meetings.ReferringID = dbo.schref.refdocid
WHERE     (dbo.meetings.iscancelled = 1)

and dbo.meetings.begintime between @ttStart and @ttTo
and dbo.meetings.deptid=@tiDeptID
order by dbo.patients.lastname,dbo.patients.firstname,meetings.begintime
GO