# Calculate field in SQL statement

• How we can achive from sql for balance field. I have attacted png file to expain. I will try my best to expain here

For first month balance is 4476.55 - 2867.00 = 1609.55

For second month balance is 6468.61  - ( first month of balance which is 1609.55 - trans out which is 2997.49) = 7856.55

For third month balance is 1259.18   - ( second month of balance which is 7856.55- trans out which is 4394.98) = -2202.39

account_id                         tran_in     tran_out    Month_Cal   Year_cal

1000667155163612544   4476.55    2867.00       1                       2020

1000667155163612544   6468.61    2997.49        2                      2020

1000667155163612544   1259.18     4394.98        3                       2020

1000667155163612544   2538.32    4044.39        4                       2020

• This topic was modified 1 month, 3 weeks ago by  sks_989.
• File attacted

###### Attachments:
You must be logged in to view attached files.
• You've been here long enough to know about providing your sample data in a form which can be pasted into SSMS (with relevant CREATE TABLE and INSERT statements), rather than being lazy and getting someone else to do it.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• sorry Phil my bad. here is create of table and insert data

CREATE TABLE [dbo].[final_data](

[account_id] [varchar](50) NULL,

[tran_in] [decimal](38, 2) NOT NULL,

[tran_out] [decimal](38, 2) NOT NULL,

[Month_Cal] [int] NULL,

[Year_cal] [int] NULL

) ON [PRIMARY]

GO

INSERT [dbo].[final_data] ([account_id], [tran_in], [tran_out], [Month_Cal], [Year_cal]) VALUES (N'abc123', CAST(5936.19 AS Decimal(38, 2)), CAST(6012.48 AS Decimal(38, 2)), 4, 2020)

GO

INSERT [dbo].[final_data] ([account_id], [tran_in], [tran_out], [Month_Cal], [Year_cal]) VALUES (N'abc123', CAST(12962.04 AS Decimal(38, 2)), CAST(7802.66 AS Decimal(38, 2)), 1, 2020)

GO

INSERT [dbo].[final_data] ([account_id], [tran_in], [tran_out], [Month_Cal], [Year_cal]) VALUES (N'abc123', CAST(2832.63 AS Decimal(38, 2)), CAST(5883.55 AS Decimal(38, 2)), 9, 2020)

GO

INSERT [dbo].[final_data] ([account_id], [tran_in], [tran_out], [Month_Cal], [Year_cal]) VALUES (N'abc123', CAST(6694.78 AS Decimal(38, 2)), CAST(7243.24 AS Decimal(38, 2)), 12, 2020)

GO

INSERT [dbo].[final_data] ([account_id], [tran_in], [tran_out], [Month_Cal], [Year_cal]) VALUES (N'abc123', CAST(8337.92 AS Decimal(38, 2)), CAST(4826.49 AS Decimal(38, 2)), 7, 2020)

GO

INSERT [dbo].[final_data] ([account_id], [tran_in], [tran_out], [Month_Cal], [Year_cal]) VALUES (N'abc123', CAST(3922.64 AS Decimal(38, 2)), CAST(7576.98 AS Decimal(38, 2)), 10, 2020)

GO

INSERT [dbo].[final_data] ([account_id], [tran_in], [tran_out], [Month_Cal], [Year_cal]) VALUES (N'abc123', CAST(8767.36 AS Decimal(38, 2)), CAST(1451.70 AS Decimal(38, 2)), 11, 2020)

GO

INSERT [dbo].[final_data] ([account_id], [tran_in], [tran_out], [Month_Cal], [Year_cal]) VALUES (N'abc123', CAST(14966.54 AS Decimal(38, 2)), CAST(9448.30 AS Decimal(38, 2)), 2, 2020)

GO

INSERT [dbo].[final_data] ([account_id], [tran_in], [tran_out], [Month_Cal], [Year_cal]) VALUES (N'abc123', CAST(5866.14 AS Decimal(38, 2)), CAST(14933.50 AS Decimal(38, 2)), 8, 2020)

GO

INSERT [dbo].[final_data] ([account_id], [tran_in], [tran_out], [Month_Cal], [Year_cal]) VALUES (N'abc123', CAST(4007.15 AS Decimal(38, 2)), CAST(17053.29 AS Decimal(38, 2)), 6, 2020)

GO

INSERT [dbo].[final_data] ([account_id], [tran_in], [tran_out], [Month_Cal], [Year_cal]) VALUES (N'abc123', CAST(6470.51 AS Decimal(38, 2)), CAST(5560.30 AS Decimal(38, 2)), 5, 2020)

GO

INSERT [dbo].[final_data] ([account_id], [tran_in], [tran_out], [Month_Cal], [Year_cal]) VALUES (N'abc123', CAST(6542.62 AS Decimal(38, 2)), CAST(5557.98 AS Decimal(38, 2)), 3, 2020)

GO

• Your sample data has no relation to your expected output.  How do you expect people to get from the sample data to your expected output if they are completely disconnected from each other?

J. Drew Allen

• `SELECT *,    SUM(tran_in - tran_out) OVER(PARTITION BY account_id ORDER BY Year_cal, Month_Cal         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BalanceFROM dbo.final_dataORDER BY year_cal, month_cal`

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

• Sorry again

For first month balance is trans in 12962.04- 7802.66 trans out = 5159.38

For second month balance is trans in 14966.54  - ( first month of balance which is 5159.38 - trans out which is 9448.30) = 19255.46

For third month balance is trans in 6542.62   - ( second month of balance which is 19255.46 - trans out which is 5557.98) = -7154.86

account_id tran_in          tran_out      Month_Cal       Year_cal          Balance

abc123        12962.04       7802.66        1                          2020                5159.38

abc123       14966.54        9448.30        2                         2020                 19255.46

abc123       6542.62          5557.98        3                          2020                 -7154.86

abc123       5936.19          6012.48        4                          2020                  19103.53

• This reply was modified 1 month, 3 weeks ago by  sks_989.
• This reply was modified 1 month, 3 weeks ago by  sks_989.
###### Attachments:
You must be logged in to view attached files.
• Thanks but this one not working as excpeted.

• Your Excel formula doesn't make sense to me.  For one thing, your Excel data isn't sorted, so your value for January is based on the value for April.  For another thing, I think the signs are off.  I would think that the ingoing and outgoing transactions should have different signs, but you're adding both of them.  I would also think that the previous balance should be added rather than subtracted.  I think your Excel formula should be previous balance + current month balance which is previous balance + (incoming transactions - outgoing transactions) which works out to F3 + ( B3 - C3)  where you have B3 - (F3 - C3), so you are subtracting F3 when you should be adding it and adding C3 when you should be subtracting it.

J. Drew Allen

• Here is updated excel this is what I need it that why i have formula

calculated by adding all the transfers in and subtracting all the transfers out from the previous

account balance

That why my formula is in this excel

Please explain if i am doing wrong from my side.

###### Attachments:
You must be logged in to view attached files.
• Your Excel formula is wrong.  Look at some simple examples.

• If your previous balance is 1000 and you have no transfers in and no transfers out, the balance should still be 1000.  Your formula returns -1000.
• If your previous balance is 0 and you have no transfers in and your transfers out is 1000, your new balance should be -1000.  Your formula returns +1000.

J. Drew Allen

• Ok Thanks.

• Seems to me you may want to get familiarized with the LAG() function. I am sure you can incorporate it into your solution,

https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-2017

The previous months ending balance is the current months starting balance. Add the sum of all transactions for the current month to the starting balance to get the ending balance and so on.

In your case it looks like you apply the lag function with a one row look back on the balance column, add tran_in, subtract tran_out and that should get you the current row balance amount

----------------------------------------------------

Viewing 13 posts - 1 through 12 (of 12 total)