select * from (
select * from 视图1 where moDays <=day(dateadd(month,1,'2013-10-27 18:08:00.000') - day('2013-10-27 18:08:00.000'))
) as dd2 left join (
select datepart(day,addtime) as mo2,
SUM(case when complaintype ='投诉' then 1 else 0 end) AS TS_TS,
SUM(case when complaintype ='建议' then 1 else 0 end) AS TS_JY
from (select * from 表1 where datediff(month,数据存储时间列1,'2013-10-27 18:08:00.000') = 0 and id= '666') ts group by id,datepart(day,数据存储时间列1)
) as d2 on dd2.moDays = d2.mo2
实现思路:新建一个1-31的视图,然后得到传入日期的当数,最后得到1-实际天数。然后left join 根据天数来求和。 这样就可以得到所以天数的统计了。如果不用left jion日期的视图,得到的只是有数据的日的统计(即无数据的日期不显示)。 先求另一种实现思路。报表按月每日统计报表
select * from 视图1 where moDays <=day(dateadd(month,1,'2013-10-27 18:08:00.000') - day('2013-10-27 18:08:00.000'))
) as dd2 left join (
select datepart(day,addtime) as mo2,
SUM(case when complaintype ='投诉' then 1 else 0 end) AS TS_TS,
SUM(case when complaintype ='建议' then 1 else 0 end) AS TS_JY
from (select * from 表1 where datediff(month,数据存储时间列1,'2013-10-27 18:08:00.000') = 0 and id= '666') ts group by id,datepart(day,数据存储时间列1)
) as d2 on dd2.moDays = d2.mo2
实现思路:新建一个1-31的视图,然后得到传入日期的当数,最后得到1-实际天数。然后left join 根据天数来求和。 这样就可以得到所以天数的统计了。如果不用left jion日期的视图,得到的只是有数据的日的统计(即无数据的日期不显示)。 先求另一种实现思路。报表按月每日统计报表
select *
from
(
select * from 视图1
where moDays <=day(dateadd(month,1,'2013-10-27 18:08:00.000') - day('2013-10-27 18:08:00.000'))
) as dd2
left join
(
select datepart(day,addtime) as mo2,
SUM(case when complaintype ='投诉' then 1 else 0 end) AS TS_TS,
SUM(case when complaintype ='建议' then 1 else 0 end) AS TS_JY
from 表1
where datediff(month,数据存储时间列1,'2013-10-27 18:08:00.000') = 0 and id= '666'
group by id,datepart(day,数据存储时间列1)
) as d2
on dd2.moDays = d2.mo2
;with t
as
(
select 1 as moDays
union all
select moDays + 1
from t
where t.moDays < 31
)
select *
from
(
select * from t
where moDays <=day(dateadd(month,1,'2013-10-27 18:08:00.000') - day('2013-10-27 18:08:00.000'))
) as dd2
left join
(
select datepart(day,addtime) as mo2,
SUM(case when complaintype ='投诉' then 1 else 0 end) AS TS_TS,
SUM(case when complaintype ='建议' then 1 else 0 end) AS TS_JY
from
(
select * from 表1
where datediff(month,数据存储时间列1,'2013-10-27 18:08:00.000') = 0 and id= '666'
) ts
group by id,datepart(day,数据存储时间列1)
) as d2
on dd2.moDays = d2.mo2
如果有兴趣的话版主大大来一个第32行是前面31行的的总和 ,然后把null显示为 0 。怎样。
第32行是前面31行的的总和 ,然后把null显示为 0
你用的是sql server 2005以后的版本不
select case when grouping(dd2.moDays) = 1 then 32
else dd2.moDays
end moDays ,
sum(TS_TS) as TS_TS,sum(TS_JY) as TS_JY
from
(
select * from 视图1
where moDays <=day(dateadd(month,1,'2013-10-27 18:08:00.000') - day('2013-10-27 18:08:00.000'))
) as dd2
left join
(
select datepart(day,addtime) as mo2,
SUM(case when complaintype ='投诉' then 1 else 0 end) AS TS_TS,
SUM(case when complaintype ='建议' then 1 else 0 end) AS TS_JY
from 表1
where datediff(month,数据存储时间列1,'2013-10-27 18:08:00.000') = 0 and id= '666'
group by id,datepart(day,数据存储时间列1)
) as d2
on dd2.moDays = d2.mo2
group by rollup(dd2.moDays)
with Table1(TS_YL,TS_WL,TS_JY) as
(
select isnull(TS_YL,0) as TS_YL,isnull(TS_WL,0) as TS_WL,isnull(TS_JY,0) as TS_JY
from (
select * from (
select * from v_tr_callmgrTime where moDays <=day(dateadd(month,1,'2013-10-20 18:08:00.000') - day('2013-10-20 18:08:00.000'))
) as dd2 left join (
select datepart(day,addtime) as mo2,
SUM(case when [type] ='有理' then 1 else 0 end) AS TS_YL,
SUM(case when [type] ='无理' then 1 else 0 end) AS TS_WL,
SUM(case when complaintype ='建议' then 1 else 0 end) AS TS_JY
from (select * from xxx as tc ,userTable as tm where tc.id= tm.id and datediff(month,addtime,'2013-10-20 18:08:00.000') = 0 and tm.JOB_NUMBER = '8006') ts group by id,datepart(day,addtime)
) as d2 on dd2.moDays = d2.mo2
) d
)
select TS_YL,TS_WL,TS_JY from Table1 UNION ALL
select SUM(TS_YL),SUM(TS_WL),SUM(TS_JY) from Table1 null为0,最后一行显示总和 。还有比较简单的方法吗,好像WITH有版本限制的
select *
from
(
select *
from
(
select number as moDays
from master..spt_values
where type='P' and number between 1 and 31
)
where moDays <=day(dateadd(month,1,'2013-10-27 18:08:00.000') - day('2013-10-27 18:08:00.000'))
) as dd2
left join
(
select datepart(day,addtime) as mo2,
SUM(case when complaintype ='投诉' then 1 else 0 end) AS TS_TS,
SUM(case when complaintype ='建议' then 1 else 0 end) AS TS_JY
from
(
select * from 表1
where datediff(month,数据存储时间列1,'2013-10-27 18:08:00.000') = 0 and id= '666'
) ts
group by id,datepart(day,数据存储时间列1)
) as d2
on dd2.moDays = d2.mo2
from
(
select *
from
(
select number as moDays
from master..spt_values
where type='P' and number between 1 and 31
)t
where moDays <=day(dateadd(month,1,'2013-10-27 18:08:00.000') - day('2013-10-27 18:08:00.000'))
) as dd2
left join
(
select datepart(day,addtime) as mo2,
SUM(case when complaintype ='投诉' then 1 else 0 end) AS TS_TS,
SUM(case when complaintype ='建议' then 1 else 0 end) AS TS_JY
from
(
select * from 表1
where datediff(month,数据存储时间列1,'2013-10-27 18:08:00.000') = 0 and id= '666'
) ts
group by id,datepart(day,数据存储时间列1)
) as d2
on dd2.moDays = d2.mo2
select sum(isnull(TS_YL,0)) as TS_YL,sum(isnull(TS_WL,0)) as TS_WL,sum(isnull(TS_JY,0)) as TS_JY
from (
select * from
(
select * from v_tr_callmgrTime where moDays <=day(dateadd(month,1,'2013-10-20 18:08:00.000') - day('2013-10-20 18:08:00.000'))
) as dd2
left join (
select datepart(day,addtime) as mo2,
SUM(case when [type] ='有理' then 1 else 0 end) AS TS_YL,
SUM(case when [type] ='无理' then 1 else 0 end) AS TS_WL,
SUM(case when complaintype ='建议' then 1 else 0 end) AS TS_JY
from (select * from xxx as tc ,userTable as tm where tc.id= tm.id and datediff(month,addtime,'2013-10-20 18:08:00.000') = 0 and tm.JOB_NUMBER = '8006') ts group by id,datepart(day,addtime)
) as d2 on dd2.moDays = d2.mo2
) d
)
group by rollup(moDays)