Thanks everyone.

David I took your advice and create a Rep table and then used the query that you posted, worked great

I didn't have a separate rep table. I thought about making one but I think I was able to figure out. This is the code I used:

...

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...

Like this?:

;WITH MyCTE

AS

(

SELECT row_number() over(partition by customer_id, part_id order by effective_date) As RW,*

FROM CUST_PRICE_EFFECT

)

SELECT * FROM MyCTE T1

INNER JOIN MyCTE T2

ON T1.Customer_Id = T2.CUSTOMER_ID

AND T1.PART_ID = T2.PART_ID

AND T1.ROWID...

Hey Lowell, I think that 2nd query you gave me will work, but how to do I exclude the first row? The first row will always have a discontinue...

Thanks Lowell I will try that! I think I have the topic in the wrong spot, the database is a 2008r2 database, I put my question in 2012 T-SQL....

Thanks for the reply Jason! Unfortunately I get an error with the LEAD function that it isn't a recognized built-in function name. I had thought about that too...