So I will try to explain what we are looking for... please ask questions if it doesn't make sense
We are looking for the correct SQL to get QoS-data of ping, which is the value 0 or 1 (down/up).
What we want is to have an output that generates the time when the first value '0' arrives, and then the first value '1'. And then we will calculate the amount of minutes in between.
This is what we have to generate all the samples from last month. Samplevalue will be 0 or 1 (Ping).
Next thing we want is to get the first row with samplevalue = '0', and then first following samplevalue = '1'.
So the output should only be two rows.
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
Next thing would be to get more rows, if there would be more samples during the last month that had samplevalue = 0, under a different time span.