Hi,

I've solved a problem similar to this before, and I can't for the life of me figure out how I did it.

Maybe the code below will put you (or someone) on the right track...

Good luck!

;WITH Moo AS

(

SELECT ClientID_L= T1.ClientID

,SaleDate_L= T1.SaleDate

,ClientID_R= T2.ClientID

,SaleDate_R= T2.SaleDate

FROM#Sales T1

LEFT JOIN#Sales T2 ON T1.ClientID = T2.ClientID AND DATEDIFF(MONTH, T1.SaleDate, T2.SaleDate) = 1

),

Moo2 AS

(

SELECT ClientID_L

,SaleDate_L

,sStatus =CASE

WHEN ClientID_R IS NOT NULL THEN 'Start' ELSE 'End'

END

FROM Moo

)

SELECT *

FROM Moo2

ORDER BY ClientID_L, SaleDate_L