---
title: "SQL keeping count of occurrences through a sliding window"  
description: "SQL keeping count of occurrences through a sliding window"  
author: "Pravesh Singh"  
published: 2013-04-09  
updated: 2013-04-09  
canonical: https://www.mindstick.com/forum/742/sql-keeping-count-of-occurrences-through-a-sliding-window  
category: "mssql server"  
tags: ["mssql server"]  
reading_time: 2 minutes  

---

# SQL keeping count of occurrences through a sliding window

Hi Everyone!\
In the [previous](https://yourviews.mindstick.com/view/81421/unlock-2-0-is-just-like-previous-corona-lockdowns) [question](https://www.mindstick.com/blog/23175/how-to-solve-neet-question-paper-in-less-time) (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](https://www.mindstick.com/forum/160534/orderby-then-select-vs-select-then-orderby-performance) [Date], Code, [Count] = COUNT(*) OVER ([PARTITION](https://yourviews.mindstick.com/view/87414/who-was-responsible-for-the-1947-partition-and-massacre-gandi-nehru-or-jinnah) BY Code ORDER BY [Date] ROWS UNBOUNDED PRECEDING)FROM dbo.YourTableORDER BY [Date];However, now I need to introduce an [improvement](https://answers.mindstick.com/qa/32074/which-state-introduces-land-improvement-schemes-act-bill-to-empower-farmers-in-state) 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 | 21Now, I need to count the number of occurrences in a specific [period](https://yourviews.mindstick.com/story/2247/some-great-indian-dynasties-before-mughal-period) of time. The desired [output](https://www.mindstick.com/interview/34427/explain-the-output-in-angular) 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 | 1That 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](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server) 2012.\
[Thank you in advance](https://answers.mindstick.com/qa/40482/why-was-1968-a-year-of-tension-and-turmoil-list-10-events-1-being-the-best-and-10-the-worst-thank-you-in-advance).

## Replies

### Reply by AVADHESH PATEL

Hi Pravesh!\
Please try way:\
SELECT *, ROW_NUMBER() OVER (PARTITION BY Code ORDER BY Code) Occurrences FROM YourTableORDER BY Occurrences


---

Original Source: https://www.mindstick.com/forum/742/sql-keeping-count-of-occurrences-through-a-sliding-window

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.