How to find N consecutive records in a table using SQL

Question

I have the following definition of a table with sample data. In the following table, product and customer date are key fields.

Table One Customer Product Date SALE XA 01/01/2010 YES XA 02/01/2010 YES XA 03/01/2010 NO XA 04/01/2010 NO XA 05/01/2010 YES XA 06/01/2010 NO XA 07/01/2010 NO XA 08/01/2010 NO XA 09/01/2010 YES XA 10/01/2010 YES XA 11/01/2010 NO XA 12/01/2010 YES 

In the above table, I need to find N or> N consecutive records where there was no sale, the sale value was "NO", For example, if N is 2, the result set will return the following

  Customer Product Date SALE XA 03/01/2010 NO XA 04/01/2010 NO XA 06/01/2010 NO XA 07/01/2010 NO XA 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 the ROW_NUMBER () AND PARTITION clauses, but no luck. Thanks for any help

Answer 1

You need to map the table to itself, as if there were 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 

Please note that I performed the query in the postgresql database - maybe the syntax is different from the ms-sql server, maybe the alias "FROM one AS o1", maybe you can not add / subtract this way.

Answer 2

A different approach, inspired by the last line of munchs.

Get - for this date, the first date from YES is later than this and the last date from YES is earlier than this. They form the border where our dates must correspond.

 SELECT (o1.datum), MAX (o3.datum) - MIN (o2.datum) AS diff FROM one o1, one o2, one o3 WHERE o1.sale = 'NO' AND o3.datum < (SELECT MIN (datum) FROM one WHERE datum >= o1.datum AND SALE = 'YES') AND o2.datum > (SELECT MAX (datum) FROM one WHERE datum <= o1.datum AND SALE = 'YES') GROUP BY o1.datum HAVING MAX (o3.datum) - MIN (o2.datum) >= 2 ORDER BY o1.datum; 

Maybe he needs some kind of optimization, because the first is 5 times related to the request. :)

Answer 3

Ok, we need a variable answer. We are looking for a date where we have N of the following dates, and the sales field is NO.

 SELECT d1.datum FROM one d1, one d2, i WHERE d1.sale = 'NO' AND d2.sale = 'NO' AND d1.datum = (d2.datum - i) AND i > 0 AND i < 4 GROUP BY d1.datum HAVING COUNT (*) = 3; 

This will give us the date we use for the subquery.

Notes:

• I used 'datum' instead of date, because date is the reserved keyword postgresql.

• In Oracle, you can use a dummy virtual table containing everything you ask, for example SELCT foo FROM dual WHERE foo in (1, 2, 3); which will give you 1, 2, 3, if I remember correctly. Depending on the provider, there may be other tricks to get the sequence from 1 to N. I created a table I with a column I and filled it with values ​​from 1 to 100, and I expect that N will not exceed 100; Starting with several versions, postgresql contains a function "generate_series (from, to)), which will also solve the problem, and may be similar to the solutions for your specific database. But the table I have to work as a third-party provider.

• if N == 17, you need to change 3 places from 3 to 17.

The final request will be:

 SELECT o4.* FROM one o3, one o4 WHERE o3.datum = ( SELECT d1.datum FROM one d1, one d2, i WHERE d1.sale = 'NO' AND d2.sale = 'NO' AND d1.datum = (d2.datum - i) AND i > 0 AND i <= 3 GROUP BY d1.datum HAVING COUNT (*) = 3) AND o4.datum <= o3.datum + 3 AND o4.datum >= o3.datum; customer | product | datum | sale ----------+---------+------------+------ X | A | 2010-02-06 | NO X | A | 2010-02-07 | NO X | A | 2010-02-08 | NO X | A | 2010-02-09 | NO 

Answer 4

Thanks to everyone who posted your solution. I thought I would also share my decision with everyone. Like FYI, I got this solution from another member of the SQL Server Central forum. I am definitely not going to take credit for this decision.

 DECLARE @CNT INT SELECT @CNT = 3 SELECT * FROM ( SELECT [Customer], [Product], [Date], [Sale], groupID, COUNT(*) OVER (PARTITION BY [Customer], [Product], [Sale], groupID) AS groupCnt FROM ( SELECT [Customer], [Product], [Date], [Sale], ROW_NUMBER() OVER (PARTITION BY [Customer], [Product] ORDER BY [Date]) - ROW_NUMBER() OVER (PARTITION BY [Customer], [Product], [Sale] ORDER BY [Date]) AS groupID FROM [TableSales] ) T1 ) T2 WHERE T2.[Sale] = 'NO' AND T2.[groupCnt] >= @CNT