Sum the timegap if consecutive timestamps are same for 10 min

Question

i am using the below query to find the null values of a column and get the starttime and endtime of the null values using the below query for some 30,000 rows

    SELECT
    yt1.[timestamp] AS StartTime,
    MIN(yt2.[timestamp]) AS EndTime,
    DATEDIFF(MINUTE, yt1.[timestamp], MIN(yt2.[timestamp])) AS DifferenceInMinutes
    FROM
    Sheet1$ yt1
    LEFT JOIN Sheet1$ yt2 ON yt1.[timestamp] < yt2.[timestamp]
    WHERE
    yt1.TWSPD IS NULL
    GROUP BY yt1.[timestamp]

The output is

Start time                     Endtime                DifferenceInMinutes
2012-05-18 20:47:03.000    2012-05-18 20:57:04.000      10
2012-05-18 20:57:04.000    2012-05-18 21:07:04.000      10
2012-05-21 18:25:26.000    2012-05-21 18:35:26.000      10
2012-06-07 17:36:28.000    2012-06-07 17:46:28.000      10
2012-06-07 17:46:28.000    2012-06-07 17:56:28.000      10
2012-06-07 17:56:28.000    2012-06-07 18:06:28.000      10

And for example now i need the output as (removed some row to display better)

Start time                     Endtime                DifferenceInMinutes
2012-05-18 20:47:03.000    2012-05-18 21:07:04.000      20
2012-05-21 18:25:26.000    2012-05-21 18:35:26.000      10
2012-06-07 17:36:28.000    2012-06-07 18:06:28.000      30

The timestamp is for every 10 min, if the null values for consecutive 10min timegap should be added and the starttime and endtime should be displayed as from the first null to last null of consecutive timestamp. Hope the question is clear. Please let me know if i am not clear. Please help

The sheet table contains some 30,000 rows with 20 columns and i need to retrive the null values for all the columns and get the timestamp and durations of those null values. Are you looking for any specific data? Please let me know — Reyaz, Dec 12 '12 at 08:11
Just wanted to see how a small collection of rows would look like with the 3 relevant columns (StartTime,EndTime,TWSPD) — Daniel PP Cabral, Dec 12 '12 at 08:18
`timestamp TWSPD 18-05-2012 18:08 6.019 18-05-2012 18:18 4.5786 18-05-2012 18:28 5.7103 18-05-2012 18:38 6.019 18-05-2012 18:48 18-05-2012 18:58 18-05-2012 19:08 18-05-2012 19:18 7.3051 18-05-2012 19:28 7.0993 18-05-2012 19:38 7.5109 18-05-2012 19:48 7.0993 18-05-2012 19:58 7.0993 18-05-2012 20:07 18-05-2012 20:17 18-05-2012 20:27 18-05-2012 20:37 ` — Reyaz, Dec 12 '12 at 08:52
The starttime and endtime i got it from original table of timestamp having syntax as 18-05-2012 18:08 for every 10 minutes, i need to get the null values of TWSPD and the timedifference between every row — Reyaz, Dec 12 '12 at 08:58

bummi · Accepted Answer · 2012-12-12T10:39:44.560

0

 SELECT
    yt1.[timestamp] AS StartTime,
    MIN(yt2.[timestamp]) AS EndTime,
    DATEDIFF(MINUTE, yt1.[timestamp], MIN(yt2.[timestamp])) AS DifferenceInMinutes
    into #tmp1
    FROM
    Sheet1$ yt1
    LEFT JOIN Sheet1$ yt2 ON yt1.[timestamp] < yt2.[timestamp]
    WHERE
    yt1.TWSPD IS NULL
    GROUP BY yt1.[timestamp]


Select t1.* 
into #tmp2
from #tmp1 t1
left join #tmp1 t2 on t1.Starttime=t2.Endtime
where t2.Endtime is null

Declare @rcn int
Select @rcn=1
While @rcn>0
    begin
       Update #tmp2 set #tmp2.Endtime=t.endTime,#tmp2.DifferenceInMinutes=#tmp2.DifferenceInMinutes+t.DifferenceInMinutes
       from #tmp1 t
       where t.Starttime=#tmp2.Endtime
       select @rcn=@@Rowcount
    end


select * from #tmp2

Drop Table #tmp1
Drop Table #tmp2

edited Dec 12 '12 at 10:39

answered Dec 12 '12 at 08:08

bummi

27,123
14
62
101

Thanks for the query. I am new to SQL, is it possible for you to give full query by joining the existing one and yours. You can find the existing one on the top i posted. Really appreciate your help. thanks – Reyaz Dec 12 '12 at 09:06
And my original table contains only timestamp – Reyaz Dec 12 '12 at 10:04

Roman Pekar · Answer 2 · 2012-12-12T08:25:31.640

If you want to query your original table to have grouped output - you can do this:

;with
CTE_start
as
(
    select T.timestamp, row_number() over(order by T.timestamp) as RowNum
    from temp1 as T
    where
        not exists 
        (
            select * 
            from temp1 as TT
            where TT.timestamp < T.timestamp and TT.timestamp >= dateadd(mi, -11, T.timestamp)
        )  
),
CTE_end
as
(
    select T.timestamp, row_number() over(order by T.timestamp) as RowNum
    from temp1 as T
    where
        not exists 
        (
            select * 
            from temp1 as TT
            where TT.timestamp > T.timestamp and TT.timestamp <= dateadd(mi, 11, T.timestamp)
        )  
)
select
    s.timestamp as [Start time],
    e.timestamp as [End time],
    datediff(mi, s.timestamp, e.timestamp) as [DifferenceInMinutes]
from CTE_start as s
    inner join CTE_end as e on e.RowNum = s.RowNum

SQL FIDDLE EXAMPLE

Another good one, but you have to copy your data into temporary (variable) table

declare @tmp table (timestamp datetime, RowNum int primary key)

insert into @tmp
select T.timestamp, row_number() over(order by T.timestamp) as RowNum
from temp1 as T

;with CTE
as
(
    select T.timestamp, T.RowNum, 1 as GroupNum
    from @tmp as T
    where RowNum = 1
    union all
    select
        T.timestamp, T.RowNum,
        C.GroupNum + case when datediff(mi, C.timestamp, T.timestamp) >= 11 then 1 else 0 end
    from @tmp as T
        inner join CTE as C on C.RowNum + 1 = T.RowNum
)
select
    min(C.timestamp) as [Start time],
    max(C.timestamp) as [End time],
    datediff(mi, min(C.timestamp), max(C.timestamp)) as [DifferenceInMinutes]
from CTE as C
group by C.GroupNum

SQL FIDDLE EXAMPLE

Sum the timegap if consecutive timestamps are same for 10 min

2 Answers2