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
- Summary calculation for different times
- How to get Age calculation for different years
- Joining multiple times on the same table for different criteria vs. once on aggregated table
- SQL query between date/time with different fileds for date and times
- SQL Server 2008 : TSQL, select same data for different times based on column value
- Sql server gives different result for the same mathematical calculation
- SQL: Different calculation for each line based on a calculation saved in the table
- How to run NUnit onetimesetup multiple times for different databases
- RAND not different for every row in T-SQL UPDATE
- Create SQL Server Login for IIS APPPOOL - IIS and SQL Server on different machines
- Get difference between two times for SQL Server 2012
- Sql Order By ... using `Case When` for different Ascending, Descending, and Custom Orders
- SQL Two Different WHERE Conditions for Two Columns
- Compiled query fails - Query was compiled for a different mapping source than the one associated with the specified DataContext
- How to give access to SQL Server database mail procedures for a Login in a different database?
- What is best practice for this problem (different properties for different categories)?
- SQL Server Login for IIS APPPOOL on different machines - same domain
- Why checksum() returns the same value for different string
- Retrieve different number of rows for each category from SQL Server table
- Two different update triggers for the same table
- Why does the same Linq-to-SQL query consume much more CPU time on the database server for a different project?
- How to update a column via Row_Number with a different value for each row?
- SELECT during a lengthy UPDATE - What happens to SELECT for different Transaction Isolation Levels and SELECT WITH (NOLOCK)?
- select different Max ID's for different customer
- Summary of disk usage for all databases using SSMS
- how do pivot for number of punch Times in a day in sql server 2005?
- group by for summary data
- Concatenate column values for rows with the same values (of different columns)
- Very different execution times of SQL query in C# and SQL Server Management Studio
- Using MySQL & MSSQL for two different databases with Entity Framework
More questions with similar tag
- Implement rollback in Nested stored procedure
- SQL Filter date range query
- Hibernate : MS SQL 2012 Strange Behavior with hbm2ddl.auto=update
- MDX permission error when I call a table in SQL Server instance
- Combining 3 tables into one table
- select bottom rows in natural order
- How to calculate the number of rows using a time based sliding window?
- Can Microsoft's SQLSRV driver for PHP run on Apache?
- How to read a `geography` column by `SqlDataReader`?
- How to handle database exceptions/ issue?
- sql query where date between two times
- SSIS Lookup Multiple Columns in one table to the same ID column in another
- Difference between Inner Join VS Inner Remote Join
- Can I debug a Stored Procedure in SQL Server Express Edition with Advanced Services?
- Returning most recent row SQL Server
- How to subtract two column values , one from a table and other from the stored procedure
- Creating a correlation matrix in SQL Server
- SQL Query to fetch the records with success status
- How to find more than 1 uppercase character
- SQL Query: Get rows where value of certain column is maximum
- textbox value from client side in code behind?
- How can I assign long expressions inside SELECT?
- sql question about select - trivial?
- Sum between 3 linked tables SQL Server
- How can get the stored procedure execution logs?
- Split pipe lined string into Rows - Split the empty values also in SQL Server
- Export Image column from SQL Server 2000 using BCP
- Autoincrement of Primary Key in SQL Server database
- CTE SQL query get full paths
- How to show CLOB type in a SELECT in SQL Server?
Source:
stackoverflow.com