May 5, 2022 at 4:14 am
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
May 5, 2022 at 8:46 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 5, 2022 at 12:40 pm
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
May 5, 2022 at 3:04 pm
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
Business Intelligence Analyst
Philadelphia, PA
May 5, 2022 at 3:29 pm
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 Balance
FROM dbo.final_data
ORDER BY year_cal, month_cal
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 5, 2022 at 4:50 pm
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
May 5, 2022 at 5:16 pm
Thanks but this one not working as excpeted.
May 5, 2022 at 6:30 pm
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
Business Intelligence Analyst
Philadelphia, PA
May 5, 2022 at 6:45 pm
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.
May 6, 2022 at 1:49 pm
Your Excel formula is wrong. Look at some simple examples.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 9, 2022 at 5:40 pm
Ok Thanks.
June 6, 2022 at 11:50 pm
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)
You must be logged in to reply to this topic. Login to reply