SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Consecutive Zeros
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sunriser
Starting Member

14 Posts

Posted - 12/24/2012 :  06:44:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/24/2012 :  07:22:33  Show Profile  Reply with Quote
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;
Go to Top of Page

sunriser
Starting Member

14 Posts

Posted - 12/24/2012 :  07:32:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/24/2012 :  07:40:02  Show Profile  Reply with Quote
You are right - my theory does not work in that case. will get back to you.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 12/24/2012 :  07:46:12  Show Profile  Reply with Quote

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 


--
Chandu
Go to Top of Page

sunriser
Starting Member

14 Posts

Posted - 12/24/2012 :  07:46:47  Show Profile  Reply with Quote
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 - 12/24/2012 :  07:49:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/24/2012 :  07:51:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 12/24/2012 :  07:55:38  Show Profile  Reply with Quote
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 - 12/24/2012 :  08:29:22  Show Profile  Reply with Quote
thank you so much sunitha and Bandi...

sunriser
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000