[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Customer reward points calculation

Posted on 2012-09-06
Medium Priority
254 Views
Hi experts,

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?

thanks
Points.xlsx
0
Question by:rajvja
• 3

LVL 66

Accepted Solution

Jim Horn earned 500 total points
ID: 38375934
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?
0

LVL 28

Assisted Solution

Ryan McCauley earned 500 total points
ID: 38377444
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:

http://trycatchfinally.net/2012/03/return-a-list-of-all-dates-between-a-start-and-end-date/

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.
0

LVL 11

Author Comment

ID: 38382526
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.
0

LVL 11

Author Comment

ID: 38394081
Thanks for the replies. It hasn't resolved my issue.
0

LVL 11

Author Comment

ID: 38571774
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
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
###### Suggested Courses
Course of the Month19 days, 10 hours left to enroll