Post reply

Ho do you make monthly summaries and running toals ?

  • February 20, 2015 at 6:35 am

    #294794

    Hi friends,

    Imagine a payment system or banking system. customers make transactions (like one transaction in every 2 seconds) and they have a daily limit. In order to prevent them to exceed limit you have to run a SELECT SUM() statement and if they are under limit then run their transaction. Also they have user panels that shows transaction summaries so you need run SUM again to show statistics.

    but in time SUM statements become too slow and loads unnecessary load to sql server.

    How do you plan this kind of application ? For every transaction I need monthly total, yearly total, daily total. Also I need to get these totals from another table that hold invalid transactions blah blah.

    I keep parameters in memory so I don't access then every time using database. But transaction totals must com from live data. I wrote triggers and stored procedures and keep totals in summary table but I'm curious about how running totals and operations that requires sum of live data implemented by you. At the moment I dont run SUM command every time I read summary table for every single transaction.

    I read about running total questions in forums but these solutions also access every single row on database.

    also as I need daily, weekly, monthly and yearly running totals I have to query dates multiple times. so single running total solution is not exactly for me.

    any suggestions ? Should I consider a logic like using redis database and write to summary table hourly or daily ?

    thanks.

  • February 20, 2015 at 6:44 am

    #1778929

    Just a thought:

    why not keep a balance of everything? Not just the current balance, but also the YTD, MTD, daily total?

    Each time a transaction occurs, you just update all those balances.

    This means you do not have to sum all rows, but just read the most recent row for that client.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 2 posts - 1 through 1 (of 1 total)

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