1

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.

  • 1
    Please post expected results along with sample parameters. – Dan Guzman Mar 21 '17 at 2:01
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.