• tfendt (3/26/2015)


    This works :). One thing I forgot to mention however is the fees are dynamic and not just limited to BFee and Holding, is it possible to modify it to account for this?

    If the FEES are dynamic, I presume you'd like to see a separate column in the results set for each?

    You would then need to build the query up using Dynamic SQL, including as many:

    - OUTER APPLYs as there are fee types

    - Columns as needed to report each fee type

    I know it sounds complicated but I suspect it really wouldn't be too bad.

    tfendt (3/26/2015)


    Also, this database will be moving to SQL 2012 in a few months, you mentioned it was easier there.

    I may have been wrong about that. It was just an initial impression. After I wrote the code I rethought it and off the top of my head it might not be so helpful. LAG and LEAD initially came to mind but they may have to look back a variable number of rows to pick up the appropriate FEE. Maybe. I'd have to play around with it to be sure.

    Also one thing I forgot to mention. The first OUTER APPLY (on BasePrice) could be a CROSS APPLY if you're sure that you're not reporting on Fees only.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St