Check for consecutive 12 weeks of sales 0

Question

I have a table with customer_number, week and sales. I need to check if there were 12 consecutive weeks without sales for each customer and create a 0/1 flag.

I can check the last 12 weeks or a specific time interval, but what is the best way to check consecutive runs? Here is the code that I still have:

select * from weekly_sales 
 where customer_nbr in (123, 234)
 and  week < '2015-11-01'
 and week > '2014-11-01'
 order by customer_nbr, week
;

Answer 1

Demo Sql Fiddle

Here is a simplified version needed only week_idandsales

SELECT S1.weekid start_week, MAX(S2.weekid) end_week, SUM (S2.sales)
FROM Sales  S1
JOIN Sales  S2 
  ON  S2.weekid BETWEEN S1.weekid and S1.weekid + 11
WHERE S1.weekid BETWEEN 1 and 25     -- your search range
GROUP BY S1.weekid

Let me know if this works for you.

OUTPUT

| start_week | end_week |    |
|------------|----------|----|
|          1 |       12 | 12 |
|          2 |       13 |  8 |
|          3 |       14 |  3 |
|          4 |       15 |  2 |
|          5 |       16 |  0 |   <- 
|          6 |       17 |  0 |   <- no sales for 12 week
|          7 |       18 |  0 |   <-
|          8 |       19 |  4 | 
|          9 |       20 |  9 |
|         10 |       21 | 11 |
|         11 |       22 | 15 |
|         12 |       23 | 71 |
|         13 |       24 | 78 |
|         14 |       25 | 86 | 
|         15 |       25 | 86 |    < - less than 12 week range
|         16 |       25 | 86 |    < - below this line
|         17 |       25 | 86 |   
|         18 |       25 | 86 |
|         19 |       25 | 86 |
|         20 |       25 | 82 |
|         21 |       25 | 77 |
|         22 |       25 | 75 |
|         23 |       25 | 71 |
|         24 |       25 | 15 |
|         25 |       25 |  8 |

Your final request must have

  HAVING SUM (S2.sales) = 0
     AND COUNT(*) = 12

Answer 2

Ummmmm... "" "", " ()" . 0