SQL - 按时间间隔拆分总时间 [英] SQL - Split total time by time intervals

问题描述

我有一个看起来像这样的条目:

I have an entry that looks like this:

UserID---StatusStart---StatusEnd---StatusKey---StateDuration
Joe1------8:59:46--------9:08:06-------可用-----500

UserID---StatusStart---StatusEnd---StatusKey---StateDuration
Joe1------8:59:46--------9:08:06-------Available-----500

我需要做的是将其拆分为两个条目.一个显示从 8:59:46 到 9:00:00 的状态以及从 9:00:00 到 9:08:06 结束的第二个

What I need to do is split this into two entries. One that shows the stateduration from 8:59:46 to 9:00:00 and a second that's from 9:00:00 until the end of 9:08:06

最终，我希望能够将一些东西应用到一个巨大的信息表中，这样我就可以了解代理每半小时花费的时间和时间.我想太多了，我很肯定我是，因为我每次都把自己转成一张网——一定有一些简单的事情我忘记了.

Ultimately I'm hoping to be able to have something that I can apply to a huge table of this information so I can break out what and how much time is spent each half hour for the agents. I'm overthinking this, I'm positive I am because I spin myself into a web each time - there has to be something simple I'm forgetting.

此外，理想情况下，无论它是什么公式，如果时间跨越多个间隔，它都会起作用.比如StatusStart是8:59:46，而StatusEnd是10:08:06.

Also, ideally whatever formula it is, it would work if the time spanned over multiple intervals. Such as the StatusStart being 8:59:46 but the StatusEnd being 10:08:06.

推荐答案

老派方法，但我会创建第二个表:

Old school approach, but I would create a second table:

CREATE TABLE SplitTimes (
    SplitStart time not null,
    SplitEnd time not null,
    primary key (SplitStart, SplitEnd)
)

并填充它:

INSERT INTO SplitTimes (SplitStart, SplitEnd) VALUES
('0:00', '0:30'),
('0:30', '1:00'),
('1:00', '1:30'),
('1:30', '2:00'),
('2:00', '2:30'),
('2:30', '3:00'),
('3:00', '3:30'),
('3:30', '4:00'),
('4:00', '4:30'),
('4:30', '5:00'),
('5:00', '5:30'),
('5:30', '6:00'),
('6:00', '6:30'),
('6:30', '7:00'),
('7:00', '7:30'),
('7:30', '8:00'),
('8:00', '8:30'),
('8:30', '9:00'),
('9:00', '9:30'),
('9:30', '10:00'),
('10:00', '10:30'),
('10:30', '11:00'),
('11:00', '11:30'),
('11:30', '12:00'),
('12:00', '12:30'),
('12:30', '13:00'),
('13:00', '13:30'),
('13:30', '14:00'),
('14:00', '14:30'),
('14:30', '15:00'),
('15:00', '15:30'),
('15:30', '16:00'),
('16:00', '16:30'),
('16:30', '17:00'),
('17:00', '17:30'),
('17:30', '18:00'),
('18:00', '18:30'),
('18:30', '19:00'),
('19:00', '19:30'),
('19:30', '20:00'),
('20:00', '20:30'),
('20:30', '21:00'),
('21:00', '21:30'),
('21:30', '22:00'),
('22:00', '22:30'),
('22:30', '23:00'),
('23:00', '23:30'),
('23:30', '23:59:59.9999999');

现在我可以运行这个:

SELECT e.UserID
    ,case when e.StatusStart >= t.SplitStart then e.StatusStart else t.SplitStart end as SplitStatusStart
    ,case when e.StatusEnd   <= t.SplitEnd   then e.StatusEnd   else t.SplitEnd   end as SplitStatusEnd
    ,e.StatusKey
    ,datediff(second, case when e.StatusStart >= t.SplitStart then e.StatusStart else t.SplitStart end, 
        case when e.StatusEnd   <= t.SplitEnd   then e.StatusEnd   else t.SplitEnd   end) SplitStatusDuration
FROM EntryTable e
INNER JOIN SplitTimes t
    ON  e.StatusStart <= t.SplitEnd
    AND e.StatusEnd >= t.SplitStart

它可以使用日期时间而不是时间，并且可以在午夜工作.从日期中删除时间并重新添加另一个时间只是额外的摆弄.

It can be made to work with datetimes instead of times and it can be made to work across midnight. It's just extra fiddling to strip the time from the date and add the other one back on.

这具有不是递归 CTE 的优点，它可能在大表上表现更好.

This has the advantage of not being a recursive CTE, which may perform better on a large table.

这篇关于SQL - 按时间间隔拆分总时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！