34,594
社区成员
发帖
与我相关
我的任务
分享
declare @sql nvarchar(max),@col nvarchar(max),@s_time varchar(10),@e_time varchar(10)
set @s_time='2012-11-01'
set @e_time='2012-12-01'
;with cte as(
select dateadd(day,number,@s_time) time
from master..spt_values where type='P' and number<31
)select @sql=isnull(@sql+',','')+ quotename(convert(varchar,time,23))
,@col=isnull(@col+',isnull(','isnull(')+ quotename(convert(varchar,time,23))+',0) as '+quotename(convert(varchar,time,23))
from cte where time<@e_time
set @sql=';with cte as(
select convert(varchar,dateadd(day,number,@s_time),23) time
from master..spt_values where type=''P'' and number<31
),cte1 as
(select convert(varchar,DealStartTime,23) t,datename(hh,DealStartTime) h,count(1) c
from NormalDealInfo where DealStartTime>@s_time and DealStartTime<@e_time
and CONVERT(varchar(8),DealStartTime,108) between ''07:00:00'' and ''23:00:00''
group by convert(varchar,DealStartTime,23),datename(hh,DealStartTime)
),cte2 as
(select a.time,b.h,b.c from cte a left join cte1 b on a.time=b.t where a.time<@e_time)
select h,'+@col+' from cte2 pivot (max(c) for time in ('+@sql+'))a'
select @sql
exec sp_executesql @sql,N'@s_time varchar(10),@e_time varchar(10)',@s_time,@e_time
declare @sql nvarchar(max),@s_time varchar(10),@e_time varchar(10)
set @s_time='2012-11-01'
set @e_time='2012-12-01'
;with cte as(
select dateadd(day,number,@s_time) time
from master..spt_values where type='P' and number<31
)
select @sql=isnull(@sql+',','')+ quotename(right(convert(varchar,time,23),5)) from cte where time<@e_time
set @sql=';with cte as(
select convert(varchar,dateadd(day,number,@s_time),23) time
from master..spt_values where type=''P'' and number<31
),cte1 as
(select cast(datename(hh,DealStartTime) as int) h,convert(varchar,DealStartTime,23) t,count(*) c
from NormalDealInfo where DealStartTime >@s_time and DealStartTime<@e_time
and CONVERT(varchar(8),DealStartTime,108) between ''07:00:00'' and ''22:00:00''
group by cast(datename(hh,DealStartTime) as int),convert(varchar,DealStartTime,23)
),cte2 as
(select a.time,b.h,b.c from cte a left join cte1 b on a.time=b.t where a.time<@e_time)
select * from cte2 pivot (max(c) for time in ('+@sql+'))a'
select @sql
exec sp_executesql @sql,N'@s_time varchar(10),@e_time varchar(10)',@s_time,@e_time
declare @sql nvarchar(max),@s_time varchar(10),@e_time varchar(10)
set @s_time='2012-11-01'
set @e_time='2012-12-01'
;with cte as(
select dateadd(day,number,@s_time) time
from master..spt_values where type='P' and number<31
)select @sql=isnull(@sql+',','')+ quotename(convert(varchar,time,23)) from cte where time<@e_time
set @sql=';with cte as(
select convert(varchar,dateadd(day,number,@s_time),23) time
from master..spt_values where type=''P'' and number<31
),cte1 as
(select convert(varchar,DealStartTime,23) t,datename(hh,DealStartTime) h,count(1) c
from NormalDealInfo where DealStartTime >@s_time and DealStartTime<@e_time
and CONVERT(varchar(8),DealStartTime,108) between ''07:00:00'' and ''23:00:00''
group by convert(varchar,DealStartTime,23),datename(hh,DealStartTime)
),cte2 as
(select a.time,b.h,b.c from cte a left join cte1 b on a.time=b.t where a.time<@e_time)
select * from cte2 pivot (max(c) for time in ('+@sql+'))a'
select @sql
exec sp_executesql @sql,N'@s_time varchar(10),@e_time varchar(10)',@s_time,@e_time
declare @sql nvarchar(max),@s_time varchar(10),@e_time varchar(10)
set @s_time='2012-01-01'
set @e_time='2012-02-01'
;with cte as(
select dateadd(day,number,@s_time) time
from master..spt_values where type='P' and number<31
)select @sql=isnull(@sql+',','')+ quotename(right(convert(varchar,time,23),5)) from cte where time<@e_time
set @sql=';with cte as(
select convert(varchar,dateadd(day,number,@s_time),23) time
from master..spt_values where type=''P'' and number<31
),cte1 as
(select convert(varchar,NormalDealInfo,23) t,datename(hh,NormalDealInfo) h,count(1) c
from NormalDealInfo where NormalDealInfo >@s_time and NormalDealInfo<@e_time
and CONVERT(varchar(8),b.DealStartTime,108) between '07:00:00' and '22:59:59'
group by convert(varchar,NormalDealInfo,23),datename(hh,NormalDealInfo)
)select a.time,b.h,b.c from cte a left join cte1 b on a.time=b.t'
select @sql
exec sp_executesql @sql,N'@s_time varchar(10),@e_time varchar(10)',@s_time,@e_time
declare @sql nvarchar(max),@s_time varchar(10),@e_time varchar(10)
set @s_time='2012-11-01'
set @e_time='2012-12-01'
;with cte as(
select dateadd(day,number,@s_time) time
from master..spt_values where type='P' and number<31
)select @sql=isnull(@sql+',','')+ quotename(right(convert(varchar,time,23),5)) from cte where time<@e_time
set @sql=';with cte as(
select convert(varchar,dateadd(day,number,@s_time),23) time
from master..spt_values where type=''P'' and number<31
),cte1 as
(select convert(varchar,DealStartTime,23) t,datename(hh,DealStartTime) h,count(1) c
from NormalDealInfo where DealStartTime >@s_time and DealStartTime<@e_time
and CONVERT(varchar(8),DealStartTime,108) between ''07:00:00'' and ''22:00:00''
group by convert(varchar,DealStartTime,23),datename(hh,DealStartTime)
),cte2 as
(select a.time,b.h,b.c from cte a left join cte1 b on a.time=b.t)
select * from cte2 pivot (max(c) for h in ('+@sql+'))a'
select @sql
exec sp_executesql @sql,N'@s_time varchar(10),@e_time varchar(10)',@s_time,@e_time
declare @sql nvarchar(max),@s_time varchar(10),@e_time varchar(10)
set @s_time='2012-01-01'
set @e_time='2012-02-01'
;with cte as(
select dateadd(day,number,@s_time) time
from master..spt_values where type='P' and number<31
)select @sql=isnull(@sql+',','')+ quotename(right(convert(varchar,time,23),5)) from cte where time<@e_time
set @sql=';with cte as(
select convert(varchar,dateadd(day,number,@s_time),23) time
from master..spt_values where type=''P'' and number<31
),cte1 as
(select convert(varchar,NormalDealInfo,23) t,datename(hh,NormalDealInfo) h,count(1) c
from NormalDealInfo where NormalDealInfo >@s_time and NormalDealInfo<@e_time
and CONVERT(varchar(8),b.DealStartTime,108) between '07:00:00' and '22:00:00'
group by convert(varchar,NormalDealInfo,23),datename(hh,NormalDealInfo)
),cte2 as
(select a.time,b.h,b.c from cte a left join cte1 b on a.time=b.t)
select * from cte2 pivot (max(c) for h in ('+@sql+'))a'
select @sql
exec sp_executesql @sql,N'@s_time varchar(10),@e_time varchar(10)',@s_time,@e_time
declare @sql nvarchar(max),@s_time varchar(10),@e_time varchar(10)
set @s_time='2012-11-01'
set @e_time='2012-12-01'
;with cte as(
select dateadd(day,number,@s_time) time
from master..spt_values where type='P' and number<31
)select @sql=isnull(@sql+',','')+ quotename(right(convert(varchar,time,23),5)) from cte where time<@e_time
set @sql=';with cte as(
select convert(varchar,dateadd(day,number,@s_time),23) time
from master..spt_values where type=''P'' and number<31
),cte1 as
(select convert(varchar,DealStartTime,23) t,datename(hh,DealStartTime) h,count(1) c
from NormalDealInfo where DealStartTime >@s_time and DealStartTime<@e_time
and CONVERT(varchar(8),DealStartTime,108) between ''07:00:00'' and ''22:00:00''
group by convert(varchar,DealStartTime,23),datename(hh,DealStartTime)
),cte2 as
(select a.time,b.h,b.c from cte a left join cte1 b on a.time=b.t where a.time<@e_time)
select * from cte2 pivot (max(c) for time in ('+@sql+'))a'
select @sql
exec sp_executesql @sql,N'@s_time varchar(10),@e_time varchar(10)',@s_time,@e_time
declare @sql nvarchar(max),@s_time varchar(10),@e_time varchar(10)
set @s_time='2012-01-01'
set @e_time='2012-02-01'
;with cte as(
select dateadd(day,number,@s_time) time
from master..spt_values where type='P' and number<31
)select @sql=isnull(@sql+',','')+ quotename(right(convert(varchar,time,23),5)) from cte where time<@e_time
set @sql=';with cte as(
select convert(varchar,dateadd(day,number,@s_time),23) time
from master..spt_values where type=''P'' and number<31
),cte1 as
(select convert(varchar,time,23) t,datename(hh,time) h,count(1) c
from tb where time>@s_time and time<@e_time group by convert(varchar,time,23),datename(hh,time)
),cte2 as
(select a.time,b.h,b.c from cte a left join cte1 b on a.time=b.t)
select * from cte2 pivot (max(c) for h in ('+@sql+'))a'
select @sql
exec sp_executesql @sql,N'@s_time varchar(10),@e_time varchar(10)',@s_time,@e_time