SQL KEEPING COUNT OF OCCURRENCES THROUGH A SLIDING WINDOW

Pravesh Singh

Total Post:412

Points:2888
Posted by  Pravesh Singh
 1254  View(s)
Ratings:
Rate this:
Hi Everyone!

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.
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: SQL keeping count of occurrences through a sliding window

    Hi Pravesh!

    Please try way:

    SELECT 
        *, 
        ROW_NUMBER() OVER (PARTITION BY Code ORDER BY Code) Occurrences 
    FROM YourTable
    ORDER BY Occurrences

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!