score:1

Accepted answer

Here's one approach. The query selects all rows from the table, and then uses outer apply to sum up the last value per device. The not exists query filters out rows that are not the last row for that device.

select  t1.timestamp
,       last_rows_per_device.Total
from    @t t1
outer apply
        (
        select  sum(t2.value) as Total
        from    @t t2
        where   (
                    t2.timestamp < t1.timestamp
                    or (t2.timestamp = t1.timestamp and t2.device <= t1.device)
                ) 
                and not exists
                (
                    select  *
                    from    @t t3
                    where   t3.device = t2.device
                            and t2.timestamp < t3.timestamp 
                            and t3.timestamp <= t1.timestamp
                )
        ) last_rows_per_device
order by
        t1.timestamp
,       t1.device

The query assumes that (timestamp, device) is unique, and it orders rows with the same timestamp by device id, lowest device first.

This matches your example output:

timestamp             Total
2010-12-30 00:00      5
2010-12-30 00:05      5
2010-12-30 00:05      15
2010-12-30 00:13      33
2010-12-30 00:16      44
2010-12-30 00:30      43
2010-12-30 00:40      77
2010-12-30 00:40      89
2010-12-30 00:45      46
2010-12-30 10:00      67

Source data:

declare @t table (timestamp datetime, device int, value int)
insert @t (timestamp, device, value)
          select '2010-12-30 00:00', 1, 5
union all select '2010-12-30 00:05', 1, 5
union all select '2010-12-30 00:05', 2, 10
union all select '2010-12-30 00:13', 1, 23
union all select '2010-12-30 00:16', 3, 11
union all select '2010-12-30 00:30', 1, 22
union all select '2010-12-30 00:40', 2, 55
union all select '2010-12-30 00:40', 3, 12
union all select '2010-12-30 00:45', 2, 12
union all select '2010-12-30 10:00', 3, 33

More questions

More questions with similar tag