CODEWITHSUNDEEP
phpsql-servert-sql
Chris G.
Chris G.

Reputation: 3981

MSSQL Aggregated time query with multiple columns

In this example, I am collecting some engine data on a car.

Variables
--------------------------------------
id  | name
--------------------------------------
1     Headlights On 
2     Tire Pressure
3     Speed
4     Engine Runtime in Seconds
...


Values
--------------------------------------
id  |  var_id  | value  | time
--------------------------------------
1      1          1       2013-05-28 16:42:00.100
2      1          0       2013-05-28 16:42:22.150
3      2          32.0    2013-05-28 16:42:22.153
4      3          65      2013-05-28 16:42:22.155
...

I want to write a query that returns a result set something like the following:

Input: 1,2,3


Time                | Headlights On | Tire Pressure | Speed
---------------------------------------------------------------
2013-05-28 16:42:00   1                                  
2013-05-28 16:42:22   0               32              65

Being able to modify the query to include only results for a given set of variables and at a specified interval say (1 second, 1 minute or 5 minutes) are also really important for my use case.

How do you write a query in T-SQL that will return a time-aggregated multi column result set at a specific interval?

Upvotes: 0

Views: 311

Answers (2)

Greg Kramida
Greg Kramida

Reputation: 4234

1 minute aggregate: 

SELECT {edit: aggregate functions over fields here} FROM Values WHERE {blah} GROUP BY DATEPART (minute, time);

5 minute aggregate: 

SELECT {edit: aggregate functions over fields here} FROM Values WHERE {blah} GROUP BY
  DATEPART(YEAR, time),
  DATEPART(MONTH, time),
  DATEPART(DAY, time),
  DATEPART(HOUR, time),
  (DATEPART(MINUTE, time) / 5);

For the reason this latter part is so convoluded, please see the SO post here: How to group time by hour or by 10 minutes .

Edit 1: For the part "include only results for a given set of variables", my interpretation is that you want to to isolate Values with var_id being within a specified set. If you can rely on the variable numbers/meanings not changing, the common SQL solution is the IN keyword (http://msdn.microsoft.com/en-us/library/ms177682.aspx).

This is what you would put into the WHERE clause above, e.g.

... WHERE var_id IN (2, 4) ...

If you can't rely on knowing the variable numbers but are certain about their names, you can replace the set by a sub-query, e.g.:

... WHERE var_id IN (SELECT id FROM Variables WHERE name IN ('Tire Pressure','Headlights On')) ...

The alternative interpretation is that you actually want to aggregate based on the variable ids as well. In this case, you'll have to include the var_id in your GROUP BY clause.

To make the results more crosstab-like, I guess you'll want to order by time aggregate that you're using. Hope that helps more.

Upvotes: 1

Ian P
Ian P

Reputation: 1724

Try

SELECT
    VehicleID
    , Case WHEN Name = 'Headlights on' THEN 1
     Else 0 END ' as [Headlights on]
    , Case WHEN Name = 'Tyre pressure' THEN Value
     Else CAST( NULL AS REAL) END ' as [Tyre pressure]
 , DateName(Year, DateField) [year ]
FROM
   Table

ETC

Then agrregate as required

SELECT
      VehicleID
      , SUM([Headlights on]) SUM([Headlights on],
FROM 
(
QUery above

) S
GROUP BY
VehicleID
, [Year]

Upvotes: 1

Related Questions