CODEWITHSUNDEEP
sqlsql-server-2008
Reyaz
Reyaz

Reputation: 55

Find the time difference between rows

I have some 30000 rows in the table with the timestamp for every 10 mins and with some 20 columns.

timestamp  Col1 Col2 Col3 ... col20

timestamp would be like every 10 mins. ex: 18-05-2012 20:07, 18-05-2012 20:17, 18-05-2012 20:27

Columns have integer or float data.

My question is: I need to find the gaps and time duration which the data has been not recorded in the columns. Suppose the column has Value, NULL, NULL, Value, Value, NULL. And it has the respective timestamps.

I need the output like as starttime and endtime of NULL values and duration (starttime - end time).

Upvotes: 0

Views: 313

Answers (2)

fancyPants
fancyPants

Reputation: 51928

Okay, as I understood it now, you want the timedifference between the rows where in any column something was not recorded, right?

Then have a try with this:

SELECT
yt1.[timestamp] AS StartTime,
MIN(yt2.[timestamp]) AS EndTime,
DATEDIFF(s, yt1.[timestamp], MIN(yt2.[timestamp])) AS DifferenceInSeconds
FROM
yourTable yt1
LEFT JOIN yt2 ON yt1.[timestamp] < yt2.[timestamp]
WHERE
yt1.Col1 IS NULL
OR yt1.Col2 IS NULL
...
OR yt1.Col20 IS NULL
OR yt2.Col1 IS NULL
...
OR yt2.Col20 IS NULL 
GROUP BY yt1.[timestamp]

To be honest, I'm not sure if this works, since you didn't provide sample data to play around a bit.

To get the time difference in hh:mm:ss format search questions on stackoverflow, for example this one.

Upvotes: 1

merletta
merletta

Reputation: 444

I think you should use 2 left joins: to detect the beginning of NULL appearance, in common my answer looks much like previous answer: 

select distinct  max(table2.dt) as startTime, min(table3.dt) as endTime,  
datediff(minute, max(table2.dt), min(table3.dt)) as timeDifference
from Table_2 table1
left join Table_2 table2 on table2.dt < table1.dt 
left join Table_2 table3 on table3.dt > table1.dt
where (table3.tt1 is not NULL and table3.tt2 is not NULL)
  and (table2.tt1 is not NULL and table2.tt2 is not NULL)
  and (table1.tt1 is NULL or table1.tt2 is NULL)
  group by table1.dt, table1.id

Upvotes: 0

Related Questions