Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Gary8877
Starting Member
1 Post |
 Posted - 2011-11-13 : 14:39:09
|
| 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 spentSlot 2 $5000 - $9999 3 points for each dollar spentSlot 3 $10000 - $49999 5 points for each dollar spentSlot 4 $50000 - $99999 10 points for each dollar spentSo 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)The account remains active till 5 years and after that the spend re-starts from $0. So 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. The transaction and Payment details are processed for points calculations in a daily batch using SSIS. POS system spits out flat files. Can this be done without looping in SSIS to enhance performance? ThanksG |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-14 : 01:26:26
|
| One question here, what is the order in which you need consider the products for giving the points against themfor example in your case, suppose you bought product D and E for $6000 and $5000 respectively in second instance, and as per your explanation first $4499 spend will get 1 points each and next 5000 will get 3 points each. So when you report how do you consider the order of purchase(D followed by E or E followed by D). the result will be different as in first case you will report points for $4499 against D and in second case E. Or is there some field in your invoice(may be invoiceitemid ) which will determine the order of items.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
Change loop to a set based approach