CODEWITHSUNDEEP
sql-serversum
mickey
mickey

Reputation: 1

How to find the sum of prior amounts for an year including the current amount

I need to get the sum of current with Previous amount of that specific year for the 2nd entry.

Input_table

+-----------+----------+-------------+-----------+----------+
| ID        | Name     | Date1       |Date 2     |amount    |
+-----------+----------+-------------+-----------+----------+
| 10000000  | ABC      | 11/2/2017   |**11/2/2018**  |2504      |
| 10000000  | ABC      | 12/20/2017  |**11/2/2018**  |-2174     |
| 10000000  | ABC      | 10/05/2018  |10/05/2019 |1234      |
| 10000000  | ABC      | 10/06/2019  |10/06/2020 |3456      |
+-----------+----------+-------------+-----------+----------+

Here's the desired output:

+-----------+----------+-------------+-----------+----------+
| ID        | Name     | Date1       |Date 2     |amount    |
+-----------+----------+-------------+-----------+----------+
| 10000000  | ABC      | 11/2/2017   |**11/2/2018**  |2504      |
| 10000000  | ABC      | 12/20/2017  |**11/2/2018**  |329       |
| 10000000  | ABC      | 10/05/2018  |10/05/2019 |1234      |
| 10000000  | ABC      | 10/06/2019  |10/06/2020 |3456      |
+-----------+----------+-------------+-----------+----------+

Record 3 amount - 1234 (1234+0(no records in that year)) as date 2 is different

Record 4 amount - 3456 (3456+0(no records in that year)) as date 2 is different

I'm Looking for sum of current,previous amount value for a record if date 2 of both records are same. Say if we have 3 records with same date 2 value. then first record should have its actual amount value , 2 nd record should have first record amount + 2 record amount and 3 record would have 1st record amount +2 nd record amount+3rd record amount.

Upvotes: 0

Views: 57

Answers (1)

critical_error
critical_error

Reputation: 6726

I've made some considerable assumptions here. Mainly, that you want to see if the current record's "Date2" value exists somewhere in "Date1" for the previous year (e.g. Date2: 11/02/2018 is looking if Date1: 11/02/2017 exists) and that the ID and Name columns have some sort of relevance in filtering the data.

Here's what I worked with in SSMS:

-- Create sample data.

DECLARE @Data TABLE (
    ID VARCHAR(10), [Name] VARCHAR(3), Date1 DATE, Date2 DATE, Amount DECIMAL(18,2)
);

INSERT INTO @Data ( ID, [Name], Date1, Date2, Amount ) VALUES
( '10000000', 'ABC', '11/2/2017', '11/2/2018',  2504),
( '10000000', 'ABC', '12/20/2017', '11/2/2018',  -2174),
( '10000000', 'ABC', '10/05/2018', '10/05/2019',  1234),
( '10000000', 'ABC', '10/06/2019', '10/06/2020',  3456);

-- Query sample data

SELECT
    MyData.ID, MyData.[Name], MyData.Date1, MyData.Date2, MyData.Amount
    , ISNULL( LastYear.Amount, 0 ) AS PreviousYear
    , ( MyData.Amount + ISNULL( LastYear.Amount, 0 ) ) AS NewAmount
FROM @Data AS MyData
OUTER APPLY (

    SELECT
        SubData.Amount AS Amount
    FROM @Data AS SubData
    WHERE
        SubData.ID = MyData.ID
        AND SubData.[Name] = MyData.[Name]
        AND SubData.Date1 = DATEADD( yy, -1, MyData.Date2 )
        AND SubData.Date1 <> MyData.Date1   -- A weak attempt to exclude the current MyData record. Really needs a unique id.

) AS LastYear
ORDER BY
    MyData.Date2;

-- Returns

+----------+------+------------+------------+----------+--------------+-----------+
|    ID    | Name |   Date1    |   Date2    |  Amount  | PreviousYear | NewAmount |
+----------+------+------------+------------+----------+--------------+-----------+
| 10000000 | ABC  | 2017-11-02 | 2018-11-02 | 2504.00  | 0.00         | 2504.00   |
| 10000000 | ABC  | 2017-12-20 | 2018-11-02 | -2174.00 | 2504.00      | 330.00    |
| 10000000 | ABC  | 2018-10-05 | 2019-10-05 | 1234.00  | 0.00         | 1234.00   |
| 10000000 | ABC  | 2019-10-06 | 2020-10-06 | 3456.00  | 0.00         | 3456.00   |
+----------+------+------------+------------+----------+--------------+-----------+

Ideally, the queried table has a PK that can be used to exclude the current record from being included (note the weak attempt to use Date1).

The same example using a PK (unique id):

DECLARE @Data TABLE (
    ID VARCHAR(10), [Name] VARCHAR(3), Date1 DATE, Date2 DATE, Amount DECIMAL(18,2), pk_PrimaryKey INT IDENTITY(1,1) PRIMARY KEY
);

INSERT INTO @Data ( ID, [Name], Date1, Date2, Amount ) VALUES
( '10000000', 'ABC', '11/2/2017', '11/2/2018',  2504),
( '10000000', 'ABC', '12/20/2017', '11/2/2018',  -2174),
( '10000000', 'ABC', '10/05/2018', '10/05/2019',  1234),
( '10000000', 'ABC', '10/06/2019', '10/06/2020',  3456);

SELECT
    MyData.ID, MyData.[Name], MyData.Date1, MyData.Date2, MyData.Amount
    , ISNULL( LastYear.Amount, 0 ) AS PreviousYear
    , ( MyData.Amount + ISNULL( LastYear.Amount, 0 ) ) AS NewAmount
FROM @Data AS MyData
OUTER APPLY (

    SELECT
        SubData.Amount AS Amount
    FROM @Data AS SubData
    WHERE
        SubData.ID = MyData.ID
        AND SubData.[Name] = MyData.[Name]
        AND SubData.Date1 = DATEADD( yy, -1, MyData.Date2 )
        AND SubData.pk_PrimaryKey <> MyData.pk_PrimaryKey

) AS LastYear
ORDER BY
    MyData.Date1;

Anyway, this is my best guess based on what little information I had to go on. Hopefully this can get you moving in the right direction.

Upvotes: 1

Related Questions