CODEWITHSUNDEEP
sql-server
user3832146
user3832146

Reputation: 5

Days between status change in SQL Server

I need find the number of days between status change in SQL Server 2014.

For example, please see the data below

+--------+--------+------------+-------------+
| status | Number | updated_on | opened_at   |
+--------+--------+------------+-------------+
| Draft  | 100    | 2017-11-03 | 2017-11-03  |
| Draft  | 100    | 2017-12-12 | 2017-11-03  |
| WIP    | 100    | 2017-12-12 | 2017-11-03  |
| Appr   | 100    | 2018-01-05 | 2017-11-03  |
| Launch | 100    | 2018-01-10 | 2017-11-03  |
| Close  | 100    | 2018-01-11 | 2017-11-03  |
+--------+--------+------------+-------------+

Based on the above input, I need to get

Draft --- 40 days,
WIP --- 23 days,
appro -- 5 days,
deploy/launch - 1 days,
closed --- 69 days

Please help me with SQL query to arrive this results.

Thanks.

Upvotes: 0

Views: 409

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I don't think your numbers are right. But this should do what you want, assuming that the statuses are unique:

select status,
       datediff(day, updated_on, lead(updated_on) over (order by updated_on) ) as days
from t;

I don't understand the first and last numbers, though.

Upvotes: 1

Prabhath Amaradasa
Prabhath Amaradasa

Reputation: 503

Try this

SELECT
tb.status,
DATEDIFF(dayofyear, tb.opened_at, tb.LastUpdate) AS  DaysInDifference
FROM
(
SELECT 
DISTINCT
status,
Max(updated_on) OVER(PARTITION BY [status]  )LastUpdate,
opened_at
FROM Table1
)AS tb

Upvotes: 0

Related Questions