CODEWITHSUNDEEP
sql-server
tkhan17
tkhan17

Reputation: 75

Get daily data from hourly

I have query which returns hourly data. But I want to get daily data from this query, so all the hourly data per day would be averaged to daily data.

declare @Days int

set @Days = -1

select      

dateadd(hour,datepart(hour,Timestamp),cast(CAST((Timestamp) as date) as datetime)) as [Time]  
,[value] 



from  [Employee]

where dateadd(hour,datepart(hour,Timestamp),cast(CAST((Timestamp) as date) as datetime)) >= CONVERT(date, DATEADD(DAY, @Days, GETDATE()))

Upvotes: 0

Views: 1107

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

Assuming you have additional columns you want to average and group by date, you can try something like:

DECLARE @Days int = -1;
SELECT 
      CAST(Timestamp AS date) AS date
    , AVG(Value) AS Value
FROM  [Employee]
WHERE Timestamp >= DATEADD(day, @Days, CAST(GETDATE() AS date))
GROUP BY CAST(Timestamp AS date)
ORDER BY date;

Note the refactored WHERE clause that avoids applying a function to the column value. This will allow an index on Timestamp to be used efficiently (sargable expression).

Upvotes: 1

Related Questions