>Example data: >Room AnesStart AnesEnd ShiftNumber >ROOM1 05/15/2007 03:05:00 05/15/2007 04:31:00 2 >ROOM1 05/15/2007 19:01:00 05/15/2007 22:35:00 2 >ROOM2 05/16/2007 06:30:00 05/16/2007 07:45:00 2 >>
>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, > case when substring(convert(varchar(5),dbo.meetings.anesstart,114),1,5) between @Shift1Start and @Shift1End then 1 > when substring(convert(varchar(5),dbo.meetings.anesstart,114),1,5) between @Shift2Start and @Shift2End then 2 > else 0 > end as ShiftNumber, >