获取当天的订单--以半小时为单位的报表

获取当天的订单--以半小时为单位的报表，格式如下：

 

     时间              订单数
14:00:00 - 14 :30:00     100
14:30:00 - 15 :00:00     102
15:00:00 - 15 :30:00     138
15:30:00 - 16 :00:00     142

 

T-SQL实现代码如下：

 

 

declare @Today varchar(10)
declare @Tommorow varchar(10)
set @Today = convert(varchar(10),getdate(),120)
set @Tommorow = convert(varchar(10),dateadd(d,1,getdate()),120)

--得到今天内的订单时间信息
select posttime into #TodayOrderList from OrderList where posttime between @Today and @Tommorow

select
    convert(varchar(13),getdate(),120) + '00:00' + '--'+ convert(varchar(13),getdate(),120) + '29:59' as 时间,
    sum(1)
from #TodayOrderList where datepart(m,posttime) between 0 and 29
union
select
    convert(varchar(13),getdate(),120) + '30:00' + '--'+ convert(varchar(13),getdate(),120) + '59:59' as 时间,
    sum(1)
from #TodayOrderList where datepart(m,posttime) between 30 and 59