select

  cast(DATEADD(MINUTE,B.number*30,dateInfo.dday) as time) StartTime,

  cast(DATEADD(SECOND,-1,DATEADD(MINUTE,(B.number+1)*30,dateInfo.dday)) as time) EndTime

from

  (select top 1 convert(varchar(10),'07:00',120)  dday) dateInfo

  cross join master..spt_values b

where b.type = 'P' and number <24  

with cte as

(

select

  cast(DATEADD(MINUTE,B.number*30,dateInfo.dday) as time) StartTime,

  cast(DATEADD(SECOND,-1,DATEADD(MINUTE,(B.number+1)*30,dateInfo.dday)) as time) EndTime

from

  (select top 1 convert(varchar(10),'07:00',120)  dday) dateInfo

  cross join master..spt_values b

where b.type = 'P' and number <24

)

select

  StartTime,

  EndTime,

  sum(PreSum) PreSum,

  sum(PreUsed) PreUsed

from

  (

   select StartTime,EndTime,case when hbStartTime is null then 0 else 1 end PreSum,case when Used ='' then 0 else 1 end PreUsed

   from

     cte dateInfo

     left join yshb_timetable b on cast(b.hbStartTime as time) between StartTime and EndTime

     where b.YshbID='查询条件'

  )a

group by StartTime,EndTime