CODEWITHSUNDEEP
sqlsql-serverjoinself-join
misterk
misterk

Reputation: 27

SQL - Need return from single table of SUMs over 3 different date ranges

I have a table of Account Transactions that includes ID, Amount, Date. Basically, I want to create a resulting table that looks at the table and returns what the SUM was for the Account over three different Ending Date Ranges. Then I want to Flag (Combined_Flag) each Account ID, 1 if any of the SUMs for that ID are non-zero, and a 0 if all of the SUMs are 0.

Date Range 1) Min Date to End of Last Month (-1 Month)
Date Range 2) Min Date to End of 2 Months ago (-2 Months)
Date Range 3) Min Date to End of Last Month, Last Year (-13 Months)

The Resulting table should be: ID, SUM_R1, SUM_R2, SUM_R3, Flag_R1, Flag_R2, Flag_R3, Combined_Flag

Example Data

| ID       | Amount         | Date          |
| -------- | -------------- |-------------- |
| 1        | 20             | 09/01/19      |
| 2        | 40             | 09/01/19      |
| 3        | 0              | 09/01/19      |
| 4        | 0              | 09/01/19      |
| 1        | 10             | 10/01/19      |
| 2        | 0              | 10/01/19      |
| 3        | 0              | 10/01/19      |
| 4        | 0              | 10/01/19      |
| 1        | 15             | 11/01/19      |
| 2        | 40             | 11/01/19      |
| 3        | 0              | 11/01/19      |
| 4        | 0              | 11/01/19      |
| 1        | 20             | 09/01/20      |
| 2        | 40             | 09/01/20      |
| 3        | 0              | 09/01/20      |
| 4        | 50             | 09/01/20      |
| 1        | 10             | 10/01/20      |
| 2        | 0              | 10/01/20      |
| 3        | 0              | 10/01/20      |
| 4        | 65             | 10/01/20      |
| 1        | 15             | 11/01/20      |
| 2        | 40             | 11/01/20      |
| 3        | 0              | 11/01/20      |
| 4        | 0              | 11/01/20      |

Expected Result Table (Using Date of 12/21/2020)

| ID       | SUM_R1   | SUM_R2   | SUM_R3   | Flag_R1   | Flag_R2   | Flag_R3   | Combined_Flag   |
| -------- | -------- | -------- | -------- | --------- | --------- | --------- | --------------- |
| 1        | 90       | 75       | 45       | 1         | 1         | 1         | 1               |
| 2        | 160      | 120      | 80       | 1         | 1         | 1         | 1               |
| 3        | 0        | 0        | 0        | 0         | 0         | 0         | 0               |
| 4        | 115      | 115      | 0        | 1         | 1         | 0         | 1               |

The difficulty I'm having is in joining the table basically to itself 2 times. I'm getting results all over the place and not really sure exactly what's going on.

Upvotes: 1

Views: 98

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Is this what you want?

select id,
       sum(case when date < datefromparts(year(v.dte), month(v.dte), 1)
                then amount else 0
           end) as sum_r1,
       sum(case when date < dateadd(month, -1, datefromparts(year(v.dte), month(v.dte), 1))
                then amount else 0
           end) as sum_r2,
       sum(case when date < dateadd(month, -13, datefromparts(year(v.dte), month(v.dte), 1))
                then amount else 0
           end) as sum_r3,
       max(case when amount > 0 and date < datefromparts(year(v.dte), month(v.dte), 1)
                then 1 else 0
           end) as flag_r1,
       max(case when amount > 0 and date < dateadd(month, -1, datefromparts(year(v.dte), month(v.dte), 1))
                then 1 else 0
           end) as flag_r2,
       max(case when amount > 0 and date < dateadd(month, -13, datefromparts(year(v.dte), month(v.dte), 1))
                then 1 else 0
           end) as flag_r3
from t cross join
     (values (convert(date, '2020-12-21'))
     ) v(dte)
group by id;

The flag columns assume that the amounts are never negative (which is consistent with the data in your question.

EDIT:

The shorthand in the comment for creating the flag looks like:

   abs(sign(sum(case when amount > 0 and date < datefromparts(year(v.dte), month(v.dte), 1) 
                     then amount else 0 
                end))) as flag_r1,

Here is a db<>fiddle.

Upvotes: 1

Related Questions