Finding consecutive zero's - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Finding consecutive zero's

Author

Topic

rjc08
Starting Member

3 Posts

Posted - 2008-11-24 : 10:02:21

I have about 8,000 rows and am looking for a query that can give the average number of consecutive zero's in each row. For example:

100 / 0 / 100 / 0 / 0 / 0/ 0 / 100 / 100 / 100 / 0 / ... so on

In this example I have 6 zero's, but only have 4 in a row, that is what i'm interested in. So if the next row has 5 consecutive zero's then the query would give me a result of 4.5 for the two rows. I need the query to not include single zero's int the average.

Can someone help me out with this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 10:05:10

SQL Server 2005?
See http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 10:18:03

[code]DECLARE @Sample TABLE
(
rowID INT IDENTITY(1, 1),
col0 INT,
col1 INT,
col2 INT,
col3 INT,
col4 INT,
col5 INT,
col6 INT,
col7 INT,
col8 INT,
col9 INT
)

INSERT @Sample
SELECT 100, 0, 100, 0, 0, 0, 0, 100, 100, 0 UNION ALL
SELECT 100, 0, 100, 100, 100, 0, 0, 0, 0, 0

DECLARE @Stage TABLE
(
rowID INT,
seqID INT,
grp INT,
PRIMARY KEY CLUSTERED
(
rowID,
seqID
)
)

INSERT @Stage (rowID, seqID)
SELECT rowID,
seqID
FROM (
SELECT u.rowID,
ROW_NUMBER() OVER (PARTITION BY u.rowID ORDER BY u.theCol) AS seqID,
u.theValue
FROM @Sample AS s
UNPIVOT (
theValue
FOR theCol IN (s.col0, s.col1, s.col2, s.col3, s.col4, s.col5, s.col6, s.col7, s.col8, s.col9)
) AS u
) AS d
WHERE theValue = 0

DECLARE @rowID INT,
@seqID INT,
@grp INT

SELECT TOP 1 @rowID = rowID,
@seqID = seqID,
@grp = 1
FROM @Stage
ORDER BY rowID,
seqID

UPDATE @Stage
SET @grp = grp = CASE WHEN @rowID = rowID AND @seqID = seqID - 1 THEN @grp + 1 ELSE 1 END,
@rowID = rowID,
@seqID = seqID

SELECT AVG(theCons)
FROM (
SELECT 1.0E * MAX(grp) AS theCons
FROM @Stage
GROUP BY rowID
) AS d[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources