CODEWITHSUNDEEP
sql-servergroup-by
Mark
Mark

Reputation: 55

Summary calculation for different times

I have the following example for which I'd like to calculate totals at each available time:

timestamp  device value
2010-12-30 00:00 1 5
2010-12-30 00:05 1 5
2010-12-30 00:05 2 10
2010-12-30 00:13 1 23
2010-12-30 00:16 3 11
2010-12-30 00:30 1 22
2010-12-30 00:40 2 55
2010-12-30 00:40 3 12
2010-12-30 00:45 2 12
2010-12-30 10:00 3 33

At the end the result should look like this:

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 88
2010-12-30 00:40 89
2010-12-30 00:45 46
2010-12-30 10:00 67

The idea is that for each timestamp I need to get the last value for each device and then SUM up the values.

For example:

For timestamp 2010-12-30 00:13 I'd need to take the following entries and SUM them:

2010-12-30 00:05 2 10
2010-12-30 00:13 1 23

The total would then be 26. For timestamp 2010-12-30 10:00 I'd need to take the following lines:

2010-12-30 00:30 1 22
2010-12-30 00:45 2 12
2010-12-30 10:00 3 33

Which gives 67 as the total.

My idea was to create a query which would select latest values for each device for each timestamp, but I'm stuck already at selecting the latest values for each device for a specific timestamp, so I'd kindly ask for some support.

Upvotes: 1

Views: 307

Answers (1)

Andomar
Andomar

Reputation: 238196

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

Upvotes: 1

Related Questions