CODEWITHSUNDEEP
sqlsql-server
BerithBE
BerithBE

Reputation: 37

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

Upvotes: 0

Views: 67

Answers (2)

Chanukya
Chanukya

Reputation: 5893

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;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270883

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;

Upvotes: 1

Related Questions