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
    Business Intelligence Analyst
    Philadelphia, PA

  • 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) "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
    Business Intelligence Analyst
    Philadelphia, PA

  • 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
    Business Intelligence Analyst
    Philadelphia, PA

  • 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)

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