SQL统计每月每天不同时间段预约次数_数据

 

--生成2018-01-01至2028-01-01的日期临时表#dt_month
--实际应用中，建议创建成一个日期表，可减少每次调用生成临时表的性能损耗
create table #dt_month(yyrq varchar(10),[year] int,[month] int,[day] int)
declare @thisdate varchar(10),@enddate varchar(10)
set @thisdate='2018-01-01'
set @enddate='2068-01-01'
while(cast(@thisdate as datetime)<=cast(@enddate as datetime))
begin
  insert into #dt_month([yyrq],[year],[month],[day]) values(@thisdate,year(@thisdate),month(@thisdate),day(@thisdate))
  set @thisdate=convert(varchar(10),dateadd(day,1,cast(@thisdate as datetime)),120)
end
go
--select * from #dt_month
--go

--模拟数据
select '2018-11-01 00:00:00:000' yyrq,'10:00-11:00' yysj into #dt_data
union all
select '2018-11-02 00:00:00:000' yyrq,'10:00-11:00' yysj
union all
select '2018-11-02 00:00:00:000' yyrq,'10:00-11:00' yysj
union all
select '2018-11-02 00:00:00:000' yyrq,'9:00-10:00' yysj
union all
select '2018-11-03 00:00:00:000' yyrq,'10:00-11:00' yysj
union all
select '2018-11-04 00:00:00:000' yyrq,'10:00-11:00' yysj
union all
select '2018-11-05 00:00:00:000' yyrq,'10:00-11:00' yysj
go
select * from #dt_data
go


--统计2018年11月份的数据
select 
  a.yyrq,
  [9:00-10:00]=sum(case b.yysj when '9:00-10:00' then b.record else 0 end),
  [10:00-11:00]=sum(case b.yysj when '10:00-11:00' then b.record else 0 end),
  [11:00-12:00]=sum(case b.yysj when '11:00-12:00' then b.record else 0 end)
from #dt_month a
left join
(
  select yyrq,yysj,count(*) record from #dt_data group by yyrq,yysj
)b on a.yyrq=convert(varchar(10),b.yyrq,120)
where a.[year]=2018 and a.[month]=11
group by a.yyrq


--删除临时表
drop table #dt_month,#dt_data

SQL统计每月每天不同时间段预约次数_数据_02