Home > DeveloperSection > Forums > SQL keeping count of occurrences through a sliding window
Pravesh Singh

Total Post:411

Points:2881
Posted on    April-09-2013 2:04 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 969  View(s)
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.


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    April-09-2013 7:42 AM

Hi Pravesh!

Please try way:

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

Don't want to miss updates? Please click the below button!

Follow MindStick