交叉统计问题
第一张表:客户表
客户代码 客户名称
A XXX
B HHH
C JJJ
D PPP
E UYY
第二张表:操作流水表
id 起始时间 截止时间 客户代码
1 2007-05-06 02:00:00 2007-05-07 04:03:00 A
2 2007-05-06 11:00:00 2007-05-06 12:30:00 A
3 2007-05-06 12:00:00 2007-05-06 12:30:00 A
4 2007-05-07 12:00:00 2007-05-10 12:30:00 A
5 2007-05-06 12:00:00 2007-05-10 12:30:00 B
现在想达到如下统计效果 输入日期后输出所有客户按小时操作次数
比如输入 2007-05-06
输出
客户代码 0点 1点 2点 3点 4点..11点 12点 13点... 23点
A 0 0 1 1 1 2 3 1 1
B 0 0 0 0 0 0 1 0 C 0 0.................................. 0 0
D 0 0....... ............... 0
[解决办法]
try
Select
A.客户代码,
SUM(Case When 0 Between DatePart(hh, 起始时间) And DatePart(hh, 截止时间) Then 1 Else 0 End) As [0点],
SUM(Case When 1 Between DatePart(hh, 起始时间) And DatePart(hh, 截止时间) Then 1 Else 0 End) As [1点],
SUM(Case When 2 Between DatePart(hh, 起始时间) And DatePart(hh, 截止时间) Then 1 Else 0 End) As [2点],
SUM(Case When 3 Between DatePart(hh, 起始时间) And DatePart(hh, 截止时间) Then 1 Else 0 End) As [3点],
SUM(Case When 4 Between DatePart(hh, 起始时间) And DatePart(hh, 截止时间) Then 1 Else 0 End) As [4点],
SUM(Case When 5 Between DatePart(hh, 起始时间) And DatePart(hh, 截止时间) Then 1 Else 0 End) As [5点],
SUM(Case When 6 Between DatePart(hh, 起始时间) And DatePart(hh, 截止时间) Then 1 Else 0 End) As [6点],
SUM(Case When 7 Between DatePart(hh, 起始时间) And DatePart(hh, 截止时间) Then 1 Else 0 End) As [7点],
...
SUM(Case When 23 Between DatePart(hh, 起始时间) And DatePart(hh, 截止时间) Then 1 Else 0 End) As [23点]
From
客户表 A
Left Join
操作流水表 B
On A.客户代码 = B.客户代码
Group By
A.客户代码
[解决办法]
DECLARE
@查询日期 datetime
SELECT
@查询日期 = '2007-05-06 '
SELECT
A.客户代码,
[0点] = SUM(CASE WHEN 起始时间 <=0 AND 截止时间 > = 0 THEN 1 ELSE 0 END),
[1点] = SUM(CASE WHEN 起始时间 <=1 AND 截止时间 > = 1 THEN 1 ELSE 0 END),
[2点] = SUM(CASE WHEN 起始时间 <=2 AND 截止时间 > = 2 THEN 1 ELSE 0 END),
[3点] = SUM(CASE WHEN 起始时间 <=3 AND 截止时间 > = 3 THEN 1 ELSE 0 END),
....
[23点] = SUM(CASE WHEN 起始时间 <=23 AND 截止时间 > = 23 THEN 1 ELSE 0 END)
FROM(
SELECT
客户代码,
起始时间 = CASE
WHEN 起始时间 <= 起始时间 THEN 0
ELSE DATEPART(Hour, 起始时间) END,
截止时间 = CASE
WHEN 截止时间 > = DATEADD(Day, 1, @查询日期) THEN 23
ELSE DATEPART(Hour, 截止时间) END
FROM 操作流水表
WHERE 起始时间 <= DATEADD(Day, 1, @查询日期)
AND 截止时间 > = @查询日期
)A
--INNER JOIN 客户表 B
--ON A.客户代码 = B.客户代码
GROUP BY A.客户代码
[解决办法]
邹建:
起始时间 = CASE
WHEN 起始时间 <= 起始时间 THEN 0
ELSE DATEPART(Hour, 起始时间) END,
不对吧
