;WITH cte AS (
	SELECT name, 
		DATEADD(mm, - ROW_NUMBER() OVER (ORDER BY name, saledate), saledate) dategroup,
		saledate
	FROM #sales 
	WHERE  quantity > 2
	)
SELECT name, MIN(saledate) firstsaledate, COUNT(*) cnt
FROM cte
GROUP BY name, dategroup
ORDER BY name, dategroup

;WITH cte AS (
	SELECT name, 
		DATEADD(mm, - ROW_NUMBER() OVER (ORDER BY name, saledate), saledate) dategroup,
		saledate
	FROM #sales 
	WHERE quantity > 2  and saledate >= '2012-01-01'
	)
SELECT name, max(saledate), COUNT(*)
FROM cte
GROUP BY name, dategroup
HAVING COUNT(*) > 1 
ORDER BY name, dategroup

;with m2_cte_f (name,saledate,quantity,ind) as (
select s.*, 0 as ind
from #sales s
where s.saledate='2012-01-01'
union all
select s.*, case when s.quantity > 2 and sc.quantity > 2 then 1 else 0 end as ind
from #sales s
inner join m2_cte_f sc 
on (s.saledate = dateadd(month,1,sc.saledate) and s.name=sc.name)
where sc.ind = 0 
)
select * from m2_cte_f where ind=1 

(3 row(s) affected)
Table '#sales____00000000009F'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(3 row(s) affected)
Table 'Worktable'. Scan count 2, logical reads 91, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#sales____00000000009F'. Scan count 2, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

INSERT INTO #sales (name, saledate)
SELECT *
FROM
	(SELECT * FROM (VALUES('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T')) as sales(name)) names,
	(SELECT TOP 156 dateadd(mm, N, '1999-12-01') saledate FROM Tally) as months 

UPDATE #sales
SET quantity = RAND(Checksum(Newid())) * 5

CREATE CLUSTERED INDEX SALES_IDX1 ON #sales (saledate, name)

(22 row(s) affected)
Table '#sales__0000000000A4'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(15 row(s) affected)
Table 'Worktable'. Scan count 2, logical reads 635, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#sales__0000000000A4'. Scan count 96, logical reads 193, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 3 ms.

(27 row(s) affected)
Table '#sales__0000000000AD'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(19 row(s) affected)
Table 'Worktable'. Scan count 2, logical reads 1193, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#sales__0000000000AD'. Scan count 189, logical reads 380, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 5 ms.

USE tempdb
GO
IF OBJECT_ID('TestTbl') IS NOT NULL
	DROP TABLE TestTbl

CREATE TABLE Testtbl (id INT PRIMARY KEY) 

INSERT INTO Testtbl
(
	id
)
SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY c.object_id) id FROM sys.[columns] c ,sys.[columns] c2

DELETE FROM TestTbl WHERE id IN(SELECT top 100 ABS(CHECKSUM(NEWID())%1000) FROM sys.[columns] c)

DELETE FROM TestTbl WHERE id IN(SELECT top 100 ABS(CHECKSUM(NEWID())%1000) FROM sys.[columns] c)

SELECT * FROM TestTbl;

WITH S AS (
SELECT ROW_NUMBER() OVER (order by t.id) AS RN,t.id 
FROM  TestTbl t LEFT OUTER JOIN TestTbl t2
 ON t.id -1= t2.id
 WHERE t2.id IS NULL
 ),E AS (
SELECT ROW_NUMBER() OVER (order by t.id) AS RN, t.id 
FROM TestTbl t LEFT OUTER JOIN TestTbl t2
 ON t.id +1= t2.id
WHERE t2.id IS NULL
 )

SELECT s.id AS [START], e.id AS [END] FROM S INNER JOIN E ON s.Rn= E.rn 
GO

USE tempdb
GO

IF OBJECT_ID('sales') IS NOT NULL
	DROP TABLE sales
CREATE TABLE sales(
[name] [varchar](50) NOT NULL,
[saledate] [datetime] NULL,
[quantity] [int] NULL
)
GO
Insert into sales(name,saledate,quantity)
values
('A','2012-01-01',1),('A','2012-02-01',2),('A','2012-03-01',3),
('A','2012-04-01',4),('A','2012-05-01',5),('A','2012-06-01',6),

('B','2012-01-01',6),('B','2012-02-01',2),('B','2012-03-01',3),
('B','2012-04-01',4),('B','2012-05-01',1),('B','2012-06-01',6),

('C','2012-01-01',6),('C','2012-02-01',1),('C','2012-03-01',3),
('C','2012-04-01',1),('C','2012-05-01',4),('C','2012-06-01',1),

('D','2012-01-01',6),('D','2012-02-01',3),('D','2012-03-01',3),
('D','2012-04-01',4),('D','2012-05-01',1),('D','2012-06-01',6);
WITH Fil AS (
                SELECT *
                FROM   sales
                WHERE  quantity > 2
            ), 	 
S AS (
         SELECT ROW_NUMBER() OVER (ORDER BY t.name) AS RN, t.NAME, MONTH (t.saledate) AS id
         FROM   Fil t
                LEFT OUTER JOIN Fil t2
                     ON  MONTH (t.saledate) -1 = MONTH (t2.saledate) AND
                         t2.name = t.name
         WHERE  t2.saledate IS NULL
     ),
E AS (
         SELECT ROW_NUMBER() OVER (ORDER BY t.name) AS RN, t.NAME, MONTH (t.saledate) AS id
         FROM   Fil t
                LEFT OUTER JOIN Fil t2
                     ON  MONTH (t.saledate) + 1 = MONTH (t2.saledate) AND
                         t2.name = t.name
         WHERE  t2.saledate IS NULL
     ),
Gap AS(
          SELECT e.NAME, s.id AS [DateStart], e.id AS [DateEnd]
          FROM   S
                 INNER JOIN E
                      ON  s.Rn = E.rn 
                      --AND s.id<>e.id            
      )

SELECT g.NAME ,g.Datestart, sum(CASE WHEN(sales.quantity>2) AND MONTH(sales.saledate) BETWEEN g.datestart AND g.dateend THEN 1 ELSE 0 END ) as Res
FROM sales INNER JOIN gap g ON sales.name =g.NAME  AND month(sales.saledate)>= g.datestart 
GROUP BY g.name,g.Datestart
ORDER BY g.name,g.Datestart