SQL Server查找不同行之间的日期差异，总和
我正在尝试构建一个查询来分析我们的时间跟踪系统中的数据.每次用户刷入或刷出时，它都会连续记录刷卡时间和 On 或 Off 站点(进入或退出).在用户Joe Bloggs"的情况下，有 4 行，我想配对并计算 Joe Bloggs 在网站上花费的总时间.
I am trying to build a query that analyzes data in our time tracking system. Every time a user swipes in or out, it makes a row recording the swipe time and On or Off site (entry or exit). In user 'Joe Bloggs' case there are 4 rows, which I want to pair and calculate a total time spent on site for Joe Bloggs.
问题是有些记录不容易配对.在给出的示例中，第二个用户有两个连续的开"，我需要找到一种方法来忽略重复的开"或关"行.
The problem is that there are records that are not as easy to pair. In the example given, the second user has two consecutive 'on's, and I need to find a method for ignoring repeated 'on' or 'off' rows.
ID | Time |OnOffSite| UserName
------------------------------------------------------
123 | 2011-10-25 09:00:00.000 | on | Bloggs Joe |
124 | 2011-10-25 12:00:00.000 | off | Bloggs Joe |
125 | 2011-10-25 13:00:00.000 | on | Bloggs Joe |
126 | 2011-10-25 17:00:00.000 | off | Bloggs Joe |
127 | 2011-10-25 09:00:00.000 | on | Jonesy Ian |
128 | 2011-10-25 10:00:00.000 | on | Jonesy Ian |
129 | 2011-10-25 11:00:00.000 | off | Jonesy Ian |
130 | 2011-10-25 12:00:00.000 | on | Jonesy Ian |
131 | 2011-10-25 15:00:00.000 | off | Jonesy Ian |
我的系统是 MS SQL 2005.查询的报告期是每月.
My System is MS SQL 2005. The reporting period for the query is Monthly.
谁能提出解决方案?我的数据已经按用户名和时间分组在一个表中，ID 字段是身份.
Can anyone suggest a solution? my data is already grouped in a table by Username and time, with the ID field being Identity.
推荐答案
-- =====================
-- sample data
-- =====================
declare @t table
(
ID int,
Time datetime,
OnOffSite varchar(3),
UserName varchar(50)
)
insert into @t values(123, '2011-10-25 09:00:00.000', 'on', 'Bloggs Joe')
insert into @t values(124, '2011-10-25 12:00:00.000', 'off', 'Bloggs Joe')
insert into @t values(125, '2011-10-25 13:00:00.000', 'on', 'Bloggs Joe')
insert into @t values(126, '2011-10-25 17:00:00.000', 'off', 'Bloggs Joe')
insert into @t values(127, '2011-10-25 09:00:00.000', 'on', 'Jonesy Ian')
insert into @t values(128, '2011-10-25 10:00:00.000', 'on', 'Jonesy Ian')
insert into @t values(129, '2011-10-25 11:00:00.000', 'off', 'Jonesy Ian')
insert into @t values(130, '2011-10-25 12:00:00.000', 'on', 'Jonesy Ian')
insert into @t values(131, '2011-10-25 15:00:00.000', 'off', 'Jonesy Ian')
-- =====================
-- solution
-- =====================
select
UserName, timeon, timeoff, diffinhours = DATEDIFF(hh, timeon, timeoff)
from
(
select
UserName,
timeon = max(case when k = 2 and OnOffSite = 'on' then Time end),
timeoff = max(case when k = 1 and OnOffSite = 'off' then Time end)
from
(
select
ID,
UserName,
OnOffSite,
Time,
rn = ROW_NUMBER() over(partition by username order by id)
from
(
select
ID,
UserName,
OnOffSite,
Time,
rn2 = case OnOffSite
-- '(..order by id)' takes earliest 'on' in the sequence of 'on's
-- to take the latest use '(...order by id desc)'
when 'on' then
ROW_NUMBER() over(partition by UserName, OnOffSite, rn1 order by id)
-- '(... order by id desc)' takes the latest 'off' in the sequence of 'off's
-- to take the earliest use '(...order by id)'
when 'off' then
ROW_NUMBER() over(partition by UserName, OnOffSite, rn1 order by id desc)
end,
rn1
from
(
select
*,
rn1 = ROW_NUMBER() over(partition by username order by id) +
ROW_NUMBER() over(partition by username, onoffsite order by id desc)
from @t
) t
) t
where rn2 = 1
) t1
cross join
(
select k = 1 union select k = 2
) t2
group by UserName, rn + k
) t
where timeon is not null or timeoff is not null
order by username
相关文章