matzj (2/19/2015)
Hey everyone thanks again for all the responses! I was able to get this working late yesterday and wanted to share my code. here is what I did:SELECT
ROWID,
CUSTOMER_ID,
PART_ID,
EFFECTIVE_DATE,
DISCONTINUE_DATE
FROM CUST_PRICE_EFFECT
WHERE DISCONTINUE_DATE > (SELECT TOP 1 EFFECTIVE_DATE
FROM CUST_PRICE_EFFECT AS sub
WHERE sub.CUSTOMER_ID = CUST_PRICE_EFFECT.CUSTOMER_ID AND sub.PART_ID = CUST_PRICE_EFFECT.PART_ID
AND sub.EFFECTIVE_DATE > CUST_PRICE_EFFECT.EFFECTIVE_DATE
ORDER BY EFFECTIVE_DATE)
ORDER BY CUST_PRICE_EFFECT.PART_ID
Thanks for posting that. You'd be surprised at how many people come to this forum seeking help, get it and then never get back to us letting us know what worked for them.
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