select * from t
where num in (
select a.num
from (
select num,count(*) fc
from (
select a.date,a.num
from t as a
left join t as b on a.num=b.num and a.date=b.date+1
where b.date is null
) a
group by num
having count(*)=1
) a
inner join (
select num,count(*) fc
from (
select a.date,a.num
from t as a
left join t as b on a.num=b.num and a.date=b.date-1
where b.date is null
) a
group by num
having count(*)=1
) b on a.num=b.num
)
where num in (
select a.num
from (
select num,count(*) fc
from (
select a.date,a.num
from t as a
left join t as b on a.num=b.num and a.date=b.date+1
where b.date is null
) a
group by num
having count(*)=1
) a
inner join (
select num,count(*) fc
from (
select a.date,a.num
from t as a
left join t as b on a.num=b.num and a.date=b.date-1
where b.date is null
) a
group by num
having count(*)=1
) b on a.num=b.num
)
WITH T(Date,Num,Amount)
AS (
SELECT Convert(datetime,'2014-08-06',120),'a111',100000 UNION ALL
SELECT '2014-08-07','a111',100000 UNION ALL
SELECT '2014-08-09','a111',2100 UNION ALL
SELECT '2014-08-10','a111',4900 UNION ALL
SELECT '2014-08-12','a111',150000 UNION ALL
SELECT '2014-08-13','a111',50000 UNION ALL
SELECT '2014-08-04','a123',2000000 UNION ALL
SELECT '2014-08-06','a123',200000 UNION ALL
SELECT '2014-08-04','a123',10000 UNION ALL
SELECT '2014-08-04','a123',200000 UNION ALL
SELECT '2014-08-15','a123',199900 UNION ALL
SELECT '2014-08-15','a123',10000 UNION ALL
SELECT '2010-03-31','a45000',30000 UNION ALL
SELECT '2014-08-06','a777',500004 UNION ALL
SELECT '2014-08-07','a777',10000000 UNION ALL
SELECT '2014-08-08','a777',499990 UNION ALL
SELECT '2014-08-09','a777',10000009 UNION ALL
SELECT '2014-08-06','a777',20980.38 UNION ALL
SELECT '2014-08-06','a777',908.27 UNION ALL
SELECT '2014-08-06','a777',5000 UNION ALL
SELECT '2014-08-06','a789111',1500000 UNION ALL
SELECT '2014-08-06','a789111',500000 UNION ALL
SELECT '2014-08-06','a789112',300000 UNION ALL
SELECT '2014-08-06','a789112',40000 UNION ALL
SELECT '2014-08-06','a789113',50000 UNION ALL
SELECT '2014-08-07','a789113',300 UNION ALL
SELECT '2014-08-08','a789113',1500.89 UNION ALL
SELECT '2014-08-06','a789114',3000 UNION ALL
SELECT '2014-08-06','a789114',1000 UNION ALL
SELECT '2014-08-06','a789114',5000 UNION ALL
SELECT '2014-08-06','a789115',6109.23 UNION ALL
SELECT '2014-08-06','a789115',8908.23 UNION ALL
SELECT '2014-08-06','a789115',10000 UNION ALL
SELECT '2014-08-07','a789116',180000 UNION ALL
SELECT '2014-08-08','a789116',180000 UNION ALL
SELECT '2014-08-09','a789116',180000 UNION ALL
SELECT '2014-08-10','a789116',1200000 UNION ALL
SELECT '2014-08-12','a789116',1500000 UNION ALL
SELECT '2014-08-13','a789116',2000000 UNION ALL
SELECT '2014-08-01','a789116',10000 UNION ALL
SELECT '2014-08-03','a789116',10000 UNION ALL
SELECT '2014-08-15','a789116',10000 UNION ALL
SELECT '2014-08-16','a789116',10000 UNION ALL
SELECT '2014-08-17','a789116',11111111
),
D(Num,Date)
AS(
SELECT DISTINCT Num,Date
FROM T
WHERE Convert(varchar(7),Date,120)='2014-08'
),
S(Num,Date,NeighborCount)
AS(
SELECT D.Num,
D.Date,
CASE WHEN P1.Date IS NULL
THEN 0
ELSE CASE WHEN P2.Date IS NULL
THEN 1
ELSE 2
END
END +
CASE WHEN N1.Date IS NULL
THEN 0
ELSE CASE WHEN N2.Date IS NULL
THEN 1
ELSE 2
END
END
FROM D
LEFT JOIN D P1
ON P1.Num = D.Num
AND P1.Date = D.Date - 1
LEFT JOIN D P2
ON P2.Num = D.Num
AND P2.Date = D.Date - 2
LEFT JOIN D N1
ON N1.Num = D.Num
AND N1.Date = D.Date + 1
LEFT JOIN D N2
ON N2.Num = D.Num
AND N2.Date = D.Date + 2
)
--SELECT * FROM S ORDER BY NUM,DATE
SELECT T.*
FROM T
JOIN S
ON S.Num = T.Num
AND S.Date = T.Date
WHERE S.NeighborCount >= 2
Date Num Amount
----------------------- ------- ---------------------------------------
2014-08-06 00:00:00.000 a777 500004.00
2014-08-07 00:00:00.000 a777 10000000.00
2014-08-08 00:00:00.000 a777 499990.00
2014-08-09 00:00:00.000 a777 10000009.00
2014-08-06 00:00:00.000 a777 20980.38
2014-08-06 00:00:00.000 a777 908.27
2014-08-06 00:00:00.000 a777 5000.00
2014-08-06 00:00:00.000 a789113 50000.00
2014-08-07 00:00:00.000 a789113 300.00
2014-08-08 00:00:00.000 a789113 1500.89
2014-08-07 00:00:00.000 a789116 180000.00
2014-08-08 00:00:00.000 a789116 180000.00
2014-08-09 00:00:00.000 a789116 180000.00
2014-08-10 00:00:00.000 a789116 1200000.00
2014-08-15 00:00:00.000 a789116 10000.00
2014-08-16 00:00:00.000 a789116 10000.00
2014-08-17 00:00:00.000 a789116 11111111.00
AS (
SELECT Convert(datetime,'2014-08-06',120),'a111',100000 UNION ALL
SELECT '2014-08-07','a111',100000 UNION ALL
SELECT '2014-08-09','a111',2100 UNION ALL
SELECT '2014-08-10','a111',4900 UNION ALL
SELECT '2014-08-12','a111',150000 UNION ALL
SELECT '2014-08-13','a111',50000 UNION ALL
SELECT '2014-08-04','a123',2000000 UNION ALL
SELECT '2014-08-06','a123',200000 UNION ALL
SELECT '2014-08-04','a123',10000 UNION ALL
SELECT '2014-08-04','a123',200000 UNION ALL
SELECT '2014-08-15','a123',199900 UNION ALL
SELECT '2014-08-15','a123',10000 UNION ALL
SELECT '2010-03-31','a45000',30000 UNION ALL
SELECT '2014-08-06','a777',500004 UNION ALL
SELECT '2014-08-07','a777',10000000 UNION ALL
SELECT '2014-08-08','a777',499990 UNION ALL
SELECT '2014-08-09','a777',10000009 UNION ALL
SELECT '2014-08-06','a777',20980.38 UNION ALL
SELECT '2014-08-06','a777',908.27 UNION ALL
SELECT '2014-08-06','a777',5000 UNION ALL
SELECT '2014-08-06','a789111',1500000 UNION ALL
SELECT '2014-08-06','a789111',500000 UNION ALL
SELECT '2014-08-06','a789112',300000 UNION ALL
SELECT '2014-08-06','a789112',40000 UNION ALL
SELECT '2014-08-06','a789113',50000 UNION ALL
SELECT '2014-08-07','a789113',300 UNION ALL
SELECT '2014-08-08','a789113',1500.89 UNION ALL
SELECT '2014-08-06','a789114',3000 UNION ALL
SELECT '2014-08-06','a789114',1000 UNION ALL
SELECT '2014-08-06','a789114',5000 UNION ALL
SELECT '2014-08-06','a789115',6109.23 UNION ALL
SELECT '2014-08-06','a789115',8908.23 UNION ALL
SELECT '2014-08-06','a789115',10000 UNION ALL
SELECT '2014-08-07','a789116',180000 UNION ALL
SELECT '2014-08-08','a789116',180000 UNION ALL
SELECT '2014-08-09','a789116',180000 UNION ALL
SELECT '2014-08-10','a789116',1200000 UNION ALL
SELECT '2014-08-12','a789116',1500000 UNION ALL
SELECT '2014-08-13','a789116',2000000 UNION ALL
SELECT '2014-08-01','a789116',10000 UNION ALL
SELECT '2014-08-03','a789116',10000 UNION ALL
SELECT '2014-08-15','a789116',10000 UNION ALL
SELECT '2014-08-16','a789116',10000 UNION ALL
SELECT '2014-08-17','a789116',11111111
)select t.* from t,
(select num, min(date) mindate,MAX(date) maxdate from
(select DATE,num,datediff(dd,convert(datetime,date),getdate())
+ROW_NUMBER() over (partition by num order by convert(datetime,DATE)) as grp
from (select distinct date,num from t) a) m
group by num,grp) a where a.Num=t.num and t.date>=mindate and t.date<=maxdate and DATEDIFF(dd,mindate,maxdate)>=2