CODEWITHSUNDEEP
sqlsql-server
Ricardo Pedrotti
Ricardo Pedrotti

Reputation: 83

Sum previous months values for row date

I need a SQL Server query to return something like:

Client_ID | REF_DATE | SALES | SALES_PREVIOUS_6_MONTHS | SALES_NEXT_6_MONTHS

But the problem is that my base date is not "Today" or something like that. But actually the "REF_DATE".

So, if a customer buys something on july/2015, I want to know how much he spent in this reference month (july/2015), the previous and next 6 months from july/2015. I need to have a line for August to and so on.

I'm using an invoice database where I have an aggregation for the month on the first day of it.

I tried using the CASE WHEN statement, but as it returns only "FALSE" for my condition, it returns 0.

SELECT
    Client_ID,
    REF_DATE,
    SUM(SALES),
    CASE 
       WHEN REF_DATE BETWEEN DATEADD(MONTH, -6, REF_DATE) AND DATEADD(MONTH, -1, REF_DATE) 
          THEN SUM(SALES) 
          ELSE 0.0 
    END AS SALES_PREVIOUS_6_MONTHS,
    CASE 
       WHEN REF_DATE BETWEEN DATEADD(MONTH, +1, REF_DATE) AND DATEADD(MONTH, +6, REF_DATE) 
          THEN SUM(SALES) 
          ELSE 0.0 
    END AS SALES_NEXT_6_MONTHS
FROM
    dbo.invoices         
GROUP BY
    Client_ID, REF_DATE

My desired output is something like:

+------------+------------+---------+---------------------------+----------------------+
| Client_ID  |  REF_DATE  |  SALES  |  SALES_PREVIOUS_6_MONTHS  |  SALES_NEXT_6_MONTHS |
+------------+------------+---------+---------------------------+----------------------+
|          1 | 01/07/2014 |       0 |                         0 |                 1234 |
|          1 | 01/08/2014 |       0 |                         0 |                 3579 |
|          1 | 01/09/2014 |       0 |                         0 |                 7035 |
|          1 | 01/10/2014 |       0 |                         0 |                11602 |
|          1 | 01/11/2014 |       0 |                         0 |                12836 |
|          1 | 01/12/2014 |       0 |                         0 |                17403 |
|          1 | 01/01/2015 |    1234 |                         0 |                24059 |
|          1 | 01/02/2015 |    2345 |                      1234 |                21714 |
|          1 | 01/03/2015 |    3456 |                      3579 |                18258 |
|          1 | 01/04/2015 |    4567 |                      7035 |                13691 |
|          1 | 01/05/2015 |    1234 |                     11602 |                12457 |
|          1 | 01/06/2015 |    4567 |                     12836 |                 7890 |
|          1 | 01/07/2015 |    7890 |                     17403 |                    0 |
|          1 | 01/08/2015 |       0 |                     24059 |                    0 |
|          1 | 01/09/2015 |       0 |                     21714 |                    0 |
|          1 | 01/10/2015 |       0 |                     18258 |                    0 |
|          1 | 01/11/2015 |       0 |                     13691 |                    0 |
|          1 | 01/12/2015 |       0 |                     12457 |                    0 |
+------------+------------+---------+---------------------------+----------------------+

Upvotes: 3

Views: 1068

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

If you have a record for every client for every month:

SELECT Client_ID, REF_DATE, SUM(SALES),
       SUM(SUM(SALES)) OVER (PARTITION BY Client_id ORDER BY REF_DATE ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING) as SALES_PREVIOUS_6_MONTHS,
       SUM(SUM(SALES)) OVER (PARTITION BY Client_id ORDER BY REF_DATE ROWS BETWEEN 1 FOLLOWING AND 6 FOLLOWING) as SALES_NEXT_6_MONTHS,
FROM dbo.invoices i  
GROUP BY Client_ID, REF_DATE;

If you don't have a record for each client for each month, then the logic is a bit more complicated.

Upvotes: 3

Related Questions