set @Shift1Start ='07:00' set @Shift1End ='18:59' set @Shift2Start ='19:00' set @Shift2End ='06:59' select (Select substring(roomname,1,10) from coRooms where coRooms.RoomID=meetings.RoomID) as RoomDesc, datediff(mi,dbo.meetings.anesstart,dbo.meetings.anesend) as AnesMins, dbo.meetings.AnesStart, dbo.meetings.AnesEnd, convert(varchar(5),anesstart,114) as jTime, case when convert(varchar(5),anesstart,114) between @Shift1Start and @Shift1End then 1 when convert(varchar(5),anesstart,114) between @Shift1End and '23:59' then 2 when convert(varchar(5),anesstart,114) between '00:00' and @Shift2End then 2 else 0 end as ShiftNumber from dbo.meetings where dbo.meetings.iscompleted=1 and dbo.meetings.deptID=@DeptID and dbo.meetings.Begintime between @BeginTime and @EndTime order by anesstart,RoomDesc