SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

with cte1 as
(
select 1 as id,'2018-08-08 00:00:00.000' as d,'Active' as p
union all
select 1 as id,'2018-08-08 01:00:00.000' ,'Active'
union all
select 1 as id,'2018-08-08 02:00:00.000' ,'Inactive'
union all
select 2 as id,'2018-08-08 01:00:00.000' ,'InActive'
union all
select 2 as id,'2018-08-08 02:00:00.000' ,'Active'
)

select id,concat('Not Active between ', format(cast(prevd as datetime),' hh:mm '), 'hrs to ', format(cast(d as datetime),' hh:mm '), 'hrs') from
(select *,
LAG (d, 1, 0) OVER (PARTITION BY id ORDER BY d) prevd,
LAG (p, 1, 0) OVER (PARTITION BY id ORDER BY d) prevp
from cte1 
)a where prevp<>'0' and p<>prevp



id
(No column name)


1
Not Active between  01:00 hrs to  02:00 hrs

2
Not Active between  01:00 hrs to  02:00 hrs


select format(getdate(),'hh:mm')


(No column name)


08:51


/*select id, case when p<>prevp then 'Not Active between'+prevd+'to'+d end
as val
from
(
select *,
LAG (d, 1, 0) OVER (PARTITION BY id ORDER BY d) prevd,
LAG (p, 1, 0) OVER (PARTITION BY id ORDER BY d) prevp
from cte1
)X where prevp<>0*/

id	(No column name)
1	Not Active between 01:00 hrs to 02:00 hrs
2	Not Active between 01:00 hrs to 02:00 hrs