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

Question

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:

START to ONHOLD-> working time: 2hr
ON-HOLD to In Progress -> idle time: 34mins
In Progress to On-hold -> working time: 3hrs & 16mins

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.

how is the state in progress > pending data > in progress calculated? — Salman A, Oct 10 '18 at 08:23
See the answer [here](https://stackoverflow.com/questions/710212/is-there-a-way-to-access-the-previous-row-value-in-a-select-statement) on how to select previous and current rows. See also `LAG` and `LEAD` — Peter Smith, Oct 10 '18 at 08:30

score 3 · Accepted Answer · answered Oct 10 '18 at 08:27

3

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'

answered Oct 10 '18 at 08:27

Amin Mozhgani

604
1
7
22

Hi thanks for you reply. the "lead" your saying is the on hold time? – reborn Oct 10 '18 at 09:14
@reborn lead offers to the next record, based on order that we defined, so I referred to immediately record after our In Progress one. – Amin Mozhgani Oct 10 '18 at 09:21
when i run the code it's not working, lead name is not recognized. any thoughts? – reborn Oct 10 '18 at 09:25
What's your SQL version? – Amin Mozhgani Oct 10 '18 at 09:26
its version 17.6 – reborn Oct 10 '18 at 09:29
It's your SSMS version, Whats your SQL server engine version? (2012,2014,...) – Amin Mozhgani Oct 10 '18 at 09:35
hi i figured out its working on sql 2017 and my application is running on windows 2008 r2 that's why the code is not working. – reborn Oct 10 '18 at 09:46
LEAD and LAG works on SQL servers greater or equal to 2012(11.x) https://learn.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-2017 – Amin Mozhgani Oct 10 '18 at 09:47
Hi what is the correct way to put group by ID, if i have multiple data? im not getting the right value if if put group by Id after where clause. – reborn Oct 15 '18 at 09:25
Group by which field? – Amin Mozhgani Oct 15 '18 at 10:13
`select sum(datediff(second,date_changed,lead)),id from ( select id,date_changed,LEAD(date_changed,1,0) over (partition by id order by date_changed ) as lead,[status] from @s ) D where D.STATUS = 'IN PROGRESS' group by id` – Amin Mozhgani Oct 15 '18 at 12:12

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

1 Answers1