Sum up different dates

  • Hi guys

    To the table below - I want to sum up the amount from each Flexi Account No. + each Posting Date where you have to consider the historical amount.

    For example: For the Flexi Account No. 50112235 and Posting Date 2021-11-29 I want to sum up the amount of this day and the amount of all the previous posting dates, which means -459+181+19 = -259.

    For the Flexi Account No. 50112235 and Posting Date of 2021-12-10, on the other hand, I want to sum up 19+339-459+181+19 = 99.

    I do not want to keep the column "Amount" from Table A, only the Flexi Account No., Posting Date, and I also want to create a new column (see output below).

    Anyone who can help me with this?

     

    SELECT

    [Flexi Account No.]

    ,[Posting Date]

    ,[Amount]

    FROM TableA

    Expected output:

     

  • I strongly recommend that you Read'n'Heed the article at the first link in my signature line below to help us help you.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • OK, from my understaning I should to this instead, not really sure though. Otherwise I would be glad with some input! 🙂

    --===== If the test table already exists, drop it

    IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

    DROP TABLE #mytable

    --===== Create the test table with

    CREATE TABLE #mytable

    (

    [Flexi Account No.] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table

    [Posting Date] DATETIME,

    [Amount]  DECIMAL(18, 2)

    )

    --===== Setup any special required conditions especially where dates are concerned

    SET DATEFORMAT DMY

    --===== All Inserts into the IDENTITY column

    SET IDENTITY_INSERT #mytable ON

    --===== Insert the test data into the test table

    INSERT INTO #mytable

    ([Flexi Account No.], [Posting Date], [Amount])

    SELECT '50112235','Oct 25 2021 12:00AM','19094.00000000000000000000', UNION ALL

    SELECT '50112235','Nov 10 2021 12:00AM','19.00000000000000000000', UNION ALL

    SELECT '50112235','Nov 10 2021 12:00AM','180.79000000000000000000', UNION ALL

    SELECT '50112235','Dec 10 2021 12:00AM','19.00000000000000000000', UNION ALL

    SELECT '50112235','Nov 29 2021 12:00AM','-459.00000000000000000000', UNION ALL

    SELECT '50112235','Dec 10 2021 12:00AM','339.03000000000000000000', UNION ALL

    SELECT '50112235','Jan 10 2022 12:00AM','19.00000000000000000000', UNION ALL

    SELECT '50112235','Dec 27 2021 12:00AM','-459.00000000000000000000', UNION ALL

    SELECT '50112235','Jan 10 2022 12:00AM','337.21000000000000000000', UNION ALL

    SELECT '50112235','Feb 10 2022 12:00AM','19.00000000000000000000', UNION ALL

    SELECT '50112235','Jan 27 2022 12:00AM','-459.00000000000000000000', UNION ALL

    SELECT '50112235','Feb 10 2022 12:00AM','335.36000000000000000000', UNION ALL

    SELECT '50112235','Feb 28 2022 12:00AM','-459.00000000000000000000', UNION ALL

    SELECT '50112235','Mar 3 2022 12:00AM','333.48000000000000000000', UNION ALL

    SELECT '50112235','Mar 3 2022 12:00AM','-18859.87000000000000000000', UNION ALL

    SELECT '50112236','Oct 25 2021 12:00AM','20595.00000000000000000000', UNION ALL

    SELECT '50112236','Nov 10 2021 12:00AM','19.00000000000000000000', UNION ALL

    SELECT '50112236','Nov 10 2021 12:00AM','195.00000000000000000000', UNION ALL

    SELECT '50112236','Nov 12 2021 12:00AM','-20849.00000000000000000000', UNION ALL

    SELECT '50112236','Nov 17 2021 12:00AM','40.00000000000000000000'

    --===== Set the identity insert back to normal

    SET IDENTITY_INSERT #mytable OFF

    • This reply was modified 2 months ago by  pelusch.
    • This reply was modified 2 months ago by  pelusch.
  • pelusch wrote:

    OK, from my understaning I should to this instead, not really sure though. Otherwise I would be glad with some input! 🙂

    Pretty close but I can tell you didn't try running it to see if it worked.  Close enough for this first go at it, though.

    I'll try to get to this after work later tonight.  There's some good people on this forum and they'll likely beat me to the punch, though.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • For anyone interested, here's the cleaned up and modernized version of the DDL and test data.  Note that there is no IDENTITY column in this table.  Also note that the data isn't the same as what the OP originally posted.  It's their first time at this.  I also don't know for sure if they're actually using the DMY date format or not but that's what they included.

    --===== If the test table already exists, drop it
    DROP TABLE IF EXISTS #mytable
    ;
    --===== Create the test table with
    CREATE TABLE #mytable
    (
    [Flexi Account No.] INT,
    [Posting Date] DATETIME,
    [Amount] DECIMAL(18,2)
    )
    ;
    --===== Setup any special required conditions especially where dates are concerned
    SET DATEFORMAT DMY
    ;
    --===== Insert the test data into the test table
    INSERT INTO #mytable
    ([Flexi Account No.], [Posting Date], [Amount])
    VALUES
    (50112235,'Oct 25 2021 12:00AM',19094.00 )
    ,(50112235,'Nov 10 2021 12:00AM',19.00 )
    ,(50112235,'Nov 10 2021 12:00AM',180.79 )
    ,(50112235,'Dec 10 2021 12:00AM',19.00 )
    ,(50112235,'Nov 29 2021 12:00AM',-459.00 )
    ,(50112235,'Dec 10 2021 12:00AM',339.03 )
    ,(50112235,'Jan 10 2022 12:00AM',19.00 )
    ,(50112235,'Dec 27 2021 12:00AM',-459.00 )
    ,(50112235,'Jan 10 2022 12:00AM',337.21 )
    ,(50112235,'Feb 10 2022 12:00AM',19.00 )
    ,(50112235,'Jan 27 2022 12:00AM',-459.00 )
    ,(50112235,'Feb 10 2022 12:00AM',335.36 )
    ,(50112235,'Feb 28 2022 12:00AM',-459.00 )
    ,(50112235,'Mar 3 2022 12:00AM',333.48 )
    ,(50112235,'Mar 3 2022 12:00AM',-18859.87)
    ,(50112236,'Oct 25 2021 12:00AM',20595.00 )
    ,(50112236,'Nov 10 2021 12:00AM',19.00 )
    ,(50112236,'Nov 10 2021 12:00AM',195.00 )
    ,(50112236,'Nov 12 2021 12:00AM',-20849.00)
    ,(50112236,'Nov 17 2021 12:00AM',40.00 )
    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • DECLARE @Sample TABLE
    (
    Account INT NOT NULL,
    Posting DATE NOT NULL,
    Amount INT NOT NULL
    );

    INSERT @Sample
    VALUES (50112235, '20211210', 19),
    (50112235, '20211210', 339),
    (50112235, '20211129', - 459),
    (50112235, '20211110', 181),
    (50112235, '20211110', 19),
    (50112236, '20211117', 40),
    (50112236, '20211112', -20849),
    (50112236, '20211110', 19),
    (50112236, '20211110', 195),
    (50112236, '20211025', 20595);

    -- swePeso solution
    SELECT DISTINCT Account,
    Posting,
    SUM(Amount) OVER (PARTITION BY Account ORDER BY Posting RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    FROM @Sample
    ORDER BY Account,
    Posting DESC;

    N 56°04'39.16"
    E 12°55'05.25"

  • Cool.  Peter answer the question.  Have a good night, folks.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply