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
   2010/01/02    | 23    |      2
   2010/01/02    | 23    |      3
   2010/01/03    | 23    |      3 -> We are not considering the occurence in 2011/01/01 as it is out of the scope now
   2010/01/04    | 23    |      2 -> Considers only occurrences in 01/03 and 01/04 
   2010/01/05    | 23    |      2
   2010/01/06    | 23    |      2
   2010/01/07    | 23    |      2
   .....
   2013/03/02    |  21   |      1

That is, I need to know how many times the code 'x' has appeared in my table in the last 'n' months.

This is run in SQL Server 2012.

Thank you in advance.

Answer 1

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)