What is the best way to sum data over multiple date ranges in SQL?

user_1244630 0
Viewed 446

I have a table containing a user id, purchase amount, and date. I need to calculate the sum over several periods, for the same user. For example, given:

UserId Date Amount
1 2021-01-03 10
1 2021-01-10 20
1 2021-02-07 30

From an API, for the same user, I might need to sum and return all of the following ranges:

Description Range Expected Result
The first week of Jan 1st (Friday) to the 3rd (Sunday) 10
The month of Jan 1st to the 31st 30
The month of Feb 1st to the 28th 30

Some restrictions:

  1. I don't know the dates up front (the consumer either needs calendar periods, or rolling periods), so I don't think I can create curated queries using SUM and CASE? I saw an example of this here.
  2. I also need to use a stored procedure when interacting with the DB, so I wouldn't be able to dynamically create the SELECT statement from the API.

I don't have a lot of experiences writing SQL, but I imagine it's better to have a single query that calculates all of the totals than calculating the sums individually? Any suggestions?

sum sql-server
1 Answers

Thank you @Larnu, this seems to work, where Range is passed in as a table, and Purchases is the table listed in the question:

SELECT r.StartDate,
       r.EndDate,
       SUM(p.Amount) AS Total
FROM Range r
JOIN Purchases p ON p.Date >= r.StartDate
                AND p.Date < r.EndDate
GROUP BY r.StartDate, r.EndDate;

Which results in:

StartDate EndDate Total
2021-01-01 00:00:00.000 2021-01-04 00:00:00.000 10
2021-01-01 00:00:00.000 2021-02-01 00:00:00.000 30
2021-02-01 00:00:00.000 2021-03-01 00:00:00.000 30

Will this query perform reasonably well? Or is there perhaps a better way to do this?

user_12446300
Related