| Author |
 Topic  |
|
|
sunriser
Starting Member
14 Posts |
 Posted - 12/24/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/24/2012 : 07:22:33
|
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; |
 |
|
|
sunriser
Starting Member
14 Posts |
Posted - 12/24/2012 : 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 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/24/2012 : 07:40:02
|
| You are right - my theory does not work in that case. will get back to you. |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 12/24/2012 : 07:46:12
|
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 |
|
|
|
sunriser
Starting Member
14 Posts |
Posted - 12/24/2012 : 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 - 12/24/2012 : 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 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/24/2012 : 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
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 12/24/2012 : 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 |
|
|
|
sunriser
Starting Member
14 Posts |
Posted - 12/24/2012 : 08:29:22
|
thank you so much sunitha and Bandi...
sunriser |
|
|
| |
Topic |
|
|
|
Consecutive Zeros
