SQL Server range count

Question

I have SQL Server table in which one of the column represents time, while another represents an ID, like this:

    Datum                     SomeID 
2017-01-01 07:44:57.840         1   
2017-01-02 07:45:10.093         2   
2017-01-02 07:45:21.557         3    
2017-01-03 09:07:21.253         2  
2017-01-05 09:07:42.680         1 
2017-01-06 09:07:49.007         5 

I am looking for an SQL Query or Stored Procedure which takes the time unit as an input (e.g. 'YY', 'MM', 'DD', 'HH') and returns a table with the unique ID count for each time interval. If no records are found for that interval, the count should be zero.

In this case, assuming 'DD' as time unit, the result should be:

Time interval      Count
2017-01-01           1
2017-01-02           2
2017-01-03           1
2017-01-04           0
2017-01-05           1
2017-01-06           1

Answer 1

I'll often use a Table-Value-Function to create dynamic Date/Time Ranges. A tally/calendar table would do the trick as well. However, the function offers a little more flexibility. Parameters are Date Range, DatePart, and Increment.

I should also note that your particular question, I use Format(). There are some great features in this function, but it is NOT know for its performance.

Example:

Declare @YourTable table (Datum datetime,SomeID int)
Insert Into @YourTable values
('2017-01-01 07:44:57.840',1),
('2017-01-02 07:45:10.093',2),   
('2017-01-02 07:45:21.557',3),    
('2017-01-03 09:07:21.253',2),  
('2017-01-05 09:07:42.680',1), 
('2017-01-06 09:07:49.007',5)

Declare @DispFormat varchar(10)='yyyy-MM-dd'   --<< Standard Format() options
Declare @DatePart   varchar(10)='DD'           --<< YY,MM,DD,HH,MI,SS
Declare @Increment  int        = 1             --<< Set Increment

Declare @DateR1 datetime
Declare @DateR2 datetime
Select @DateR1=min(Datum),@DateR2=max(Datum) from @YourTable  --<< Can be set manually if desired

Select A.TimeInterval 
      ,Count        = count(Distinct SomeID)
 From  (Select *,TimeInterval = format(RetVal,@DispFormat) From [dbo].[udf-Range-Date](@DateR1,@DateR2,@DatePart,@Increment)) A
 Left  Join @YourTable B on format(A.RetVal,@DispFormat) = format(B.Datum,@DispFormat)
 Group By  A.TimeInterval 

Returns

TimeInterval    Count
2017-01-01      1
2017-01-02      2
2017-01-03      1
2017-01-04      0
2017-01-05      1
2017-01-06      1

The UDF if interested

CREATE FUNCTION [dbo].[udf-Range-Date] (@R1 datetime,@R2 datetime,@Part varchar(10),@Incr int)
Returns Table
Return (
    with cte0(M)   As (Select 1+Case @Part When 'YY' then DateDiff(YY,@R1,@R2)/@Incr When 'QQ' then DateDiff(QQ,@R1,@R2)/@Incr When 'MM' then DateDiff(MM,@R1,@R2)/@Incr When 'WK' then DateDiff(WK,@R1,@R2)/@Incr When 'DD' then DateDiff(DD,@R1,@R2)/@Incr When 'HH' then DateDiff(HH,@R1,@R2)/@Incr When 'MI' then DateDiff(MI,@R1,@R2)/@Incr When 'SS' then DateDiff(SS,@R1,@R2)/@Incr End),
         cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
         cte2(N)   As (Select Top (Select M from cte0) Row_Number() over (Order By (Select NULL)) From cte1 a, cte1 b, cte1 c, cte1 d, cte1 e, cte1 f, cte1 g, cte1 h ),
         cte3(N,D) As (Select 0,@R1 Union All Select N,Case @Part When 'YY' then DateAdd(YY, N*@Incr, @R1) When 'QQ' then DateAdd(QQ, N*@Incr, @R1) When 'MM' then DateAdd(MM, N*@Incr, @R1) When 'WK' then DateAdd(WK, N*@Incr, @R1) When 'DD' then DateAdd(DD, N*@Incr, @R1) When 'HH' then DateAdd(HH, N*@Incr, @R1) When 'MI' then DateAdd(MI, N*@Incr, @R1) When 'SS' then DateAdd(SS, N*@Incr, @R1) End From cte2 )

    Select RetSeq = N+1
          ,RetVal = D 
     From  cte3,cte0 
     Where D<=@R2
)
/*
Max 100 million observations -- Date Parts YY QQ MM WK DD HH MI SS
Syntax:
Select * from [dbo].[udf-Range-Date]('2016-10-01','2020-10-01','YY',1) 
Select * from [dbo].[udf-Range-Date]('2016-01-01','2017-01-01','MM',1) 
*/

Just for Fun ... Try:

Declare @DispFormat varchar(10)='HH:00'
Declare @DatePart   varchar(10)='HH'   
Declare @Increment  int        = 1     

Answer 2

Probably the simplest method is dynamic SQL:

declare @timeunit nvarchar(255);
declare @sql nvarchar(max);

set timeunit = N'hh';

set @sql = '
select dateadd(@timeunit, datediff(@timeunit, 0, datum), 0) as dt, count(*)
from t
group by dateadd(@timeunit, datediff(@timeunit, 0, datum), 0) 
';

set @sql = replace(@sql, '@timeunit', @timeunit);

exec sp_executesql @sql;

I am not thrilled with putting user input into a query like this. You can readily check it for valid values, though:

(case when @timeunit in ('year', 'yy', 'month', 'mm',  . . .) then . . .
 end)