CODEWITHSUNDEEP
t-sql
Aercheony
Aercheony

Reputation: 1

T-SQL Query with SUM, DATEADD logic

I’m looking for a query that can calculate the Calculation table from the Value table. The query had to take the Date value, like ‘2017-03-01’ but I need to take the values from the records 2 months before, but it must be the record with the same ID. In this scenario It must take the values from 2017-03-01 , 2017-02-01, 2017-01-01 (993, 492, 312) and sum together (1,797) and store it in the 2017-03-01 record like below from the customer where CustomerID = 1001.

|1001 | 2017-02-01 |   492 |             |
|1001 | 2017-03-01 |   993 |             |
|1002 | 2017-01-01 |   838 |        1797 |

This need to be done of all records. Of course, some record cannot go back minus 2 months, but those values can stay null. I really don’t know to write this query.

Got some test queries to make some steps like:

SELECT SUM(Value) FROM TestTable WHERE Date BETWEEN Date AND DATEADD(month, -2, Date);

+------------+------------+-------+-------------+
| CustomerID |    Date    | Value | Calculation |
+------------+------------+-------+-------------+
|       1001 | 2016-08-01 |   123 |             |
|       1001 | 2016-09-01 |   434 |             |
|       1001 | 2016-10-01 |   423 |             |
|       1001 | 2016-11-01 |   235 |             |
|       1001 | 2016-12-01 |   432 |             |
|       1001 | 2017-01-01 |   312 |             |
|       1001 | 2017-02-01 |   492 |             |
|       1001 | 2017-03-01 |   993 |             |
|       1002 | 2017-01-01 |   838 |             |
|       1002 | 2017-02-01 |   234 |             |
|       1002 | 2017-03-01 |   453 |             |
|       1002 | 2017-04-01 |   838 |             |
|       1003 | 2017-01-01 |   746 |             |
|       1003 | 2017-02-01 |   242 |             |
|       1003 | 2017-03-01 |   432 |             |
|       1004 | 2017-01-01 |   431 |             |
|       1004 | 2017-02-01 |   113 |             |
+------------+------------+-------+-------------+

I want my table like below

+------------+------------+-------+-------------+
| CustomerID |    Date    | Value | Calculation |
+------------+------------+-------+-------------+
|       1001 | 2016-08-01 |   123 | NULL        |
|       1001 | 2016-09-01 |   434 | NULL        |
|       1001 | 2016-10-01 |   423 | 980         |
|       1001 | 2016-11-01 |   235 | 1092        |
|       1001 | 2016-12-01 |   432 | 1090        |
|       1001 | 2017-01-01 |   312 | 979         |
|       1001 | 2017-02-01 |   492 | 1236        |
|       1001 | 2017-03-01 |   993 | 1797        |
|       1002 | 2017-01-01 |   838 | NULL        |
|       1002 | 2017-02-01 |   234 | NULL        |
|       1002 | 2017-03-01 |   453 | 1525        |
|       1002 | 2017-04-01 |   838 | 1525        |
|       1003 | 2017-01-01 |   746 | NULL        |
|       1003 | 2017-02-01 |   242 | NULL        |
|       1003 | 2017-03-01 |   432 | 1420        |
|       1004 | 2017-01-01 |   431 | NULL        |
|       1004 | 2017-02-01 |   113 | NULL        |
+------------+------------+-------+-------------+

I hope you can help me with this! 😉

Upvotes: 0

Views: 383

Answers (2)

Onat Sölek
Onat Sölek

Reputation: 11

--First Create Table

create table Testtable(
    CustomerID int,
    Date date,
    Value int
)


--Insert test values

insert into Testtable VALUES(1001,'2016-08-01',123),
                            (1001,'2016-09-01',434),
                            (1001,'2016-10-01',423),
                            (1001,'2016-11-01',235),
                            (1001,'2016-12-01',432),
                            (1001,'2017-01-01',312),
                            (1001,'2017-02-01',492),
                            (1001,'2017-03-01',993),
                            (1002,'2017-01-01',838),
                            (1002,'2017-02-01',234),
                            (1002,'2017-03-01',453),
                            (1002,'2017-04-01',838),
                            (1003,'2017-01-01',746),
                            (1003,'2017-02-01',242),
                            (1003,'2017-03-01',432),
                            (1004,'2017-01-01',431),
                            (1004,'2017-02-01',113);

--Select Query

SELECT 
    CustomerID,
    Date,
    Value,
    CASE WHEN (SELECT COUNT(*) FROM Testtable T4 WHERE T4.CustomerID = T3.CustomerID AND T4.Date < T3.Date) < 2 THEN NULL
         ELSE Calculation END AS Calculation
FROM
(SELECT 
     *,
     (SELECT SUM(T2.Value) FROM Testtable T2 WHERE T.CustomerID = T2.CustomerID AND T2.Date BETWEEN DATEADD(month,-2,T.Date) AND T.Date) AS Calculation
FROM Testtable T) AS T3

Upvotes: 1

Boris Jovanovic
Boris Jovanovic

Reputation: 132

This might take some trial and error to get completely correct but I'll give it a shot, try below:

SELECT CustomerID
       , Date
       , Value
       , Value + 
         (SELECT Value from table_name where CustomerID = x.CustomerID and Date = 
          DATEADD(m,-1,x.Date)) +
         (SELECT Value from table_name where CustomerID = x.CustomerID and Date = 
          DATEADD(m,-2,x.Date)) as Calculation
FROM table_name x

Note that this will only work it the CustomerID/Date are a composite key in your table.

Hope this helps!

Upvotes: 0

Related Questions