sql server - Show averages of a dataset, different date/time ranges -

db: ms sql server 11.0.3156.

i have table record periodic data values. key columns are: fldobjectguid (varchar), flddatatimestamp (datetime), fldconfigitem (varchar), fldconfigitemvalue (numeric)

i want retrieve data different time frame (day, week, month). keep number of returned data pints manageable number (e.g. less < 350), therefore, i'd averages.

for example:

• day - return data (already got this!)
• week - return data in hourly average values (e.g. there 24 * 1 hour averages, * 7 days)
• month - return data in 3-hourly average values (e.g. 8 * average on 3 hours, * 30)
• yearly - return data in daily average values (e.g. 1 * average on 24 hours, * 365)

a small example of data set shown here:

    +--------------------------------------------------------------------------------+ + fldobjectguid | fldrecordupdatedtimestamp | fldconfigitem     | fldconfigitemvalue | + 40010000      | 2015-06-16 18:20:48.000   | icmpresponsetime  |   4.00             | + 40010000      | 2015-06-16 19:22:00.000   | icmpresponsetime  |   15.00            | + 40010000      | 2015-06-16 20:22:14.000   | icmpresponsetime  |   4.00             | + 40010000      | 2015-06-17 17:35:19.000   | icmpresponsetime  |   6.00             | + 40010000      | 2015-06-17 18:36:26.000   | icmpresponsetime  |   4.00             | + 40010000      | 2015-06-28 02:18:31.000   | icmpresponsetime  |   19.00            | + 40010000      | 2015-06-28 03:18:54.000   | icmpresponsetime  |   9.00             | + 40010000      | 2015-06-02 17:25:16.000   | icmpresponsetime  |   3.00             | +------------------------------------------------------------------------------------+ 

data added object (fldobjectguid) @ different rates. 1 row every 5 minutes or 1 one row every hour. there can gaps in data (hours or days). want graph fldconfigitemvalue data each object on different time frames; day (last 24 hours), week, month , year. periods of returned data don't need exact. so, month last 30 days, or 1 calendar month today's date.

the sql needs return data single fldobjectguid , fldconfigitem combination - i'll amend sql @ run-time data required object/configitem.

there may gaps in data, no data points within given period. so, return value can zero.

i'm retrieving data using classic asp, creating sql statement , parsing results. achieve result programatically in asp code. 'week' required set, make repeated calls db, using average function, , clause retrieve subset of records (now - 1 hour). store value, repeat using clause (now - 1 hour - 2 hours). , step in time until i've got values week. 'month' , 'yearly' routines same, different timeframes in clauses.

however, me, seems clumsy way of doing , 1 sql routine (or different sql routine week, month , year) must quicker , / or more elegant.

at moment, have sql (from stackoverflow?) thought might work , have code build sql 'month' view (i've hard-coded fldobjectguid , fldconfigitem in example, make example clearer):

select      top 30 convert(date, l.flddatatimestamp) 'flddatatimestamp_result', l.fldconfigitemvalue, l.fldconfigitemvalue        tblobjecthealthcheckdata_historic l             inner join  (             select    min(flddatatimestamp) first_timestamp                 tblobjecthealthcheckdata_historic             fldobjectguid = '10050400' , fldconfigitem = 'availableram'             group convert(date, flddatatimestamp)             ) sub_l on (sub_l.first_timestamp = l.flddatatimestamp) fldobjectguid = '10050400' , l.fldconfigitem = 'availableram' order flddatatimestamp desc   

but gets first data point each day (as can guess, whilst understand sql , programming, hobby, not living) , i'm struggling fix code.

i'm assuming people agree, it's more efficient doing in code making many separate sql calls - can help?

i try datepart function, way can different parts of fldrecordupdatedtimestamp , avg field fldconfigitemvalue.

this goes down single hour of timestamp (could minutes, check msdn datepart in t-sql), if wish daily averages per week need include:

day_fldrecordupdatedtimestamp week_fldrecordupdatedtimestamp 

this average each day inside each week.

below example shows average per month - mind year, if have more years worth of data make sure include year_fldrecordupdatedtimestamp etc.

with partstable  (     select         fldobjectguid         , fldrecordupdatedtimestamp         , fldconfigitem         , fldconfigitemvalue         , datepart(hour, fldrecordupdatedtimestamp) hour_fldrecordupdatedtimestamp         , datepart(day, fldrecordupdatedtimestamp) day_fldrecordupdatedtimestamp         , datepart(week, fldrecordupdatedtimestamp) week_fldrecordupdatedtimestamp         , datepart(month, fldrecordupdatedtimestamp) month_fldrecordupdatedtimestamp         , datepart(year, fldrecordupdatedtimestamp) year_fldrecordupdatedtimestamp             yourlogtable     --where     -- perhaps set limit here not huge set in first step.     ) select     count(1) setcount /* shows how many rows in each avg calculation. */     , fldobjectguid     , fldconfigitem     , month_fldrecordupdatedtimestamp /* change column specific span you're intrested in. */     , avg(fldconfigitemvalue) avg_fldconfigitemvalue     partstable group     fldobjectguid     , fldconfigitem     , month_fldrecordupdatedtimestamp /* change column specific span you're intrested in. */ ; 

one final note: make sure include month_, week_ etc. column in both select , group by.