Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2012
    Location
    Greyton, South Africa
    Posts
    19

    Unanswered: tables for cumulative tally and carrying balances forward

    Hi All

    I'm after some advice as far as design is concerned. I have a client that I provide financial modelling services to (using Excel). They have a requirement to start capturing subscriber movements in their SQL DB. I would like to help them by suggesting how the table should be set-up and how to extract the necessary movements report. This is largely so that I may include these components in some of the financial models that I am working on.

    Subscribers are reported as follows:

    Opening subs (the prior periods closing balance; or the sum of new sales at point of 1st entry)
    + New Sales (new subs)
    + Upgrades (movement from a lower product package to the associated package)
    - Downgrades (movement to a lower product package from the associated package)
    - Churn (subscriber losses)
    Closing Balance

    All transactions are captured against a specific product package, on a specific date (ymd), and for an associated platform (e.g. digital TV, broadband TV, cable TV).

    I believe we only need to capture new sales, upgrades, downgrades and churn. And then used a SP to compile the movements behaviour as described above.

    So perhaps the table would appear as follows:
    Platform Package Date Movement Value
    DTV PROD 1 2014-11-02 New Sales 8
    DTV PROD 1 2014-11-02 Upgrades 1
    DTV PROD 1 2014-11-02 Downgrades 3
    DTV PROD 1 2014-11-02 Churn 3
    DTV PROD 1 2014-11-03 New Sales 6
    DTV PROD 1 2014-11-03 Upgrades 2
    DTV PROD 1 2014-11-03 Downgrades 1
    DTV PROD 1 2014-11-03 Churn 2

    So I am assuming that given a table such as the above, we could write a SP to produce an output such as (note, below looks at monthly total so will not agree back to sample above which contains only 2 days):

    Platform Package Movement September October November
    DTV PROD 1 OPEN 600 676 776
    DTV PROD 1 New Sales 92 106 88
    DTV PROD 1 Upgrades 22 26 14
    DTV PROD 1 Downgrades 16 14 16
    DTV PROD 1 Churn 22 18 21
    DTV PROD 1 CLOSE 676 776 841


    I'm really not sure how one is best to accumulate the balances given that the open date for any given reporting period is in fact an accumulation of all balances since day 1.

    How does one typically capture this type of thing in SQL?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you do the SQL correctly, each row in every table describes exactly one fact. A single transaction, not a transaction and all of its history.

    The query/report/display is another matter, and can be handled by SQL (using window functions), a mid-tier or reporting tool, or even a tool running at the point of delivery (like a "fat client"). The means varies, there are thousands of ways to deliver the results that the client needs, and those aren't really germane to long term use and effectiveness of your solution... The database, mid-tier, and client will change over time so don't design for them. Pick a clean, simple design and long term that will be the best answer in terms of longevity, simplicity, and lower TCO (Total Cost of Ownership).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2012
    Location
    Greyton, South Africa
    Posts
    19
    Thanks for your advice, I feel re-assured that the table structure is ok (given it represents a single transaction). Do you agree? If so, this means that for a report on e.g. opening subs for any future date I have to accumulate the total of all prior transactions. So is that the recommendation here?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I have far too many questions to be able to give you a good strategy.

    I assume that this financial data is audited, and that audit balances have to be approved and maintained. This is both a legal and customary practice requirement in virtually every nation, but I can't specifically speak to South Africa. Depending on the type of investment, it may be impractical or impossible to have all of the data online (I've participated in audits of funds with well over 400 years of history)... Even within a transaction type, the length varies considerably: a North American mortgage rarely lasts over 30 years, a Japanese mortgage is rarely less than 100 years.

    If you can give a bit more background I'm sure that someone can help you find a starting point, but only you have sufficient background knowledge to know what is the "best fit" for your needs.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jun 2012
    Location
    Greyton, South Africa
    Posts
    19
    Hi again

    This isn't sensitive financial data at all. My client is in Norway and we are inputting forecast volumes for product subscribers. Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •