This query will show you the first event for each sensor per device. It will output data so that you can do whatever you want with it.
IF OBJECT_ID(N'tempdb..#s1') IS NOT NULL
drop table #s1
select ROW_NUMBER() over (partition by '1' order by imeino, sensor ,timestamp) rid, * into #s1 from
(
select autoid, imeino , deviceid , timestamp, 's-1' sensor, [s-1] status from sensordata
union
select autoid, imeino , deviceid , timestamp, 's-2' sensor, [s-2] status from sensordata
union
select autoid, imeino , deviceid , timestamp, 's-3' sensor, [s-3] status from sensordata
union
select autoid, imeino , deviceid , timestamp, 's-4' sensor, [s-4] status from sensordata
) as flatted
where not imeino is null
;with a(rid, autoid, imeino, deviceid, timestamp, sensor, status, r) as
(
select * , RANK() over (partition by imeino, sensor, status order by rid) r from #s1
)
select * from a
where r = 1
I assumed that your table name is sensordata so change it to whatever it is.
If you can't convert it to stored procedure so you do not deserve to get a good score...
Hope it helps.
Submit an article or tip