In the current row, sum the data for rows that represent records for last week (7 days)

Question

I have the SQL table BaseData (values in value column are random):

------------------------------------------------------
|    Date    |    Value    |  Day  |  Moth  |  Year  |
------------------------------------------------------
| 01-07-2020 |      1      |   1   |    7   |  2020  |
| 02-07-2020 |      2      |   2   |    7   |  2020  |
| 03-07-2020 |      3      |   3   |    7   |  2020  |
| 04-07-2020 |      4      |   4   |    7   |  2020  |
| 05-07-2020 |      5      |   5   |    7   |  2020  |
| 06-07-2020 |      6      |   6   |    7   |  2020  |
| 07-07-2020 |      7      |   7   |    7   |  2020  |
| 08-07-2020 |      8      |   8   |    7   |  2020  |
| 09-07-2020 |      9      |   9   |    7   |  2020  |
| 10-07-2020 |      10     |   10  |    7   |  2020  |
------------------------------------------------------

What I need is, to query data from the BaseData in a way that for each row, I will have the SUM of the Values from the last 7 days (not including the current day).

For example, for July 8th we should have the SUM of Values between July 1st and July 7th.

For example, this should be my result table for the given example from above:

----------------------------
|    Date    |  ResultSum  | 
----------------------------
| 01-07-2020 |      0      | //assuming this is the first record
| 02-07-2020 |      1      | //SUM = 1
| 03-07-2020 |      3      | //SUM = 1 + 2
| 04-07-2020 |      6      | //SUM = 1 + 2 + 3
| 05-07-2020 |      10     | //SUM = 1 + 2 + 3 + 4
| 06-07-2020 |      15     | //SUM = 1 + 2 + 3 + 4 + 5
| 07-07-2020 |      21     | //SUM = 1 + 2 + 3 + 4 + 5 + 6
| 08-07-2020 |      28     | //SUM = 1 + 2 + 3 + 4 + 5 + 6 + 7
| 09-07-2020 |      35     | //SUM = 2 + 3 + 4 + 5 + 6 + 7 + 8
| 10-07-2020 |      42     | //SUM = 3 + 4 + 5 + 6 + 7 + 8 + 9
----------------------------

What I have so far is:

SELECT Date,
       (SELECT SUM(nestedTable.Value)
        FROM BaseData AS nestedTable
        WHERE DATEADD(dd, DATEDIFF(dd, 0, Date), 0) BETWEEN
              DATEADD(day, DATEDIFF(day, 0, DATEADD(d,-1, Date)), 0) AND
              DATEADD(day, DATEDIFF(day, 0, DATEADD(d,-8, Date)), 0)
       ) AS ResultSum
FROM BaseData

But seems it's not working. I found some similar question here, but I am not able to figure out how can I use those answers here.

Upgrade to a supported version of SQL Server, and this is trivial, as you have access to the `ROWS BETWEEN` in the `OVER` clause. Considering SQL Server 2008 has been completely unsupported for a year, you *should* already have those upgrade plans under way too. — Thom A, Jul 16 '20 at 09:21
Please [edit] your question with [proper sample data](https://stackoverflow.com/tags/sql/info). — Zohar Peled, Jul 16 '20 at 09:24
@ZoharPeled tabular formatted text is "fine", as it's at least consumable with small effort. It's far better than the images we constantly see. — Thom A, Jul 16 '20 at 09:25
@Larnu I wouldn't lower my standards that far to say it's "fine". It's arguably half-decent at best, but you are correct, I also feel like question quality is going down the drain lately (and btw, so is the easy hand on the voting buttons. My best-voted answer is a pretty new one that's nothing more than a documentation quote - I've written much better answers that got 0 points unfortunately...) — Zohar Peled, Jul 16 '20 at 09:28
@delux [sqlfiddle](http://sqlfiddle.com/) has a nifty little button called "Text to DDL". Give it a try. — Zohar Peled, Jul 16 '20 at 09:43

GMB · Accepted Answer · 2020-07-16T09:40:04.930

3

Just use window functions. Assuming that dates are consecutive in the table, you would do:

select
    date,
    coalesce(
        sum(value) over(order by date rows between 7 preceding and 1 preceding),
        0
    ) resultsum
from basedata

In versions of SQL Server that do not support the rows clause to window functions, you can use a correlated subquery or a lateral join instead:

select
    date,
    (
        select coalesce(sum(b1.value), 0)
        from basedata b1
        where b1.date between dateadd(day, -7, b.date) and dateadd(day, -1, b.date)
    ) resultsum
from basedata b

This query would take advantage of an index on the date column - and it has the advantage of handling non-consecutive dates.

Both queries assume that dates are stored as datatype date (or the-like) - if that's not the case, you need to cast() them first (or better yet... fix your data model!).

edited Jul 16 '20 at 09:40

answered Jul 16 '20 at 09:22

GMB

216,147
25
84
135

The OP has, unfortunately, tagged SQL server 2008. `ROWS BETWEEN` is only available in supported versions of SQL Server, as it was introduced with SQL Server 2012. – Thom A Jul 16 '20 at 09:23
That's assuming dates are consecutive in the table.... – Zohar Peled Jul 16 '20 at 09:23
@Larnu: ah yes, did not spot that in the first place. Answer update with a correlated subquery instead. – GMB Jul 16 '20 at 09:27
@ZoharPeled: indeed - OP's sample data is not showing otherwise though. – GMB Jul 16 '20 at 09:28
True, @GMB, but you do (ironically) handle this logic in the triangular join. :) – Thom A Jul 16 '20 at 09:31
You're also assuming `date` data type (which seems to contradict the code in the question). You might want to use `cast(b.[date] as date)`... – Zohar Peled Jul 16 '20 at 09:36
@ZoharPeled: thanks, I updated my answer to mention that. – GMB Jul 16 '20 at 09:41
I would suggest it's "safe" to assume a date value is a Date and Time data type, @ZoharPeled. If it isn't then that's a design flaw by the OP and they should be fixing it at source. – Thom A Jul 16 '20 at 09:41

In the current row, sum the data for rows that represent records for last week (7 days)

1 Answers1