Post reply

Sum up different dates

  • November 30, 2022 at 3:09 pm

    #4120112

    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:

     

  • November 30, 2022 at 3:44 pm

    #4120127

    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.

    Change is inevitable... Change for the better is not.

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

  • November 30, 2022 at 7:00 pm

    #4120271

    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 1 year, 4 months ago by  pelusch.
    • This reply was modified 1 year, 4 months ago by  pelusch.

  • November 30, 2022 at 9:46 pm

    #4120346

    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.

    Change is inevitable... Change for the better is not.

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

  • November 30, 2022 at 10:00 pm

    #4120350

    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.

    Change is inevitable... Change for the better is not.

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

  • November 30, 2022 at 10:43 pm

    #4120362 

    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"

  • December 1, 2022 at 3:37 am

    #4120467

    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.

    Change is inevitable... Change for the better is not.

    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