select
LogTime = Convert(char(10),LogTime,111),
UPPER(LTRIM(ClientUserName)),
COMPANY,
DEPT_ID,
[NAME],
ProcessingTime =sum( ProcessingTime),
H_00 = sum(Case datepart(Hour,LogTime)
when 0 then ProcessingTime else 0 end),
H_01 = sum(Case datepart(Hour,LogTime)
when 1 then ProcessingTime else 0 end),
H_02 = sum(Case datepart(Hour,LogTime)
when 2 then ProcessingTime else 0 end),
H_03 = sum(Case datepart(Hour,LogTime)
when 3 then ProcessingTime else 0 end),
H_04 = sum(Case datepart(Hour,LogTime)
when 4 then ProcessingTime else 0 end),
H_05 = sum(Case datepart(Hour,LogTime)
when 5 then ProcessingTime else 0 end),
H_06 = sum(Case datepart(Hour,LogTime)
when 6 then ProcessingTime else 0 end),
H_07 = sum(Case datepart(Hour,LogTime)
when 7 then ProcessingTime else 0 end),
H_08 = sum(Case datepart(Hour,LogTime)
when 8 then ProcessingTime else 0 end),
H_09 = sum(Case datepart(Hour,LogTime)
when 9 then ProcessingTime else 0 end),
H_10 = sum(Case datepart(Hour,LogTime)
when 10 then ProcessingTime else 0 end),
H_11 = sum(Case datepart(Hour,LogTime)
when 11 then ProcessingTime else 0 end),
H_12 = sum(Case datepart(Hour,LogTime)
when 12 then ProcessingTime else 0 end),
H_13 = sum(Case datepart(Hour,LogTime)
when 13 then ProcessingTime else 0 end),
H_14 = sum(Case datepart(Hour,LogTime)
when 14 then ProcessingTime else 0 end),
H_15 = sum(Case datepart(Hour,LogTime)
when 15 then ProcessingTime else 0 end),
H_16 = sum(Case datepart(Hour,LogTime)
when 16 then ProcessingTime else 0 end),
H_17 = sum(Case datepart(Hour,LogTime)
when 17 then ProcessingTime else 0 end),
H_18 = sum(Case datepart(Hour,LogTime)
when 18 then ProcessingTime else 0 end),
H_19 = sum(Case datepart(Hour,LogTime)
when 19 then ProcessingTime else 0 end),
H_20 = sum(Case datepart(Hour,LogTime)
when 20 then ProcessingTime else 0 end),
H_21 = sum(Case datepart(Hour,LogTime)
when 21 then ProcessingTime else 0 end),
H_22 = sum(Case datepart(Hour,LogTime)
when 22 then ProcessingTime else 0 end),
H_23 = sum(Case datepart(Hour,LogTime)
when 23 then ProcessingTime else 0 end)
from webproxylog A,MSP_USER B
where --Convert(char(10),LogTime,111) > @VAR_DATE ---AND (ClientUserName LIKE 'UNIMICRON%')
--and
ClientUserName <> 'anonymous' and a.ClientUserName=b.NETWORK_ACC
group by Convert(char(10),LogTime,111),UPPER(LTRIM(ClientUserName)),COMPANY,DEPT_ID,NAME;
group by 的 sum 求和
最新推荐文章于 2022-02-21 09:40:23 发布