donetcharting中使用的sql语句

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