计数日SQL Server

Question

我目前正试图解决我的一个问题。

   | ID | Op | Object | STATE | Timestamp |
   | 01 | 1  | A      |   1   | 01-02-2016|
   | 02 | 1  | A      |   2   | 04-02-2016|
   | 03 | 1  | A      |   1   | 10-02-2016|
   | 04 | 1  | A      |   3   | 01-02-2016|
   | 05 | 2  | A      |   2   | 02-02-2016|
   | 06 | 3  | A      |   1   | 05-02-2016|
   | 07 | 3  | A      |   2   | 10-11-2016|

我需要写一个SQL，返回一个对象在状态2通过的天数。例如，对象A，停留在状态2+ 02-02到05-02，以及从10-11到今天6天+3天+4天。

  SQL return 13

目前使用的是代码，但我需要在SQL解压缩中使用它，我不知道如何继续。SQL有可能做到这一点吗？

谢谢

Answer 1

我认为您需要lead()以及聚合和日期逻辑：

select object,
       sum(case when state = 2 then datediff(day, timestamp, coalesce(next_timestamp, getdate()) )
                else 0
           end) as days_state_2
from (select t.*,
             lead(timestamp) over (partition by object order by timestamp) as next_timestamp
      from t
     ) t
group by object;

或者，您可以将筛选条件移动到外部select。

select object,
       sum(datediff(day, timestamp, coalesce(next_timestamp, getdate()) )) as days_state_2
from (select t.*,
             lead(timestamp) over (partition by object order by timestamp) as next_timestamp
      from t
     ) t
where state = 2
group by object;

Answer 2

select object,
       sum(datediff(day, timestamp, coalesce(next_timestamp, getdate()) )) as days_state_2
from (select *,
             lead(timestamp) over (partition by object order by timestamp) as next_timestamp
      from #b
     ) t
where state = 2
group by object;