CODEWITHSUNDEEP
sqlsql-serverdatetimeaverage
Azfar Kashif
Azfar Kashif

Reputation: 193

SQL Server DateTime Intervals

In a table, I have data coming in from sensors. The activity value is sent by a sensor every 15 minutes. Now, I want to calculate average of these values 2 times a day. As in, the 1st average of a day will be the average of values from 00:00:00 to 12:00:00 and the 2nd average for the same day will be between the time 12:00:00 and 23:59:59.

My TimeStampByNode column stores datetime in the format datetime2, i.e. "2015-09-02 02:03:04"

I used this query to get the daily average, i.e. of all 24 hours;

SELECT 
    DATEFROMPARTS(YEAR(TimeStampByNode), MONTH(TimeStampByNode), DAY(TimeStampByNode)), 
    ROUND(AVG(Value), 2) 
FROM 
    data_record2 
WHERE 
    Node_ID = 55554321 AND Value_Type = 'Activity'
GROUP BY 
    DATEFROMPARTS(YEAR(TimeStampByNode), MONTH(TimeStampByNode), DAY(TimeStampByNode))
ORDER BY  
    DATEFROMPARTS(YEAR(TimeStampByNode), MONTH(TimeStampByNode), DAY(TimeStampByNode)) DESC

How do I modify this to get 2 daily averages?

Upvotes: 0

Views: 174

Answers (2)

Jan
Jan

Reputation: 13858

How about doing two different averages per day? The NULL returned in ELSE cases makes sure those are not put into averages.

SELECT 
    DATEFROMPARTS(YEAR(TimeStampByNode), MONTH(TimeStampByNode), DAY(TimeStampByNode)), 
     -- Start --
    ROUND(AVG(CASE WHEN DATEPART(HH, TimeStampByNode) < 12 
          THEN Value ELSE NULL END), 2) as AVG_AM,
    ROUND(AVG(CASE WHEN DATEPART(HH, TimeStampByNode) >= 12 
          THEN Value ELSE NULL END), 2) as AVG_PM
    -- End --
FROM 
    data_record2 
WHERE 
    Node_ID = 55554321 AND Value_Type = 'Activity'
GROUP BY 
    DATEFROMPARTS(YEAR(TimeStampByNode), MONTH(TimeStampByNode), DAY(TimeStampByNode))
ORDER BY  
    DATEFROMPARTS(YEAR(TimeStampByNode), MONTH(TimeStampByNode), DAY(TimeStampByNode)) DESC

(Missing bracket edited)

Upvotes: 1

Andy
Andy

Reputation: 3743

You need to add the following condition to the GROUP BY clause:

 ...
GROUP BY (...), case when (DATEPART(hh, TimeStampByNode) < 12) then 1 else 0 end
 ...

So this will become:

SELECT 
    DATEFROMPARTS(YEAR(TimeStampByNode), MONTH(TimeStampByNode), DAY(TimeStampByNode)), 
    ROUND(AVG(Value), 2) 
FROM 
    data_record2 
WHERE 
    Node_ID = 55554321 AND Value_Type = 'Activity'
GROUP BY 
    DATEFROMPARTS(YEAR(TimeStampByNode), MONTH(TimeStampByNode), DAY(TimeStampByNode))
  , case when (DATEPART(hh, TimeStampByNode) < 12) then 1 else 0 end
ORDER BY  
            DATEFROMPARTS(YEAR(TimeStampByNode), MONTH(TimeStampByNode), DAY(TimeStampByNode)) DESC

Upvotes: 1

Related Questions