SQL Server在不同行之间查找datediff，求和 [英] SQL Server find datediff between different rows, sum

问题描述

我正在尝试建立一个查询，以分析我们的时间跟踪系统中的数据。每次用户向内或向外滑动时，都会记录记录滑动时间以及打开或关闭站点（进入或退出）。在用户 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.

问题是难以配对的记录。在给定的示例中，第二个用户有两个连续的 on，我需要找到一种方法来忽略重复的 on或 off行。

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 SQL2005。查询的报告时间是每月。

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

这篇关于SQL Server在不同行之间查找datediff，求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！