Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Help With Query
Message
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Title:
Miscellaneous
Thread ID:
01245198
Message ID:
01245220
Views:
20
This logic seems to work, although it sets some things in stone rather than paratmers.
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
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform