locked
Design for Pricing Scenarios RRS feed

  • Question

  • Hello all

    I am contemplating building a small data mart to accommodate a need to price invoices according to oft-times convoluted logic.

    The pricing logic generally follows a pattern of...

    Customer --> Product --> State 

    ...however, there can be quite a few exceptions built into any of those levels (and sometimes more levels depending on customer).  

    To date to accomplish this I built customer-based queries that have a gazillion case statements each.  These are hard to write, hard to maintain, and hard to even look at.  However, I don't know a better way to do it as the logic isn't very friendly.

    Is it even possible to simplify this sort of logic by translating it into a data mart?  If so, what would the right approach be?

    Thanks for the help!


    Bonediggler

    Friday, November 16, 2012 4:39 PM

Answers

  • To date to accomplish this I built customer-based queries that have a gazillion case statements each.  These are hard to write, hard to maintain, and hard to even look at.  However, I don't know a better way to do it as the logic isn't very friendly.


    Make it table-driven instead of CASE logic driven.

    Create two lookup tables:

    PriceLookupHeader

    PriceLookup  (detail)


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    • Marked as answer by Maggie Luo Wednesday, December 5, 2012 1:54 AM
    Tuesday, December 4, 2012 12:06 PM

All replies

  • Hi Bonediggler,

    If you want to keep business logic translation into data mart then you have to parameterize those exception in tabular format.

    Can please make us update your specific logic then it will be easy to demonstrate.


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/


    Friday, November 16, 2012 8:17 PM
  • An example of the logic would be

    Case when customer = AA then
    
    Case when SubClient = x then
    	Case when state = CA then
    		Case when pricing_code = a then
    				10
    		     when pricing_code = b then
    				5
    		End
            Else
                    Case when pricing_code = a then
    				8
    		     when pricing_code = b then
    				4
    		End
           End
        
         when SubClient = y then...

    I am not sure if this helps.  Please let me know if you need more information


    Bonediggler

    Wednesday, November 21, 2012 2:25 PM
  • To date to accomplish this I built customer-based queries that have a gazillion case statements each.  These are hard to write, hard to maintain, and hard to even look at.  However, I don't know a better way to do it as the logic isn't very friendly.


    Make it table-driven instead of CASE logic driven.

    Create two lookup tables:

    PriceLookupHeader

    PriceLookup  (detail)


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    • Marked as answer by Maggie Luo Wednesday, December 5, 2012 1:54 AM
    Tuesday, December 4, 2012 12:06 PM