-1

I want to calculate moving average with a datetime over a 7 days period, the dataset is in 10 timestep.Im running sql-server 2008. The data is like this:

+-------------------------+--------+--+
|          DATE           | Checks |  |
+-------------------------+--------+--+
| 2018-10-03 16:00:00.000 |     5  |  |
| 2018-10-03 16:10:00.000 |      8 |  |
+-------------------------+--------+--+

And I want to forecast how many checks will be a threshold today at a given time. Any ideas? Thank you.

Salman A
  • 262,204
  • 82
  • 430
  • 521
ib1
  • 11
  • 4
  • 3
    And what is your expected output ? – Ullas Oct 05 '18 at 10:07
  • Could you explain more infomation about your question? – D-Shih Oct 05 '18 at 10:07
  • SQL Server 2008 is no longer supported. The earliest supported version *for now* is SQL Server 2014. Moving averages are easy to implement with the windowing functions introduced in SQL Server 2012. Are you *sure* the target server will be 2008? – Panagiotis Kanavos Oct 05 '18 at 10:09
  • You say over 7 days, but there's only 10 minutes worth of data there. – Thom A Oct 05 '18 at 10:09
  • Sample data is best served as [DDL](https://en.wikipedia.org/wiki/Data_definition_language) + [DML](https://en.wikipedia.org/wiki/Data_manipulation_language). Please [edit] your question to include it, your current attempt and your desired results. For more details, [read this.](https://dba.meta.stackexchange.com/questions/2976/help-me-write-this-query-in-sql) – Zohar Peled Oct 05 '18 at 10:11
  • @PanagiotisKanavos yes unfortunately it is. – ib1 Oct 05 '18 at 10:11
  • In SQL Server 2012 and later you could write : `select date,sum(chk) OVER (PARTITION BY DATE ROWS between current and 7 preceding) FROM (select cast(date as date) as date,sum(checks) as chk from thatTable group by cast(date as date)) dailies` – Panagiotis Kanavos Oct 05 '18 at 10:11
  • @PanagiotisKanavos wasn't `sum() over(...)` supported also in 2008? – Zohar Peled Oct 05 '18 at 10:12
  • @Larnu so it is for 7 days and every day is divided by 10 minutes so basically its (7 days) / (10 minutes) = 1008 records. – ib1 Oct 05 '18 at 10:13
  • @ZoharPeled *2008* isn't supported – Panagiotis Kanavos Oct 05 '18 at 10:13
  • @Wanderer edited the post. Sorry. – ib1 Oct 05 '18 at 10:14
  • @PanagiotisKanavos Yes, I'm aware of that... however I'll bet you know that some projects are legacy projects and some customers will simply not upgrade anything... Up until not so long ago I've seen questions about 2000 version. – Zohar Peled Oct 05 '18 at 10:15
  • @PanagiotisKanavos Thank you but it has to be done in Sql Server 2008. – ib1 Oct 05 '18 at 10:16
  • Possible duplicate of [T-SQL calculate moving average](https://stackoverflow.com/questions/26618353/t-sql-calculate-moving-average) – Panagiotis Kanavos Oct 05 '18 at 10:19
  • @ib1 check the duplicate. Seems the `OVER` clause is supported in 2008 *R2*. The top voted answer though covers 2008 with a CTE – Panagiotis Kanavos Oct 05 '18 at 10:20
  • @Zohar the `OVER` clause was added in SQL Server 2008 yes. The `ROWS` operator was added in SQL Server 2012. *(Also, 2008 isn't completely unsupported, it's still in extended support, which is why things like SSMS 2018 still support it. That'll change soon though, as it's completely out of Extended support early next year)*. – Thom A Oct 05 '18 at 10:24
  • @Larnu seems like you are correct, as [official documentation states.](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017) Good to know I still remember a thing or two :-) – Zohar Peled Oct 05 '18 at 10:29
  • @ZoharPeled i'm still suffering SQL Server 2008 at one of the smaller companies, so I was pretty confident it worked in 2008 as otherwise a lot of my SQL wouldn't work. :) Can't wait to decommission that later this year (mind, it's only going to a 2012 instance we have until the 2017/2019 budget is signed off (hopefully) next year). – Thom A Oct 05 '18 at 10:36

1 Answers1

0

Try this code once.

Select dateadd(wk, datediff(wk, 0, [DATE]), 0),SUM(Checks ) 
FROM testtable
GROUP BY dateadd(wk, datediff(wk, 0, [DATE]), 0)
Raju Bandaram
  • 3
  • 6