Author |
Topic |
sunriser
Starting Member
14 Posts |
Posted - 2012-12-24 : 06:44:31
|
Hi All,I have a requirement to find the if there are any consecutive zeros .Say for example i have to find if there are any 3 consecutive zeros in the reading.If there then i have display the date(dt column name).CREATE TABLE #tmp(dt DATETIME, reading INT);INSERT INTO #tmp VALUES ('20111101',44),('20111201',58),('20120101',45),('20120201',60),('20120301',51), ('20120401',0),('20120501',0),('20120601',0),('20120701',1),('20120801',0), ('20120901',0),('20121001',54),('20121101',66);sunriser |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-24 : 07:22:33
|
[code]SELECT dt,reading FROM (SELECT *,COUNT(*) OVER (PARTITION BY Grp) as N FROM( SELECT *, ROW_NUMBER() OVER (ORDER BY dt) -ROW_NUMBER() OVER(PARTITION BY reading ORDER BY dt) AS Grp FROM #tmp) s) s2 WHERE N >= 3;[/code] |
|
|
sunriser
Starting Member
14 Posts |
Posted - 2012-12-24 : 07:32:56
|
Thanks u sunitha,Can you please check with this input now. In this i dont have any 3 consecutive zeros. so here i should not return any. drop TABLE #tmpCREATE TABLE #tmp(dt DATETIME, reading INT);INSERT INTO #tmp VALUES ('20111101',44),('20111201',58),('20120101',45),('20120201',60),('20120301',51),('20120401',0),('20120501',0),('20120601',1),('20120701',1),('20120801',0),('20120901',0),('20121001',54),('20121101',66);sunriser |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-24 : 07:40:02
|
You are right - my theory does not work in that case. will get back to you. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-24 : 07:46:12
|
[code]drop TABLE #tmpCREATE TABLE #tmp(dt DATETIME, reading INT);INSERT INTO #tmp VALUES ('20111101',44),('20111201',58),('20120101',45),('20120201',60),('20120301',51),('20120401',0),('20120501',0),('20120601',1),('20120701',1),('20120801',0),('20120901',0),('20121001',54),('20121101',66);;with cte as (SELECT t.reading, MIN(t.dt) AS MinDate, MAX(t.dt) AS MaxDateFROM #tmp tOUTER APPLY (SELECT MIN(dt) AS NextDate FROM #tmp WHERE dt > t.dt AND reading <> t.reading ) t1WHERE t.reading = 0GROUP BY t.reading, t1.NextDate)SELECT * FROM cte WHERE DATEDIFF(MM, MinDate, MaxDate)+1 = 3 [/code]--Chandu |
|
|
sunriser
Starting Member
14 Posts |
Posted - 2012-12-24 : 07:46:47
|
quote: Originally posted by sunitabeck You are right - my theory does not work in that case. will get back to you.
Thanks for your time.. mean time i will also check.sunriser |
|
|
sunriser
Starting Member
14 Posts |
Posted - 2012-12-24 : 07:49:11
|
quote: Originally posted by bandi
drop TABLE #tmpCREATE TABLE #tmp(dt DATETIME, reading INT);INSERT INTO #tmp VALUES ('20111101',44),('20111201',58),('20120101',45),('20120201',60),('20120301',51),('20120401',0),('20120501',0),('20120601',1),('20120701',1),('20120801',0),('20120901',0),('20121001',54),('20121101',66);;with cte as (SELECT t.reading, MIN(t.dt) AS MinDate, MAX(t.dt) AS MaxDateFROM #tmp tOUTER APPLY (SELECT MIN(dt) AS NextDate FROM #tmp WHERE dt > t.dt AND reading <> t.reading ) t1WHERE t.reading = 0GROUP BY t.reading, t1.NextDate)SELECT * FROM cte WHERE DATEDIFF(MM, MinDate, MaxDate)+1 = 3
Thanku Bandi... seems it is working fine.Thanku Very muchsunriser |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-24 : 07:51:29
|
I think this works:SELECT dt,reading FROM (SELECT *,COUNT(*) OVER (PARTITION BY Grp) as N FROM( SELECT *, ROW_NUMBER() OVER (ORDER BY dt) -ROW_NUMBER() OVER(PARTITION BY CASE WHEN reading = 0 THEN 0 ELSE 1 END ORDER BY dt) AS Grp FROM #tmp) s) s2 WHERE N >= 3 AND reading = 0 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-24 : 07:55:38
|
quote: Originally posted by sunriserThanku Bandi... seems it is working fine.Thanku Very muchsunriser
Its okay.. Sunita's solution is also fine now..--Chandu |
|
|
sunriser
Starting Member
14 Posts |
Posted - 2012-12-24 : 08:29:22
|
thank you so much sunitha and Bandi...sunriser |
|
|
|
|
|