sql遍历结果/分组/排序 [英] sql loop through results / group by / sort
问题描述
这是我上一篇文章的后续问题
this is a follow on question from my previous post
我有以下代码将查询我们的代理在特定时间登录的数据库.它在给定的一天工作得很好,但我现在需要在选定的时间段内进行.我不知道从哪里开始调整这个!我需要按日期和小时分组的结果......所以它看起来如下
I have the following code which will query a database of when our agents were logged on at a particular time. It works nicely for a given day, but I need to now do it for a selected time period. I am not sure where to start to adjust this! I'd need the results grouped by date and hour... so it would look as follows
date hour count
10/10/11 22 52
10/10/11 23 24
11/10/11 00 12
11/10/11 01 33
因此将显示所选范围内每个日期的 24 小时周期.
So the 24 hour period would be displayed for each date within the selected range.
ALTER procedure [dbo].[LoggedOnCountByHour]
@DayToCheck datetime,
@HelplineID int
as
select dateadd(hour, N.number, @DayToCheck) as [date_hour],
DATEPART(hh,dateadd(hour, N.number, @DayToCheck)) as [Hour],
count(L.ExpertRecID) as [count of users]
from master..spt_values as N
left outer join WorkDetail as L
on L.KickedOffTime > dateadd(hour, N.number, @DayToCheck) and
L.LoginTime < dateadd(hour, N.number + 1, @DayToCheck)
left join PoolMembership P on P.ExpertRecID = L.ExpertRecID
where N.Type = 'P' and
N.Number between 0 and 23 and
P.HelplinePoolID = @HelplineID
group by dateadd(hour, N.number, @DayToCheck), DATEPART(hh,dateadd(hour, N.number, @DayToCheck) )
有什么想法!?非常感谢
any ideas!? Many thanks
推荐答案
类似这样的事情(未彻底测试)
Something like this (not thoroughly tested)
alter procedure [dbo].[LoggedOnCountByHour]
@FromDayToCheck datetime,
@ToDayToCheck datetime,
@HelplineID int
as
select dateadd(hour, N.number, @FromDayToCheck) as [date_hour],
DATEPART(hh,dateadd(hour, N.number, @FromDayToCheck)) as [Hour],
count(L.ExpertRecID) as [count of users]
from master..spt_values as N
left outer join WorkDetail as L
on L.KickedOffTime > dateadd(hour, N.number, @FromDayToCheck) and
L.LoginTime < dateadd(hour, N.number + 1, @FromDayToCheck)
left join PoolMembership P on P.ExpertRecID = L.ExpertRecID
where N.Type = 'P' and
dateadd(hour, N.number + 1, @FromDayToCheck) <= @ToDayToCheck + 1 and
P.HelplinePoolID = @HelplineID
group by dateadd(hour, N.number, @FromDayToCheck), DATEPART(hh,dateadd(hour, N.number, @FromDayToCheck) )
order by [date_hour], [Hour]
注意:如果您认为要检查的时间间隔超过 2048 小时,则应使用数字表而不是 master..spt_values.
Note: If you think that your time interval you are checking against is more than 2048 hours you should use a numbers table instead of master..spt_values.
这篇关于sql遍历结果/分组/排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!