CODEWITHSUNDEEP
sqlpostgresql
Taylor C
Taylor C

Reputation: 13

SQL group aggregate by date range columns in another table

I need a query to group an aggregate in one table by date ranges in another table.

Table 1
 weeknumber | weekyear | weekstart  |  weekend
------------+----------+------------+------------
         18 |     2016 | 2016-02-01 | 2016-02-08
         19 |     2016 | 2016-02-08 | 2016-02-15
         20 |     2016 | 2016-02-15 | 2016-02-22
         21 |     2016 | 2016-02-22 | 2016-02-29
         22 |     2016 | 2016-02-29 | 2016-03-07
         23 |     2016 | 2016-03-07 | 2016-03-14
         24 |     2016 | 2016-03-14 | 2016-03-21
         25 |     2016 | 2016-03-21 | 2016-03-28
         26 |     2016 | 2016-03-28 | 2016-04-04
         27 |     2016 | 2016-04-04 | 2016-04-11
         28 |     2016 | 2016-04-11 | 2016-04-18
         29 |     2016 | 2016-04-18 | 2016-04-25
         30 |     2016 | 2016-04-25 | 2016-05-02
         31 |     2016 | 2016-05-02 | 2016-05-09
         32 |     2016 | 2016-05-09 | 2016-05-16
         33 |     2016 | 2016-05-16 | 2016-05-23
         34 |     2016 | 2016-05-23 | 2016-05-30
         35 |     2016 | 2016-05-30 | 2016-06-06
         36 |     2016 | 2016-06-06 | 2016-06-13
         37 |     2016 | 2016-06-13 | 2016-06-20
         38 |     2016 | 2016-06-20 | 2016-06-27
         39 |     2016 | 2016-06-27 | 2016-07-04
         40 |     2016 | 2016-07-04 | 2016-07-11
         41 |     2016 | 2016-07-11 | 2016-07-18
         42 |     2016 | 2016-07-18 | 2016-07-25
         43 |     2016 | 2016-07-25 | 2016-08-01
         44 |     2016 | 2016-08-01 | 2016-08-08
         45 |     2016 | 2016-08-08 | 2016-08-15
         46 |     2016 | 2016-08-15 | 2016-08-22
         47 |     2016 | 2016-08-22 | 2016-08-29
         48 |     2016 | 2016-08-29 | 2016-09-05
         49 |     2016 | 2016-09-05 | 2016-09-12
Table 2
 accountid |   rdate    | fee1 | fee2 | fee3 | fee4
-----------+------------+------+------+------+------
    481164 | 2015-12-22 |    8 |    1 |    5 |    1
    481164 | 2002-12-22 |    1 |    0 |    0 |    0
    481166 | 2015-12-22 |    1 |    0 |    0 |    0
    481166 | 2016-10-20 |   14 |    0 |    0 |    0
    481166 | 2016-10-02 |    5 |    0 |    0 |    0
    481166 | 2016-01-06 |   18 |    4 |    0 |    5
    482136 | 2016-07-04 |   18 |    0 |    0 |    0
    481164 | 2016-07-04 |    2 |    3 |    4 |    5
    481164 | 2016-06-28 |   34 |    0 |    0 |    0
    481166 | 2016-07-20 |   50 |    0 |    0 |   69
    481166 | 2016-07-13 |   16 |    0 |    0 |    5
    481166 | 2016-09-15 |    8 |    0 |    0 |    2
    481166 | 2016-10-03 |    8 |    0 |    0 |    0

I need to aggregate fee1+fee2+fee3+fee4 for rdates in each date range(weekstart,weekend) in table 1 and then group by accountid. Something like this:

 accountid |  weekstart |   weekend  | SUM 
-----------+------------+------------+------
    481164 | 2016-02-01 | 2016-02-08 |  69 
    481166 | 2016-02-01 | 2016-02-08 |  44
    481164 | 2016-02-08 | 2016-02-15 |  22 
    481166 | 2016-02-08 | 2016-02-15 |  12

Upvotes: 1

Views: 109

Answers (1)

McNets
McNets

Reputation: 10807

select accountid, weekstart, weekend, 
       sum(fee1 + fee2 + fee3 + fee4) as total_fee
from table2
     inner join table1 on table2.rdate >= table1.weekstart and table2.rdate < table1.weekend 
group by accountid, weekstart, weekend;

Just a thing:

 weeknumber | weekyear | weekstart  |  weekend
------------+----------+------------+------------
         18 |     2016 | 2016-02-01 | 2016-02-08
         19 |     2016 | 2016-02-08 | 2016-02-15

weekend for week 18 should be 2016-02-07, because 2016-02-08 is weekstart for week 19.

 weeknumber | weekyear | weekstart  |  weekend
------------+----------+------------+------------
         18 |     2016 | 2016-02-01 | 2016-02-07
         19 |     2016 | 2016-02-08 | 2016-02-14

Check it here: http://rextester.com/NCBO56250

Upvotes: 2

Related Questions