question

I hope that I understand the requirements correctly. There is a table with 2 columns of interest: **id\_no** int and **issue\_date** datetime. There might be many records for the same **id\_no** and the **issue\_date** values maybe for the same or different dates. The question is to write a query to find the best 23 day period for each **id\_no** based on the number of records within the period. For example, if records with a specific **id\_no** has a single entry for each day in January except 3rd, 15th and 25th and 30th when it has 2 records for each day then the best period for this id_no would be from January 3rd to January 25th as this is the only period which will include 3 of the 4 available spikes. The query in this solution depends on the heap created on the following idea: it has one record for each potential period specifying first and last date of the period. For example, if the table in question has records from January 1st until August 31st then the heap will have 221 records defining periods like this: - first period from 2010-01-01 at midnight to 2010-01-23 23:59:59.997 - second period from 2010-01-02 at midnight to 2010-01-24 23:59:59.997 - etc... - 221st period from 2010-08-09 at midnight to 2010-08-31 23:59:59.997 So each period includes 23 days - 3 milliseconds to avoid errors associated with miscalculating results should the issue\_date of any record in the table in question have 00:00:00.000 as its respective time component. The reason why 3 milliseconds are chosen are explained in one of my previous answers which includes dissection of datetime internals: [ http://ask.sqlservercentral.com/questions/16420/php-with-mssql-strtotime-with-mssql-datetime-column][1] The table is interrogated using brutal force cross apply for each 23 days period. One of the columns returned from this cross apply is row_number() over(partition by id_no order by id_no, item_count desc) What this does is that it assigns the record number of aggregated by id_no data and restarts the numbering the moment this id changes. This allows record with highest items for each id to be numbered as 1, and this is the key to this solution. Here is the final script: declare @start_date datetime; declare @end_date datetime; declare @n int; select @start_date = dateadd(day, datediff(day, 0, min(issue_date)), -1), @n = datediff(day, min(issue_date), max(issue_date)) - 21 from dbo.SampleTable; ;with records (first_date, last_date, id_no, item_count, num) as ( select period.first_date, period.last_date, t.id_no, t.item_count, row_number() over(partition by id_no order by id_no, item_count desc ) num from ( select top (@n) dateadd(day, row_number() over (order by [object_id]), @start_date) first_date, dateadd(millisecond, -3, dateadd(day, row_number() over (order by [object_id]) + 23, @start_date)) last_date from sys.all_columns ) period cross apply ( select id_no, count(1) item_count from dbo.SampleTable where issue_date between period.first_date and period.last_date group by id_no ) t ) select first_date, last_date, id_no, item_count from records where num = 1 order by id_no; Please change the table name and column names as needed. Since I don't have any sample data for this solution, please let me know whether the query performance is acceptable. Oleg P.S. This solution will work only if you have SQL Server 2005 or better with compat 90 or better. [1]: http://ask.sqlservercentral.com/questions/16420/php-with-mssql-strtotime-with-mssql-datetime-column