The purpose of the query below is return all cases that were done during a given time frame, but limit it to cases that have and EnterOR or ExitOR time between a set of user defined hours (such as 1500 and 0700 the next day). That is the part I'm having trouble with. The code below works with just the hours of the enterOR/ExitOR time. But if the user passes a value such as 1530 and 0630 then my logic get cases they don't want. I'm not sure how to build a where clause that can cast a value based on the case date or next day.

Thanks for any help and pointers.

Kirk

Declare
@QueryFrom DateTime,
@QueryTo DateTime,
@tcStartTime varchar(8),
@tcEndTime varchar(8)

set @QueryFrom = '12/01/2004 00:00:01'
set @QueryTo   = '12/31/2004 23:59:59'
set @tcStartTime = '14:59:00'
set @tcEndTime   = '06:59:00'

select det.meetingnumber as ConfNumber,det.begintime,det.EndTime,det.EnterOR,det.ExitOR,
	(Select rtrim(Lastname)+', '+rtrim(FirstName) from dbo.patients where patientID=det.PatientID) as PatientName,
	(Select mpinumber from dbo.patients where patientID=det.PatientID) as medRecNo,
	(SELECT TOP 1 RTRIM(dbo.coper.lastname) + ', ' + RTRIM(dbo.coper.firstname)
	   FROM dbo.meetproc INNER JOIN
                dbo.coper ON dbo.meetproc.coperid = dbo.coper.coperid
                where dbo.meetproc.meetingnumber=det.meetingnumber
	   ORDER BY dbo.meetproc.sysorder) as SurgeonName,
	dbo.Get_ProceduresOnCase(det.MeetingNumber) as PrimaryProc,

	(Select deptdesc from dbo.coDept where coDept.DeptID=det.DeptID) as DepartmentDesc,
	det.deptid,
	substring(@tcStartTime,1,2),
	substring(@tcEndTime,1,2),
	datepart(hh,det.EnterOR) as DatePartEnterOR,
	DatePart(hh,det.ExitOR) as DateParExitOR

from 
(Select meetings.meetingnumber,BeginTime,EndTime,Enteror,ExitOR,PatientID,DeptID
from dbo.meetings
where begintime between @QueryFrom and @QueryTo
and iscompleted=1) as det
where (datepart(hh,det.EnterOR) between substring(@tcStartTime,1,2) and 23
   or datepart(hh,det.EnterOR) between 0 and substring(@tcEndTime,1,2)-1)
   or (datepart(hh,det.ExitOR)  between substring(@tcStartTime,1,2) and 23
   or datepart(hh,det.ExitOR) between 0 and substring(@tcEndTime,1,2)-1)
order by departmentdesc,EnterOR