November 13, 2011 at 12:30 pm
An interesting requirement where an account holder will be awarded points based on the amount spent at the retail store. There are different earning slots at which an account holder can earn points:
Slot 1 $0 - $4999 1 point for each dollar spent
Slot 2 $5000 - $99993 points for each dollar spent
Slot 3 $10000 - $499995 points for each dollar spent
Slot 4 $50000 - $9999910 points for each dollar spent
So if I open up an account today and I buy products "A", "B" and "C" in one transaction (shopping cart) and pay $500, I will get 500 points.
Next week when I buy a product "D" for $6000 my calculation will start from previous amount spend balance. For the first $4499 I will get 1 point for each dollar (already spent $500 last week, so $4999 (max amount in first slot) - $500 = $4499) and for remaining balance $6000 - $4999 = $1001, I will earn 3 points for each dollar. And this can happens for multiple products or a single product in a shopping cart, i.e. 1 product can switch slots based on the paid amount and spend balance as Product "D" did.
To add to the complexity the points need to be given for each bought product separately (shown for each product on the invoice) and not on the total shopping cart or transaction bill. (So product "D" will show both slots and points earned in each slot on the invoice like webpage)
The account remains active till 5 years and after that the spend re-starts frrestarts account holders continue to earn points for 5 years.
So each product will have to go in a while loop (trying to avoid cursors) with added last spend balance to determine which product switches from 1 slot to the other within each transaction or shopping cart.
Can this be done without looping to enhance performance (in T-SQL or preferably SSIS)? The POS system generates a flat file from where the points are calculated in a regularly scheduled batch. SSIS extracts from these files.
Thanks
G
November 13, 2011 at 1:37 pm
Please don't cross post. It just wastes peoples time and fragments replies.
No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic1204802-148-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2011 at 1:55 pm
Sorry about that. Just wanted to get replies from both SSIS and T-SQL schools of thought.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply