Post reply

Opening and Closing balance of each activity

  • December 11, 2019 at 11:07 am

    #3706854

    <!--more-->

    Hi Guys,

    I am working on MySQL to get opening and closing balance of each activity per day. I have only 4 columns: Sample Data

    I have these columns from the source data table. Activity Date, MEASURES, PROCESSED, RECEIVED:

    I want to write logic in MYSQL:

    Opening balance = Previous Day of the closing balance

    Closing Balance = Opening Balance + Received - Processed

    It needs some recursive adaption to make this happen because the opening balance depends on the previous day of closing balance and closing balance depends on the opening balance.

    Let me know if you need more details.

     

  • December 11, 2019 at 11:20 am

    #3706858

    You'd be better off posting in a MySQL forum - this one is for SQL Server.  You're probably right - you'll need a recursive solution.  Wherever you seek help, I recommend that you provide table DDL and sample data in a format that allows people to easily create it in their environment, and that you show what you've already tried.

    John

  • December 11, 2019 at 11:44 am

    #3706859

    Thanks for the response.

    If I get any help in SQL Server then will try to implement in MYSQL as well.

    Sample DDL:

    CREATE TABLE Open_Clos_Bala ( DATE_ACT DATE, Measure CHAR(100), PROCESSED FLOAT, RECEIVED FLOAT)

    INSERT INTO coyote_dashboard.open_clos_bala (DATE_ACT, Measure, PROCESSED, RECEIVED) VALUES ('2019-12-10', 'Carrrier', '1000', '800')

    INSERT INTO coyote_dashboard.open_clos_bala (DATE_ACT, Measure, PROCESSED, RECEIVED) VALUES ('2019-12-11', 'Carrrier', '800', '600')

    INSERT INTO coyote_dashboard.open_clos_bala (DATE_ACT, Measure, PROCESSED, RECEIVED) VALUES ('2019-12-10', 'Quick Pay', '1500', '1300')

    INSERT INTO coyote_dashboard.open_clos_bala (DATE_ACT, Measure, PROCESSED, RECEIVED) VALUES ('2019-12-11', 'Quick Pay', '1800', '700')

  • December 11, 2019 at 1:38 pm

    #3706946

    I got the solution for this!

     

    Thanks,

    Farooqh

Viewing 4 posts - 1 through 3 (of 3 total)

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