alter procedure pro_hourshot
@conditionStart varchar(60),
@conditionEnd varchar(60)
as
if(@conditionStart is not null or
@conditionStart !=‘‘ or @conditionEnd is not null or @conditionEnd !=‘‘)
begin
with yuyue as (
select datepart(hh,dialogTime) as somehour, count(*) as
yuyuecount
from yy_detail where 1=1 and tujing=(select id from yy_tujingtype
where name=‘网络‘)
and (dialogTime between @conditionStart and
@conditionEnd)
-- @daoyuancondition and convert(varchar(10),dialogTime,120)
=‘2013-12-15‘
group by datepart(hh,dialogTime)
),daoyuan as(
select
datepart(hh,jizhendate) as somehour, count(*) as daoyuancount
from yy_detail
where 1=1 and tujing=(select id from yy_tujingtype where name=‘网络‘)
and
(jizhendate between @conditionStart and @conditionEnd)
-- @daoyuancondition
and convert(varchar(10),jizhendate,120) =‘2013-12-15‘
group by
datepart(hh,jizhendate)
)
select result.date,sum(result.aayuyuecount),sum(result.aadaoyuancount) from
(
select fullresult.somehour as date,AAAA.data_yuyuecount as
aayuyuecount,AAAA.data_daoyuancount as aadaoyuancount from
(
select
datepart(hh,dialogTime) as somehour from yy_detail where
convert(varchar(4),dialogTime,120)=‘2013‘ group by
datepart(hh,dialogTime)
) as fullresult
left join
(
select yuyue.somehour as data_hour , yuyue.yuyuecount as
data_yuyuecount,daoyuan.daoyuancount as data_daoyuancount
from yuyue
left join daoyuan on yuyue.somehour=daoyuan.somehour
)as
AAAA
on fullresult.somehour=AAAA.data_hour
union
select fullresult.somehour,BBB.data_yuyuecount,BBB.data_daoyuancount from
(
select datepart(hh,dialogTime) as somehour from yy_detail where
convert(varchar(4),dialogTime,120)=‘2013‘ group by
datepart(hh,dialogTime)
) as fullresult
left join
(
select daoyuan.somehour as data_hour ,
yuyue.yuyuecount as data_yuyuecount,daoyuan.daoyuancount as
data_daoyuancount
from daoyuan
left join yuyue on
yuyue.somehour=daoyuan.somehour
)as BBB
on
fullresult.somehour=BBB.data_hour
) as result
group by result.date
end
原文：http://www.cnblogs.com/wanglao/p/3538807.html