Post reply

How to Eliminate loop and use set based approach?

  • November 13, 2011 at 12:30 pm

    #251719

    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 - $9999 3 points for each dollar spent

    Slot 3 $10000 - $49999 5 points for each dollar spent

    Slot 4 $50000 - $99999 10 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

    #1406859

    Please don't cross post. It just wastes peoples time and fragments replies.

    No replies to this thread please. Direct replies to: http://qa.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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 13, 2011 at 1:55 pm

    #1406864

    Sorry about that. Just wanted to get replies from both SSIS and T-SQL schools of thought.

  • November 13, 2011 at 2:27 pm

    #1406870

    I think this could be accomplished with an rCTE in SQL.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply