How to get how many hour 4AM on a day Tuesday have there been between two dates in TSQL

Question

I have a very specific statistic I need to get information from.

This is a follow on from this question, which just asks about 4AM, I now need to also add in that it was a Tuesday

How to get how many hour 4AM have there been between two dates in TSQL

I need to work out how many occurrences of a particular time of day on a particular day of the week there has been between two dates.

As an example, I need to get how many 4AM on a Tuesday there has been between two dates.

As an example, the following would give the result = 1 as there is 1 occurrence of 4AM on Tuesday

declare @date1 datetime = '2019-10-01 00:00:00.000';
declare @date2 datetime = '2019-10-02 00:00:00.000';

As an example, the following would give the result = 0, as there has been 0 4AMs on a Tuesday

declare @date1 datetime = '2019-10-01 05:00:00.000';
declare @date2 datetime = '2019-10-02 00:00:00.000';

As an example, the following would give the result = 2 as there has been 2 4AMs

declare @date1 datetime = '2019-10-01 03:00:00.000';
declare @date2 datetime = '2019-10-08 05:00:00.000';

As an example, the following would give the result = 2 as there has been 2 4AMs even though they are just on the 4:00AM time on two Tuesdays apart

declare @date1 datetime = '2019-10-01 04:00:00.000';
declare @date2 datetime = '2019-10-08 04:00:00.000';

I have some code to get How many days of the weeks between two dates, and how many 4AMs between two dates, but not combined.

Here is the code I have attempted, which works for 3 out of 4 of my tests.

SET DATEFIRST 1
declare @HourOfDay int = 4

declare @tests table (
    date1 datetime,
    date2 datetime
)

INSERT INTO @tests VALUES
('2019-10-01 00:00:00.000', '2019-10-02 00:00:00.000'), -- returns 1 CORRECT
('2019-10-01 05:00:00.000', '2019-10-02 00:00:00.000'), -- returns 1 INCORRECT
('2019-10-01 03:00:00.000', '2019-10-08 05:00:00.000'), -- returns 2 CORRECT
('2019-10-01 04:00:00.000', '2019-10-08 04:00:00.000'); -- returns 2 CORRECT

select
date1,
date2,
DATEDIFF(DAY,DATEADD(SECOND,-1,DATEADD(HOUR, -1 * @HourOfDay, date1)),DATEADD(HOUR, -1 * @HourOfDay, date2)) / 7 + 1
from @tests

Answer 1

Try this:

SET DATEFIRST 1
declare @HourOfDay int = 4
declare @weekday int=2
declare @tests table (
    date1 datetime,
    date2 datetime
)


INSERT INTO @tests VALUES
('2019-10-01 00:00:00.000', '2019-10-02 00:00:00.000'), 
('2019-10-01 05:00:00.000', '2019-10-02 00:00:00.000'), 
('2019-10-01 03:00:00.000', '2019-10-08 05:00:00.000'), 
('2019-10-01 04:00:00.000', '2019-10-08 04:00:00.000'),
('2019-10-03 04:00:00.000', '2019-10-11 02:00:00.000'),
('2019-10-03 04:00:00.000', '2019-10-15 04:00:00.000'),
('2019-10-03 04:00:00.000', '2019-10-15 02:00:00.000'); 

;WITH cte(date1,date2,dt1,dt2)
AS
(
select
date1,
date2,
CASE WHEN datepart(weekday,date1)=@weekday AND datepart(hour,date1)<=@HourOfDay THEN 1 ELSE 0 END,  
CASE WHEN datepart(weekday,date2)=@weekday AND datepart(hour,date2)>=@HourOfDay THEN 1 ELSE 0 END
FROM @tests
)
select
date1,
date2,
dt1+dt2+DATEDIFF(DAY,DATEADD(DAY,dt1,date1),DATEADD(DAY,0-dt2,date2))/7
from cte

Answer 2

I've found a solution which seems to work for my test cases.. I need to do some more testing still though.

I'd be interested in hearing if anyone thinks my logic is incorrect

SET DATEFIRST 1
declare @HourOfDay int = 4
declare @DayOfWeek int = 2

declare @tests table (
    date1 datetime,
    date2 datetime
)

INSERT INTO @tests VALUES
('2019-10-01 00:00:00.000', '2019-10-02 00:00:00.000'), -- returns 1 CORRECT
('2019-10-01 05:00:00.000', '2019-10-02 00:00:00.000'), -- returns 1 INCORRECT
('2019-10-01 03:00:00.000', '2019-10-08 05:00:00.000'), -- returns 2 CORRECT
('2019-10-01 04:00:00.000', '2019-10-08 04:00:00.000'); -- returns 2 CORRECT

select
date1,
date2,
DATEDIFF(DAY,DATEADD(SECOND,-1,DATEADD(HOUR, -1 * @HourOfDay, date1)),DATEADD(HOUR, -1 * @HourOfDay, date2)) / 7 + 1
+ CASE 
    WHEN (DATEPART(DW, date1) = @DayOfWeek AND DATEPART(HOUR, date1) <= @HourOfDay AND
    DATEPART(HOUR, date2) <= @HourOfDay) OR 
    (DATEPART(DW, date2) = @DayOfWeek AND DATEPART(HOUR, date2) >= @HourOfDay AND
    DATEPART(HOUR, date1) <= @HourOfDay)
THEN 0
ELSE -1 END

from @tests

Answer 3

I would suggest a Calendar Table, but you can use an ad-hoc tally/numbers table as well

In the subquery B we are creating an on-the-fly calendar table of 10,000 days starting at 2015-12-31. You can adjust or make dynamic

Example

Declare @YourTable table (Date1 datetime,Date2 datetime)
Insert Into @YourTable values
 ('2019-10-01 00:00:00.000','2019-10-02 00:00:00.000')
,('2019-10-01 05:00:00.000','2019-10-02 00:00:00.000')
,('2019-10-01 03:00:00.000','2019-10-08 05:00:00.000')
,('2019-10-01 04:00:00.000','2019-10-08 04:00:00.000')



Select A.*
      ,Cnt=count(D)
 From  @YourTable A
 Left Join  (
        Select D = DateAdd(DAY,N,'2015-12-31 04:00:00')
         From ( Select Top 10000 N=Row_Number() Over (Order By (Select NULL)) From master..spt_values n1, master..spt_values n2 ) A
       ) B
 On DateName(WEEKDAY,D)='Tuesday'
   and D >=Date1 and D<=Date2
 Group By Date1,Date2

Returns

Date1                    Date2                      Cnt
2019-10-01 00:00:00.000  2019-10-02 00:00:00.000    1
2019-10-01 05:00:00.000  2019-10-02 00:00:00.000    0
2019-10-01 04:00:00.000  2019-10-08 04:00:00.000    2
2019-10-01 03:00:00.000  2019-10-08 05:00:00.000    2