BerithBE BerithBE - 26 days ago 7
SQL Question

Count day SQL Server

I'm currently trying to solve a problem i have.

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


I need to write a SQL that return the count of days that an object past at STATE 2.
Example, the object A, stay from 04-02 to 10-02 at STATE 2 + 02-02 to 05-02 and from 10-11 to TODAY so 6 day + 3 day + 4 day.

SQL return 13


Currently make it by code but i need it in an SQL extract and i don't know how to proceed. Is that possible by SQL?

Thank you

Answer

I think you want lead() along with aggregation and date logic:

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;

Or, you can move the filtering condition to the outer 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;