;With Sales(mnth, qty)
AS
	(SELECT  * FROM(VALUES ('20071201', 100),
				('20080101', 110),
				('20080201', 120),
				('20080301', 130),
				('20080401', 140),
				('20080501', 140),
				('20080601', 130),
				('20080701', 120),
				('20080801', 110),
				('20080901', 100),
				('20081001', 110),
				('20081101', 100),
				('20081201', 120),
				('20090101', 130),
				('20090201', 140),
				('20090301', 100),
				('20090401', 100),
				('20090501', 100),
				('20090601', 110),
				('20090701', 120),
				('20090801', 110),
				('20090901', 120),
				('20091001', 130),
				('20091101', 140),
				('20091201', 100))x(mnth, qty)
)

, aCTE
AS (
	SELECT 
		mnth
		,qty
		,LAG(qty,1,qty) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) as LastQty
		,LAG(mnth,1,mnth) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) as LastMnth

		,LEAD(qty,1,qty) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) as NextQty
		,LEAD(mnth,1,mnth) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) as NextMnth

		,CASE WHEN qty >  LAG(qty,1,NULL) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) 
					OR LAG(qty,1,NULL) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) IS NULL THEN 1 
			  WHEN qty =  LAG(qty,1,NULL) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) THEN 0
			  WHEN qty <  LAG(qty,1,NULL) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) THEN -1
				ELSE NULL END AS Tnd

		,CASE WHEN qty >  LAG(qty,1,NULL) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth)
				OR LAG(qty,1,NULL) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) IS NULL  THEN 'UP' 
			WHEN qty =  LAG(qty,1,NULL) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) THEN 'SAME'
			WHEN qty <  LAG(qty,1,NULL) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) THEN 'DOWN'
				ELSE 'UNKOWN' END AS TndStr	
		,YEAR(mnth) as [YEAR]	
	FROM SALES )




select 
	distinct
		LEFT(COALESCE(StartRange,StartRangeSecond),6) as Start_Range
		,LEFT(COALESCE(EndRange,StartRange,StartRangeSecond),6) as End_Range
		,TndStr
		
from aCTE AS A

	outer apply
	(
		SELECT TOP (1)
			B.NextMnth as StartRange
		FROM
			aCTE as B
		WHERE 
			A.Mnth > = B.Mnth
			AND A.TndStr<>B.TndStr
			AND A.[Year]=B.[Year]
		ORDER BY
			B.Mnth DESC )N

	outer apply
	(
		SELECT TOP (1)
			C.LastMnth as  EndRange
		FROM
			aCTE as C
		WHERE 
			A.Mnth < = C.Mnth
			AND A.TndStr <> C.TndStr
			AND A.[Year]=C.[Year]
		ORDER BY
			C.Mnth  ASC )Y

	outer apply
	(
		SELECT TOP (1)
			B.Mnth as StartRangeSEcond
		FROM
			aCTE as B
		WHERE 
			A.Mnth > = B.Mnth
			AND A.TndStr = B.TndStr
			AND A.[Year]=B.[Year]
		ORDER BY
			B.Mnth ASC )R

Start_Range	End_Range	TndStr
200712	200712	UP
200801	200804	UP
200805	200805	SAME
200806	200809	DOWN
200810	200810	UP
200811	200811	DOWN
200812	200812	UP
200901	200902	UP
200903	200903	DOWN
200904	200905	SAME
200906	200907	UP
200908	200908	DOWN
200909	200911	UP
200912	200912	DOWN