0

GIVENS:

  • Tools: SQL Server, SSMS 2016, R
  • Data: Hourly samples starting 2017-12-31 23:00:00 thru 2021-02-05 08:00:00

WANT: To chunk data into 7-day blocks ideally coinciding with week of year and grab averages for each 7-day period. Willing to sacrifice some data frontend and/or backend. Would like to reduce data frequency from 12x365 points down to perhaps 52 points per year. For end use in R.

PROBLEM(S): A) SQL datepart(week,...) method does not consider 1st seven days of 2018 as week 1. Considers that week starts on a certain day of week, not necessarily on Jan. 1.

B) I suspect SQL datepart(week,...) will assign repeating week value across several years of data. So if I group by datepart(week...), won't it combine week 1 of 2018, 2019, 2020, 2021?

Here's my starting query (AvgDate is for debug purposes):

SELECT datepart(week,Date) Week,
    FORMAT(AVG(HeadElev), '###.###') as AvgHeadEl,
    COUNT(HeadElev) as Count,
    FORMAT(AVG(datepart(Day, Date)), '##.###') as AvgDate
  FROM [dbo].[Chickamauga] as CWL
  WHERE '20171231' < Date AND Date <= '20181231' 
  GROUP BY datepart(week,Date)
  ORDER BY Week
GO

Here's what my table looks like (I've split date & time from original data):

CREATE TABLE [dbo].[SomeLake](
    [Date] [date] NULL,
    [HourCT] [time](0) NULL,
    [HeadElev] [float] NULL,
    [TailElev] [float] NULL,
    [Flow] [float] NULL
) ON [PRIMARY]

Again, trying to create simple 7-day blocks of samples and grab averages. (Not moving averages, I only want 1 data point per 7-day block.) I'm trying to reduce the data frequency from (hourly down to weekly data.)

End goal is to import into R and used time series functions that cannot accept high per year frequencies like 365. Trying to bring the frequency down to 52, ie. weekly data.)

THANK YOU for your kind assistance!

Thom A
  • 88,727
  • 11
  • 45
  • 75
DaveFer
  • 53
  • 1
  • 7
  • 365 Mod 7 = 2, so you will always have a problem. What do you *want* to happen with those extra days? – Charlieface Feb 16 '21 at 21:42
  • Thanks, Charlieface, for lucid comment. I'm starting to see from other posts, that R will handle a non-integer frequency for time series data and there are libraries available that will convert the start date to a decimal data. It looks like I will create the 7-day blocks in SQL and then let R handle the odd annual frequency and start dates. https://stackoverflow.com/questions/22188660/r-time-series-modeling-on-weekly-data-using-ts-object – DaveFer Feb 16 '21 at 22:11

1 Answers1

0

create simple 7-day blocks of samples and grab averages.

Group by something like:

1+datepart(dy,some_date)/7 week

which takes the day-of-year and performs integer division to group them into 7-day buckets, starting with 0.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67