How to find N Consecutive records in a table using SQL

sql, sql-server-2005

Solution

You need to match your table against itself, as if there where 2 tables. So you use two aliases, o1 and o2 to refer to your table:

SELECT DISTINCT o1.customer, o1.product, o1.datum, o1.sale
  FROM one o1, one o2
  WHERE (o1.datum = o2.datum-1 OR o1.datum = o2.datum +1)
  AND o1.sale = 'NO' 
  AND o2.sale = 'NO'; 
 customer | product |   datum    | sale 
----------+---------+------------+------
 X        | A       | 2010-01-03 | NO
 X        | A       | 2010-01-04 | NO
 X        | A       | 2010-01-06 | NO
 X        | A       | 2010-01-07 | NO
 X        | A       | 2010-01-08 | NO

Note that I performed the query on an postgresql database - maybe the syntax differs on ms-sql-server, maybe at the alias 'FROM one AS o1' perhaps, and maybe you cannot add/substract in that way.

Problem

I have the following Table definition with sample data. In the following table, Customer Product & Date are key fields ``` Table One Customer Product Date SALE X A 01/01/2010 YES X A 02/01/2010 YES X A 03/01/2010 NO X A 04/01/2010 NO X A 05/01/2010 YES X A 06/01/2010 NO X A 07/01/2010 NO X A 08/01/2010 NO X A 09/01/2010 YES X A 10/01/2010 YES X A 11/01/2010 NO X A 12/01/2010 YES ``` In the above table, I need to find the N or > N consecutive records where there was no sale, Sale value was 'NO' For example, if N is 2, the the result set would return the following ``` Customer Product Date SALE X A 03/01/2010 NO X A 04/01/2010 NO X A 06/01/2010 NO X A 07/01/2010 NO X A 08/01/2010 NO ``` Can someone help me with a SQL query to get the desired results. I am using SQL Server 2005. I started playing using ROW_NUMBER() AND PARTITION clauses but no luck. Thanks for any help

Original source