SQL Server的：每个项目有多少天是在每个国家(SQL Server : how many d

由于存储每个版本的每个项目的表。

例如：

+--------+----------+---------------+--------+---------------------+
| 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 |
+--------+----------+---------------+--------+---------------------+

我需要计算每个项目有多少天是在每个州（除“关闭”）。

结果应该是这样的：

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

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

使用游标（尤其是嵌套游标）是一种不好的做法。 而且是非常缓慢的。

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

我没有看到任何其他方式来计算的话不反复的所有记录和比较状态。

有没有什么解决办法吗？

提前致谢。

Answer 1:

这给你你问同样的结果，在一个稍微不同的格式（但你可以很容易地找到PIVOT的解决方案，如果你需要完全相同的结果集）：

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从你的问题列，而不是我构建Joined ，因为真正重要的是，当 一个状态生效。

---

没有处理，因为你已经不是在你的问题中描述他们的问题：1）做什么，如果目前的最终状态不是Closed -即我们忽略，或计数直到今天？2）什么，如果做一天中的每个时间DateChanged是不一样的-我们必须处理的部分天？

Answer 2:

我个人比较喜欢从[Damien_The_Unbeliever]的CTE，我需要更经常地使用它们。 使用内部连接我基本上做的是同一件事的加枢包装周围的结果，让你在找什么：（更换@t为您的真实表名）

SELECT ItemId , [New],[Active]
FROM
(
SELECT 
    ItemId , LASTSTATE,  DATEDIFF(D, LASTDATE, DateChanged) AS D 
FROM 
    @T AS T 
    INNER JOIN 
        (SELECT 
            ItemId as ItemLink, 
            Revision + 1 AS RevLink , 
            DateChanged AS LASTDATE , 
            State AS LASTSTATE from @t
        ) AS L ON T.ItemId = L.ItemLink AND T.Revision = L.RevLink
) AS P PIVOT ( SUM(D) FOR LASTSTATE IN ([New],[Active],[Closed])) AS DATA

文章来源: SQL Server : how many days each item was in each state