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).
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.