SQL Server：每个项目处于每种状态的天数 [英] SQL Server : how many days each item was in each state

问题描述

给出一个表，该表存储每个项目的每个修订。

Given a table that stores every revision for every item.

例如：

+--------+----------+---------------+--------+---------------------+
| ItemId | Revision | PreviousState | State  |     DateChanged     |
+--------+----------+---------------+--------+---------------------+
|      1 |        1 | NULL          | New    | 2014-11-13 10:00:00 |
|      1 |        2 | New           | Active | 2014-11-15 10:00:00 |
|      1 |        3 | Active        | New    | 2014-11-17 10:00:00 |
|      1 |        4 | New           | Active | 2014-11-19 10:00:00 |
|      1 |        5 | New           | Active | 2014-11-20 10:00:00 |
|      1 |        6 | Active        | Closed | 2014-11-22 10:00:00 |
|      2 |        1 | NULL          | New    | 2014-11-13 10:00:00 |
|      2 |        2 | New           | Active | 2014-11-16 10:00:00 |
|      2 |        3 | Active        | Closed | 2014-11-17 10:00:00 |
|      2 |        4 | Closed        | Active | 2014-11-19 10:00:00 |
|      2 |        5 | Active        | Closed | 2014-11-21 10:00:00 |
+--------+----------+---------------+--------+---------------------+

我需要计算每个项目在每种状态下的天数（关闭除外）。

I need to calculate how many days each item was in each state (except 'Close').

结果应如下所示：

+--------+-----+--------+
| ItemId | New | Active |
+--------+-----+--------+
|      1 |   4 |      5 |
|      2 |   3 |      3 |
+--------+-----+--------+

我尝试使用两种方法- GROUP BY 和嵌套游标。

I tried to use two approaches - GROUP BY and nested cursors.

使用游标（尤其是嵌套游标）是一种不良做法。

Using cursors (especially nested cursors) is a bad practice. And the are very slow.

GROUP BY 也不会起作用，因为没有严格的状态顺序（新建->有效->已关闭）。可能是混乱的新建->活动->封闭->活动->封闭->新建->封闭。

GROUP BY also won't work because there is no strict order of states (New -> Active -> Closed). It could be chaotic New -> Active -> Closed -> Active -> Closed -> New -> Closed.

我没有其他计算方法

有什么解决方案吗？

谢谢。

推荐答案

这会以稍有不同的格式为您提供所需的结果（但您可以轻松找到 PIVOT 解决方案，如果您需要完全相同的结果集）：

This gives you the same results you're asking for, in a slightly different format (but you can easily find PIVOT solutions if you need the exact same result set):

declare @t table (ItemId int,Revision int,State varchar(19),DateChanged datetime2)
insert into @t(ItemId,Revision,State,DateChanged) values
(1,1,'New',   '2014-11-13T10:00:00'),
(1,2,'Active','2014-11-15T10:00:00'),
(1,3,'New',   '2014-11-17T10:00:00'),
(1,4,'Active','2014-11-19T10:00:00'),
(1,5,'Active','2014-11-20T10:00:00'),
(1,6,'Closed','2014-11-22T10:00:00'),
(2,1,'New',   '2014-11-13T10:00:00'),
(2,2,'Active','2014-11-16T10:00:00'),
(2,3,'Closed','2014-11-17T10:00:00'),
(2,4,'Active','2014-11-19T10:00:00'),
(2,5,'Closed','2014-11-21T10:00:00')

;With Joined as (
    select t1.ItemId,t1.State,DATEDIFF(day,t1.DateChanged,t2.DateChanged) as Days
    from
        @t t1
            inner join
        @t t2
            on
                t1.ItemId = t2.ItemId and
                t1.Revision = t2.Revision -1
    )
select ItemId,State,SUM(Days)
from Joined
where State <> 'Closed'
group by ItemId,State

结果：

ItemId      State               
----------- ------------------- -----------
1           Active              5
1           New                 4
2           Active              3
2           New                 3

请注意，我忽略了问题中的 PreviousState 列，而是在构建加入是因为真正重要的是下一个状态生效时的。

Note that I'm ignoring the PreviousState column from your question and am instead constructing Joined because what really matters is when the next state came into effect.

问题未处理，因为您没有在问题中描述它们：1）如果当前最终状态不是已关闭-例如，我们是否忽略它，还是算到今天？，以及2）如果每个 DateChanged 的一天中的时间都不是，该怎么办？是不一样的-我们必须处理部分工作日吗？

Issues not dealt with because you've not described them in your question: 1) What to do if the current final state isn't Closed - i.e. do we ignore that, or count until today?, and 2) What to do if the time of day for each DateChanged isn't the same - do we have to handle partial days?

这篇关于SQL Server：每个项目处于每种状态的天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！