Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Consecutive Zeros

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]
Go to Top of Page

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 #tmp
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',1),('20120701',1),('20120801',0),
('20120901',0),('20121001',54),('20121101',66);

sunriser
Go to Top of Page

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.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-24 : 07:46:12
[code]
drop TABLE #tmp
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',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 MaxDate
FROM #tmp t
OUTER APPLY (SELECT MIN(dt) AS NextDate
FROM #tmp
WHERE dt > t.dt
AND reading <> t.reading
) t1
WHERE t.reading = 0
GROUP BY t.reading, t1.NextDate)
SELECT * FROM cte WHERE DATEDIFF(MM, MinDate, MaxDate)+1 = 3
[/code]

--
Chandu
Go to Top of Page

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
Go to Top of Page

sunriser
Starting Member

14 Posts

Posted - 2012-12-24 : 07:49:11
quote:
Originally posted by bandi


drop TABLE #tmp
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',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 MaxDate
FROM #tmp t
OUTER APPLY (SELECT MIN(dt) AS NextDate
FROM #tmp
WHERE dt > t.dt
AND reading <> t.reading
) t1
WHERE t.reading = 0
GROUP BY t.reading, t1.NextDate)
SELECT * FROM cte WHERE DATEDIFF(MM, MinDate, MaxDate)+1 = 3





Thanku Bandi... seems it is working fine.Thanku Very much

sunriser
Go to Top of Page

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
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-24 : 07:55:38
quote:
Originally posted by sunriser
Thanku Bandi... seems it is working fine.Thanku Very much
sunriser

Its okay.. Sunita's solution is also fine now..

--
Chandu
Go to Top of Page

sunriser
Starting Member

14 Posts

Posted - 2012-12-24 : 08:29:22
thank you so much sunitha and Bandi...

sunriser
Go to Top of Page
   

- Advertisement -