CODEWITHSUNDEEP
sqlsql-server
reborn
reborn

Reputation: 55

SQL Server query: get number of working hours from 1 column

Please help how to get only the working hours from each row on date changed column with the same ID CODE and deduct idle time. All inputs are dynamic and can be changed based on what user keyed in.

For example:

and so on..

ID  PROJ NAME   STATUS              DATE CHANGED
----------------------------------------------------
1800    abc     COMPLETED           10/1/2017 14:48
1800    abc     IN PROGRESS         10/1/2017 13:02
1800    abc     ON-HOLD             10/1/2017 11:55
1800    abc     IN PROGRESS         10/1/2017 8:07
1800    abc     ON-HOLD             9/1/2017 18:29
1800    abc     IN PROGRESS         9/1/2017 15:58
1800    abc     PENDING DATA        9/1/2017 13:52
1800    abc     IN PROGRESS         9/1/2017 13:04
1800    abc     ON-HOLD             9/1/2017 11:53
1800    abc     IN PROGRESS         9/1/2017 8:37
1800    abc     ON-HOLD             9/1/2017 8:03
1800    abc     START               9/1/2017 6:03

screenshot

Thank you.

Upvotes: 1

Views: 149

Answers (1)

Amin Mozhgani
Amin Mozhgani

Reputation: 604

Check this code, I selected each item that is In Progress and I found it's next datetime, by subtracting these two datetimes, I found all progress times and by summarizing these values, we find total active time:

declare @s table  (ID int,  PROJ_NAME nvarchar(max),   STATUS nvarchar(max),             DATE_CHANGED datetime)
insert into @s (ID,PROJ_NAME,[status],DATE_CHANGED)
values
(1800   ,'abc',    'COMPLETED'          ,'10/1/2017 14:48'),
(1800    ,'abc',     'IN PROGRESS'         ,'10/1/2017 13:02'),
(1800    ,'abc',     'ON-HOLD'             ,'10/1/2017 11:55'),
(1800    ,'abc',     'IN PROGRESS'         ,'10/1/2017 8:07'),
(1800    ,'abc',     'ON-HOLD'             ,'9/1/2017 18:29'),
(1800    ,'abc',     'IN PROGRESS'         ,'9/1/2017 15:58'),
(1800    ,'abc',     'PENDING DATA'        ,'9/1/2017 13:52'),
(1800    ,'abc',     'IN PROGRESS'        ,'9/1/2017 13:04'),
(1800    ,'abc',     'ON-HOLD'             ,'9/1/2017 11:53'),
(1800    ,'abc',     'IN PROGRESS'         ,'9/1/2017 8:37'),
(1800    ,'abc',     'ON-HOLD'             ,'9/1/2017 8:03'),
(1800    ,'abc',     'START'               ,'9/1/2017 6:03')

select sum(datediff(second,date_changed,lead)) from (
select date_changed,LEAD(date_changed,1,0) over (order by date_changed
) as lead,[status] from @s 
) D
where D.STATUS = 'IN PROGRESS'

Upvotes: 3

Related Questions