计算行内和行外的时间差

whlutmcx 于 2022-10-03 发布在其他

关注(0)|答案(1)|浏览(148)

我有一个表，其中我将进出时间存储为单独的行，我正在寻找一种方法来选择进出时间作为两列，这样我就可以根据进出计算总时间。所以我有这样一张table

Time                          TimeType
2022-04-04 09:13:19.000         IN
2022-04-04 09:20:54.000         OUT
2022-04-04 09:21:54.000         IN
2022-04-04 09:25:54.000         OUT
2022-04-04 09:26:54.000         IN
2022-04-04 09:28:54.000         IN

我想选择并显示为：

inTime                          outTime                   timeSpent
2022-04-04 09:13:19.000         2022-04-04 09:20:54.000    7
2022-04-04 09:21:54.000         2022-04-04 09:25:54.000    4
2022-04-04 09:26:54.000         NULL                       0

空值表示错误，因此任何空值都应忽略。如果有任何方法可以如上所述选择它们，请让我知道。谢谢

我试着这样做：

SELECT (SELECT Times AS inTime FROM Table WHERE Times>='2022-09-29 00:00:00' AND Times<'2022-09-29 23:59:59' AND timeType='IN' AND personID='1'),
(SELECT Times AS outTime FROM Table WHERE Times>='2022-09-29 00:00:00' AND Times<'2022-09-29 23:59:59' AND timeType='OUT' AND personID='1')

但是上面的游戏是错误的，因为我不能选择多行。

然后我试着像这样加入：

SELECT A.times AS inTime, B.times AS outTime FROM Table A
INNER JOIN Table B ON A.personID=B.personID
WHERE A.Times>='2022-04-04 00:00:00' AND A.Times<'2022-04-04 23:59:59' AND A.timeType='IN' AND A.personID='1' AND B.Times>='2022-04-04 00:00:00' AND B.Times<'2022-04-04 23:59:59' AND B.timeType='IN' AND B.personID='1'

上面的连接是重复的intime和outtime

sql-server

来源：https://stackoverflow.com/questions/73904464/calculating-time-difference-between-in-row-and-out-row

1条答案

按热度按时间

mspsb9vt1#

因此，您只需利用LEAD analytic function来检查下一行中的‘TimeType’，看看它是否是有效行(即，OUT跟随IN)，如果它有效，则使用LEAD从下一行获取Time值，然后计算差值。最后，只筛选出行中的行。

declare @MyTable table ([Time] datetime2(3), TimeType varchar(3));

insert into @MyTable ([Time], TimeType)
values
('2022-04-03 09:13:19.000', 'IN'),
('2022-04-04 09:20:54.000', 'OUT'),
('2022-04-04 09:21:54.000', 'IN'),
('2022-04-04 09:25:54.000', 'OUT'),
('2022-04-04 09:26:54.000', 'IN'),
('2022-04-04 09:28:54.000', 'IN');

with cte as (
    select *
        , lead(TimeType) over (order by [Time] asc) LeadTimeType
        , lead([Time]) over (order by [Time] asc) OutTime
    from @MyTable
)
select [Time] InTime, OutTime
    , dateadd(second, datediff(second, [Time], OutTime), convert(time(3),'00:00:00.000')) TimeSpent
from cte
-- Either the IN row has a matching OUT row, or its the last 'IN' row
where (LeadTimeType != TimeType or LeadTimeType is null) and TimeType = 'IN'
order by [Time] asc;

返回：

Intime|OutTime|TimeSpent
-|-|
2022-04-03 09：13：19.000|2022-04-04 09：20：54.000|00：07：35.000
2022-04-04 09：21：54.000|2022-04-04 09：25：54.000|00：04：00.000
2022-04-04 09：28：54.000|空|空

请注意，将样例数据添加为DDL+DML(如下所示)将使人们更容易提供帮助。

赞(0）回复(0）举报 2022-10-03

我来回答

计算行内和行外的时间差

1条答案

相关问题

热门标签

最新问答