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.

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.

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.

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  @SampleVALUES  (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 solutionSELECT DISTINCT Account,                Posting,                SUM(Amount) OVER (PARTITION BY Account ORDER BY Posting RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)FROM            @SampleORDER 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.