SQL query to identify seasonal sales items

Question

I need a SQL query that will identify seasonal sales items.

My table has the following structure -

ProdId    WeekEnd     Sales
234       23/04/09    543.23
234       30/04/09     12.43
432       23/04/09      0.00
etc

I need a SQL query that will return all ProdId's that have 26 weeks consecutive 0 sales. I am running SQL server 2005. Many thanks!

Update: A colleague has suggested a solution using rank() - I'm looking at it now...

Answer 1

SELECT DISTINCT
  s1.ProdId
FROM (
    SELECT
      ProdId,
      ROW_NUMBER() OVER (PARTITION BY ProdId ORDER BY WeekEnd) AS rownum,
      WeekEnd
    FROM Sales
    WHERE Sales <> 0
) s1
INNER JOIN (
    SELECT
      ProdId,
      ROW_NUMBER() OVER (PARTITION BY ProdId ORDER BY WeekEnd) AS rownum,
      WeekEnd
    FROM Sales
    WHERE Sales <> 0
) s2
ON s1.ProdId = s2.ProdId
AND s1.rownum + 1 = s2.rownum
AND DateAdd(WEEK, 26, s1.WeekEnd) = s2.WeekEnd;

Answer 2

Here's my version:

DECLARE @NumWeeks int
SET @NumWeeks = 26

SELECT s1.ProdID, s1.WeekEnd, COUNT(*) AS ZeroCount
FROM Sales s1
INNER JOIN Sales s2
    ON s2.ProdID = s1.ProdID
    AND s2.WeekEnd >= s1.WeekEnd
    AND s2.WeekEnd <= DATEADD(WEEK, @NumWeeks + 1, s1.WeekEnd)
WHERE s1.Sales > 0
GROUP BY s1.ProdID, s1.WeekEnd
HAVING COUNT(*) >= @NumWeeks

Now, this is making a critical assumption, namely that there are no duplicate entries (only 1 per product per week) and that new data is actually entered every week. With these assumptions taken into account, if we look at the 27 weeks after a non-zero sales week and find that there were 26 total weeks with zero sales, then we can deduce logically that they had to be 26 consecutive weeks.

Note that this will ignore products that had zero sales from the start; there has to be a non-zero week to anchor it. If you want to include products that had no sales since the beginning, then add the following line after `WHERE s1.Sales > 0':

OR s1.WeekEnd = (SELECT MIN(WeekEnd) FROM Sales WHERE ProdID = s1.ProdID)

This will slow the query down a lot but guarantees that the first week of "recorded" sales will always be taken into account.