Hi rith
I'm afraid that I only just saw this post - I hope this might still be useful.
Assuming you are on at least SQL Server 2012, you can use the LAG() analysis function to get things to work as you wish. I haven't tested this for performance on a fully populated UIM instance, but have used similar code in Oracle data warehouses many times.
with my_qos_data as (
SELECT source, s.samplevalue, s.sampletime from [NimsoftSLM].[dbo].[S_QOS_DATA] d
join [NimsoftSLM].[dbo].[RN_QOS_DATA_0021] s
on d.table_id = s.table_id
and target = 'PING-SLA'
and source = 'TEST01'
and s.samplevalue > 0
and s.sampletime > DATEADD(month, -1, GETDATE())
order by s.sampletime asc
/*
select 'TEST01' as source, '2018-05-01 01:00' as sampletime, 1 as samplevalue
union select 'TEST01' as source, '2018-05-01 02:00' as sampletime, 1 as samplevalue
union select 'TEST01' as source, '2018-05-01 03:00' as sampletime, 1 as samplevalue
union select 'TEST01' as source, '2018-05-01 04:00' as sampletime, 0 as samplevalue
union select 'TEST01' as source, '2018-05-01 05:00' as sampletime, 0 as samplevalue
union select 'TEST01' as source, '2018-05-01 06:00' as sampletime, 0 as samplevalue
union select 'TEST01' as source, '2018-05-01 07:00' as sampletime, 0 as samplevalue
union select 'TEST01' as source, '2018-05-01 08:00' as sampletime, 0 as samplevalue
union select 'TEST01' as source, '2018-05-01 09:00' as sampletime, 1 as samplevalue
union select 'TEST01' as source, '2018-05-01 10:00' as sampletime, 1 as samplevalue
union select 'TEST01' as source, '2018-05-01 11:00' as sampletime, 1 as samplevalue
union select 'TEST01' as source, '2018-05-01 12:00' as sampletime, 1 as samplevalue
*/
)
, state_change as (
select
source
, sampletime
, samplevalue
, samplevalue - (lag(samplevalue,1) over (partition by source order by sampletime)) as statechange
from my_qos_data
)
select *
from state_change
where statechange <> 0
;
Looking at the query above:
- Firstly, it retains your original query as the first sub-select (my_qos_data) - you can ignore the commented out code, that was just to get something to work with to check the syntax was correct.
- Next, it creates the second sub-select (state_change) uses the LAG() function to compare each row with the previous one (partitioned by source, ordered by sampletime) to determine any ‘state change' in the QOS value
- Finally, it returns only those rows where a ‘state change' was detected.
So, using the sample data in the /* commented code */, the output is:
Hope this helps you, and others, get closer to what you were looking for.