CODEWITHSUNDEEP
sqloracle-databasedynamicaggregation
wpolowniak
wpolowniak

Reputation: 3

SQL - dynamic sum based on dynamic date range

I'm new to SQL and I'm not even sure if what I am trying to achieve is possible.

I have two tables. The first gives an account number, a 'from' date and a 'to' date. The second table shows monthly volume for each account.

Table 1 - Dates
Account#  Date_from   Date_to
--------  ---------   -------
123       2018-01-01  2018-12-10
456       2018-06-01  2018-12-10
789       2018-04-23  2018-11-01

Table 2 - Monthly_Volume
Account#   Date         Volume
---------  ----------   ------
123        2017-12-01   5
123        2018-01-15   5
123        2018-02-05   5
456        2018-01-01   10
456        2018-10-01   15
789        2017-06-01   5
789        2018-01-15   10
789        2018-06-20   7

I would like to merge the two tables in such a way that each account in Table 1 has a fourth column that gives the sum of Volume between Date_from and Date_to.

Desired Result:
Account#  Date_from   Date_to     Sum(Volume)
--------  ---------   -------     -----------
123       2018-01-01  2018-12-10  10
456       2018-06-01  2018-12-10  15
789       2018-04-23  2018-11-01  7

I believe that this would be possible to achieve for each account individually by doing something like the following and joining the result to the Dates table:

SELECT
    Account#, 
    SUM(Volume)
FROM Monthly_Volume
WHERE 
    Account# = '123'
    AND Date_from >= TO_DATE('2018-01-01', 'YYYY-MM-DD')
    AND Date_to <= TO_DATE('2018-12-10', 'YYYY-MM-DD') 
GROUP BY Account#

What I'd like to know is whether it is possible to achieve this without having to individually fill in the Account#, Date_from and Date_to for each account (there are ~1,000 accounts), but have it be done automatically for each entry in the Dates table.

Thank you!

Upvotes: 0

Views: 756

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

You should be able to use join and group by:

select d.account#, d.Date_from, d.Date_to, sum(mv.volume)
from dates d left join
     monthly_volume mv
     on mv.account# = d.account# and
        mv.date between d.Date_from and d.Date_to
group by d.account#, d.Date_from, d.Date_to;

Upvotes: 2

Related Questions