Customer reward points calculation

Posted on 2012-09-06
Last Modified: 2012-11-06
Hi experts,

   I have asked this question earlier but haven't got any reply. Could someone reply.

I have customer reward points data. I have 2 tables.

1. CumulativeCredit -> Customer credited points and cumulative total
2. CumulativeDebit -> Customer debited points and cumulative total

From these 2 tables, I need to generate resultset to show how the customer points are debited. I mean the points debited are to be distributed as per they earned.

See attached spreadsheet for sample data and expected resultset.

I am totally mad to solve this.

Could someone have an eye on this and suggest how to achieve the result?

Question by:rajvja
    LVL 65

    Accepted Solution

    Just out of curiosity, what is the purpuse of having debits and credits in two separate sets?
    Why not the same set, with one as negative points, the other as positive points.

    Also, looking at your spreadsheet each row in both have a unique TransID, so I don't see what the relationship is between debit row and credit row.  So if there is none, why try to display one?
    LVL 28

    Assisted Solution

    by:Ryan McCauley
    Are you looking for something like a monthly breakdown of how points are earned and spent? If so, you could get a list of months using a process like this:

    And then sum your debits and credits to show how they've been used. If that's not what you're looking for, then I might need some more detail - your requirements seem a bit vague.
    LVL 11

    Author Comment

    Hi ryanmccauley,

       Thanks for the reply. I dont want monthly breakdown.

    Yes the requirement is nasty. I want to split the debit points based on credit date.
    For example, if I have used 500 points today that means definitely i should have earned those points earlier. But they might have earned on different dates.

    My requirement is to split 500 pts when they have earned.

    I hope it is clear.
    LVL 11

    Author Comment

    Thanks for the replies. It hasn't resolved my issue.
    LVL 11

    Author Comment

    I've requested that this question be closed as follows:

    Accepted answer: 0 points for rajvja's comment #a38382526
    Assisted answer: 250 points for jimhorn's comment #a38375934
    Assisted answer: 250 points for ryanmccauley's comment #a38377444

    for the following reason:

    Not resolved my issue

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now