SQL keeping count of occurrences through a sliding window

Question

In the previous question (Please refer to: SQL Keeping count of occurrences) I needed to count the number of occurrences of a variable. The code provided was as follows: ``` SELECT [Date], Code, [Count] = COUNT(*) OVER (PARTITION BY Code ORDER BY [Date] ROWS UNBOUNDED PRECEDING) FROM dbo.YourTable ORDER BY [Date]; ``` However, now I need to introduce an improvement to that code: Let's say that I have the following table: ``` Date | Code ------------------------ 2010/01/01 | 25 2010/01/01 | 22 2010/01/01 | 23 2010/01/01 | 25 2010/01/02 | 23 2010/01/02 | 23 2010/01/03 | 23 2010/01/04 | 23 2010/01/05 | 23 2010/01/06 | 23 2010/01/07 | 23 ..... 2013/03/02 | 21 ``` Now, I need to count the number of occurrences in a specific period of time. The desired output would be as follows (supposing a time frame of n=2 days, for the sake of simplicity) ``` Date | Code | Occurrences ------------------------------------ 2010/01/01 | 25 | 1 2010/01/01 | 22 | 1 2010/01/01 | 23 | 1 2010/01/01 | 25 | 2 201…

Accepted Answer

Use option with CTE, CROSS APPLY operator and ROW_NUMBER ranking function

```
 ;WITH cte AS
 (
  SELECT [Date], Code
  FROM dbo.YourTable  
  GROUP BY [Date], Code
  )
  SELECT c.Date, c.Code, o.Occurrences
  FROM cte c 
    CROSS APPLY (
                 SELECT t2.[Date], t2.Code,
                        ROW_NUMBER() OVER(PARTITION BY c.[Date] ORDER BY t2.[Date]) AS Occurrences        
                 FROM dbo.YourTable t2
                 WHERE c.Code = t2.Code
                   AND DATEDIFF(day, t2.[Date], c.[Date]) BETWEEN 0 AND 1
                 ) o
  WHERE c.Code = o.Code AND c.[Date] = o.[Date] 
  ORDER BY c.[Date]
```

Demo on SQLFiddle

For improving performance use this index

```
CREATE INDEX x ON dbo.YourTable([Date], Code)
```

SQL keeping count of occurrences through a sliding window

Solution

Problem