表结构如下:
Date 日期
hour 小时Date hour IVRTrans2Agt TransSucCount
----------------------- ---- ------------ -------------
2009-12-01 00:00:00.000 0 1 0
2009-12-01 00:00:00.000 0 0 1
2009-12-01 00:00:00.000 0 2 0
2009-12-01 00:00:00.000 1 0 0
2009-12-01 00:00:00.000 1 2 7
2009-12-01 00:00:00.000 1 0 0
2009-12-01 00:00:00.000 1 0 0
2009-12-01 00:00:00.000 2 0 0
2009-12-01 00:00:00.000 2 4 5
2009-12-01 00:00:00.000 2 0 0
2009-12-01 00:00:00.000 2 0 0
2009-12-01 00:00:00.000 2 0 6
2009-12-02 00:00:00.000 19 0 0
2009-12-02 00:00:00.000 19 0 0
2009-12-02 00:00:00.000 19 8 8
2009-12-02 00:00:00.000 19 0 0
.....2009-12-30 00:00:00.000 19 1 0现在想查询如表 2009年12月 这个月中 每天24小时分段统计 IVRTrans2Agt TransSucCount 的数量结果:
Date hour IVRTrans2Agt TransSucCount
----------------------- ---- ------------ -------------
00:00---01:00 0 52 47
01:00---02:00 0 23 34
....
23:00---24:00 0 1 47还有就是
Date IVRTrans2Agt TransSucCount
-------- ---- ------------ -------------
周一 200 470
周二 213 340
....
周六 100 470想请教高手,目前我的思路:(有些不正确)select top 100 right(100+c.hour,2)+ ':00 -> '+right(101+c.hour,2)+ ':00 ',
Sum(c.IVRTrans2Agt),sum(c.TransSucCount),sum(c.TransSucRate),sum(c.TotalCallOuts),sum(c.CallOutSucCnt),sum(c.CallOutsRate) from dbo.TSTA_EnteCallData c,
(select 0 a,1 b
union all select 1,2
union all select 2,3
union all select 3,4
union all select 4,5
union all select 5,6
union all select 6,7
union all select 7,8
union all select 8,9
union all select 9,10
union all select 10,11
union all select 11,12
union all select 12,13
union all select 13,14
union all select 14,15
union all select 15,16
union all select 16,17
union all select 17,18
union all select 18,19
union all select 19,20
union all select 20,21
union all select 21,22
union all select 22,23
union all select 23,24
) aa
where DATEPART(m,c.[Date]) =12 And DATEPART(yyyy,c.[Date])=2009group by right(100+c.hour,2)+ ':00 -> '+right(100+c.hour,2)+ ':00 ',c.hour order by right(100+c.hour,2)+ ':00 -> '+right(100+c.hour,2)+ ':00 '
我写的SQL实现的数据不正确,想在线请教高手
Date 日期
hour 小时Date hour IVRTrans2Agt TransSucCount
----------------------- ---- ------------ -------------
2009-12-01 00:00:00.000 0 1 0
2009-12-01 00:00:00.000 0 0 1
2009-12-01 00:00:00.000 0 2 0
2009-12-01 00:00:00.000 1 0 0
2009-12-01 00:00:00.000 1 2 7
2009-12-01 00:00:00.000 1 0 0
2009-12-01 00:00:00.000 1 0 0
2009-12-01 00:00:00.000 2 0 0
2009-12-01 00:00:00.000 2 4 5
2009-12-01 00:00:00.000 2 0 0
2009-12-01 00:00:00.000 2 0 0
2009-12-01 00:00:00.000 2 0 6
2009-12-02 00:00:00.000 19 0 0
2009-12-02 00:00:00.000 19 0 0
2009-12-02 00:00:00.000 19 8 8
2009-12-02 00:00:00.000 19 0 0
.....2009-12-30 00:00:00.000 19 1 0现在想查询如表 2009年12月 这个月中 每天24小时分段统计 IVRTrans2Agt TransSucCount 的数量结果:
Date hour IVRTrans2Agt TransSucCount
----------------------- ---- ------------ -------------
00:00---01:00 0 52 47
01:00---02:00 0 23 34
....
23:00---24:00 0 1 47还有就是
Date IVRTrans2Agt TransSucCount
-------- ---- ------------ -------------
周一 200 470
周二 213 340
....
周六 100 470想请教高手,目前我的思路:(有些不正确)select top 100 right(100+c.hour,2)+ ':00 -> '+right(101+c.hour,2)+ ':00 ',
Sum(c.IVRTrans2Agt),sum(c.TransSucCount),sum(c.TransSucRate),sum(c.TotalCallOuts),sum(c.CallOutSucCnt),sum(c.CallOutsRate) from dbo.TSTA_EnteCallData c,
(select 0 a,1 b
union all select 1,2
union all select 2,3
union all select 3,4
union all select 4,5
union all select 5,6
union all select 6,7
union all select 7,8
union all select 8,9
union all select 9,10
union all select 10,11
union all select 11,12
union all select 12,13
union all select 13,14
union all select 14,15
union all select 15,16
union all select 16,17
union all select 17,18
union all select 18,19
union all select 19,20
union all select 20,21
union all select 21,22
union all select 22,23
union all select 23,24
) aa
where DATEPART(m,c.[Date]) =12 And DATEPART(yyyy,c.[Date])=2009group by right(100+c.hour,2)+ ':00 -> '+right(100+c.hour,2)+ ':00 ',c.hour order by right(100+c.hour,2)+ ':00 -> '+right(100+c.hour,2)+ ':00 '
我写的SQL实现的数据不正确,想在线请教高手
select 时间段=right(100+number,2)+':00-'+right(101+number,2)+':00'
from master..spt_values
where type='p'
and number between 0 and 23
/*
时间段
00:00-01:00
01:00-02:00
02:00-03:00
03:00-04:00
04:00-05:00
05:00-06:00
06:00-07:00
07:00-08:00
08:00-09:00
09:00-10:00
10:00-11:00
11:00-12:00
12:00-13:00
13:00-14:00
14:00-15:00
15:00-16:00
16:00-17:00
17:00-18:00
18:00-19:00
19:00-20:00
20:00-21:00
21:00-22:00
22:00-23:00
23:00-24:00
*/
SUM(CASE WHEN CONVERT(VARCHAR(5),TIME,108) BETWEEN '00:00' AND '01:00' THEN .. ELSE 0 END )AS '00:00-01:00',
SUM(CASE WHEN CONVERT(VARCHAR(5),TIME,108) BETWEEN '00:00' AND '01:00' THEN .. ELSE 0 END )AS '00:00-01:00',
SUM(CASE WHEN CONVERT(VARCHAR(5),TIME,108) BETWEEN '00:00' AND '01:00' THEN .. ELSE 0 END )AS '00:00-01:00',
SUM(CASE WHEN CONVERT(VARCHAR(5),TIME,108) BETWEEN '00:00' AND '01:00' THEN .. ELSE 0 END )AS '00:00-01:00',
..
FROM TB WHERE ....参考行转列的精华
Sum(case when c.hour> =a
and c.hour <b
then c.IVRTrans2Agt else 0 end) as IVRTrans2Agt,
Sum(case when c.hour> =a
and c.hour <b
then c.TransSucCount else 0 end) as TransSucCount,
Sum(case when c.hour> =a
and c.hour <b
then c.TransSucRate else 0 end) as TransSucRate,
Sum(case when c.hour> =a
and c.hour <b
then c.TotalCallOuts else 0 end) as TotalCallOuts,
Sum(case when c.hour> =a
and c.hour <b
then c.CallOutSucCnt else 0 end) as CallOutSucCnt,
Sum(case when c.hour> =a
and c.hour <b
then c.CallOutsRate else 0 end) as CallOutsRate
from dbo.TSTA_EnteCallData c,
(select 0 a,1 b
union all select 1,2
union all select 2,3
union all select 3,4
union all select 4,5
union all select 5,6
union all select 6,7
union all select 7,8
union all select 8,9
union all select 9,10
union all select 10,11
union all select 11,12
union all select 12,13
union all select 13,14
union all select 14,15
union all select 15,16
union all select 16,17
union all select 17,18
union all select 18,19
union all select 19,20
union all select 20,21
union all select 21,22
union all select 22,23
union all select 23,24
) aa
where DATEPART(m,c.[Date]) =12 And DATEPART(yyyy,c.[Date])=2009
group by c.hour order by c.hour
select datename(weekday, c.[date]),
Sum(case when datename(weekday, c.[date])=a
then c.IVRTrans2Agt else 0 end) as IVRTrans2Agt,
Sum(case when datename(weekday, c.[date])=a
then c.TransSucCount else 0 end) as TransSucCount,
Sum(case when datename(weekday, c.[date])=a
then c.TransSucRate else 0 end) as TransSucRate,
Sum(case when datename(weekday, c.[date])=a
then c.TotalCallOuts else 0 end) as TotalCallOuts,
Sum(case when datename(weekday, c.[date])=a
then c.CallOutSucCnt else 0 end) as CallOutSucCnt,
Sum(case when datename(weekday, c.[date])=a
then c.CallOutsRate else 0 end) as CallOutsRate
from dbo.TSTA_EnteCallData c,
(select '星期日' a
union all select '星期一'
union all select '星期二'
union all select '星期三'
union all select '星期四'
union all select '星期五'
union all select '星期六'
) aa
where DATEPART(m,c.[Date]) =12 And DATEPART(yyyy,c.[Date])=2009
group by datename(weekday, c.[date])