Efficient count query for consecutive time windows

Question

I have a SQL Server Database: in one table, one of the columns represents the time of an event (of type datetime2(7)). Another column represents a product ID. My goal is to specify a flexible time unit and a time window by defining a start and an end time, and count the number of unique product IDs for each time window. A product ID may thus only appear in one time-window (e.g. the first time-window in which it occurence).

An example:

    EventDateTime            ProductID 
2013-01-01 07:44:57.840         5   
2013-01-02 07:45:10.093         5   
2013-01-03 07:45:21.557         1    
2013-01-04 09:07:21.253         2  
2013-01-05 09:07:42.680         3 
2013-01-06 09:07:49.007         5 

How would the SQL-Query look like for this task? By flexible time unit I mean I would like to specify 'years', 'months', 'days' or 'hours'.

The parameters would thus be: time_start, time_end, time_unit. The result should be a table: 1 -> Count of rows with event time in the first time window 2 -> Count of rows with event time in the second time window , and so on.

Answer 1

First time occurrence is not clear

select min(EventDateTime), max(EventDateTime), count(distinct(ProductID ))
     , datepart(YY, EventDateTime), datepart(MM, EventDateTime)
     , datepart(DD, EventDateTime), datepart(HH, EventDateTime)
from table 
group by datepart(yy, EventDateTime), datepart(MM, EventDateTime)
       , datepart(DD, EventDateTime), datepart(HH, EventDateTime)