Bad Data Filtering in SQL Server 2005

Question

Using SQL Server 2005

I have a table with the following columns

identifier name Date Value

I would like to select all rows from a table where there are no four consecutive zeros by date. How can I do it? Below is an example of what I mean.

id name date value 1 a 1/1/2010 5 2 a 1/2/2010 3 3 a 1/3/2010 5 4 a 1/4/2010 0 5 a 1/7/2010 0 6 a 1/8/2010 0 7 a 1/9/2010 2 8 a 1/10/2010 3 9 a 1/11/2010 0 10 a 1/15/2010 0 11 a 1/16/2010 0 12 a 1/17/2010 0 13 a 1/20/2010 4 14 a 1/21/2010 4 

I would like the query result to include all rows except id 9-12.

Answer 1

It is assumed that you ordered the strings by ID, but you can just change the ORDER BY id to something else, and it should still work.

Using the T-SQL CTE found on this site by Kodyaz Development Resources I managed to generate the code below. It works for me, so it deletes rows where there are two consecutive zeros, not 4, when I tested it on my code and just changed the names of the tables / rows.

 WITH CTE as ( SELECT RN = ROW_NUMBER() OVER (ORDER BY id), * FROM tablename ) SELECT [Current Row].* FROM CTE [Current Row] LEFT JOIN CTE [Previous Row] ON [Previous Row].RN = [Current Row].RN - 1 LEFT JOIN CTE [Next Row] ON [Next Row].RN = [Current Row].RN + 1 WHERE not([Current Row].value = 0 AND [Next Row].value = 0) AND // this deletes the row where value is zero and the next rows value is zero not([Previous Row].value = 0 AND [Current Row].value = 0) // this deletes the row where value is zero and the previous rows value is zero 

All you have to do to get it working for your business is to put every possible combination in a WHERE . For example, dealing with this line and the next three lines equal to 0 OR this line, the previous and two next lines.

Answer 2

You do not mention how this name is used, so I assume that you want this to be done by name. I also assume that when you talk about "consecutive", what do you mean in order of dates and not in order of id. Finally, I also assume that you would also exclude 5 zeros per line, 6 zeros per line, etc.

There may be an easier way, but this should work:

 ;WITH Transitions_To_CTE AS ( SELECT T1.id, T1.name, T1.date, T1.value FROM My_Table T1 LEFT OUTER JOIN My_Table T2 ON T2.name = T1.name AND T2.date < T1.date AND T2.value <> 0 LEFT OUTER JOIN My_Table T3 ON T3.name = T1.name AND T3.date > COALESCE(T2.date, '1900-01-01') AND T3.date < T1.date WHERE T1.value = 0 AND T3.id IS NULL ), Transitions_From_CTE AS ( SELECT T1.id, T1.name, T1.date, T1.value FROM My_Table T1 LEFT OUTER JOIN My_Table T2 ON T2.name = T1.name AND T2.date > T1.date AND T2.value <> 0 LEFT OUTER JOIN My_Table T3 ON T3.name = T1.name AND T3.date < COALESCE(T2.date, '9999-12-31') AND T3.date > T1.date WHERE T1.value = 0 AND T3.id IS NULL ), Range_Exclusions AS ( SELECT S.name, S.date AS start_date, E.date AS end_date FROM Transitions_To_CTE S INNER JOIN Transitions_From_CTE E ON E.name = S.name AND E.date > S.date LEFT OUTER JOIN Transitions_From_CTE E2 ON E2.name = S.name AND E2.date > S.date AND E2.date < E.date WHERE E2.id IS NULL AND (SELECT COUNT(*) FROM dbo.My_Table T WHERE T.name = S.name AND T.date BETWEEN S.date AND E.date) >= 4 ) SELECT T.id, T.name, T.date, T.value FROM dbo.My_Table T WHERE NOT EXISTS (SELECT * FROM Range_Exclusions RE WHERE RE.name = T.name AND T.date BETWEEN RE.start_date AND RE.end_date) 

Answer 3

Here is my attempt, use recursive cte to calculate the number of consecutive zeros, then create a sequence of identifiers using a level> 4, and then just make an optional item in the identifiers.

 with trend --work out number of consecutive zeros using level as (Select 1 as level, id, value, id as startid from IdsAndValues Union All Select [Level]+1, P.ID, p.value, t.startid From IdsAndValues as p Inner Join trend as t on p.id = t.id+1 Where t.value =0 and p.value=0 ) ,IDs --create sequence of ids using startid and id, this allows us to do the not in as ( Select startid as ExcludeID ,id from trend as t-- Where level>=4 Union All Select ExcludeID +1, id From ids where ExcludeID <id ) Select * from IdsAndValues Where id Not in (Select ExcludeID from IDs)