SQL - OVER Clause range between 2 times

Question

I am trying to use the over function to count the number of events which start between the start and end date of the current row for all rows. I.e. a concurrent counter. Ultimately I will be looking to find the Maximum Concurrent events per day or hour etc.

select   FingerPrint
        ,StartDate
        ,EndDate
        ,Num_ConCurrent =   count(FingerPrint) over (
                                                    partition by StartDate
                                                    order by StartDate  
                                                    range between StartDate PRECEDING and EndDate following 
        )
from    #File
group by     FingerPrint
            ,StartDate
            ,Enddate

Unfortunately this does not appear to work. Whilst I know I could simply use a self join I thought there might be an alternative method with the over clause.

Any help much appreciated.

NB: Error Message

Msg 102, Level 15, State 1, Line 7 Incorrect syntax near 'StartDate'.

Answer 1

The Range parameter in the over clause is looking for an integer value, not a column reference. There may be a way to do it with an over clause, but i couldn't get it to work either.

In my experience, self joining on dates within a range is super slow and i haven't found the right set of indexes to speed it up. In this case i would just use in inline sub query.

select  FingerPrint,
        StartDate,
        EndDate,
        (select count(FingerPrint)
         from #File F2
         where F2.StartDate between F.StartDate and F.EndDate
        ) 
        as Num_ConCurrent 
        )
from    #File F

Ran some tests against some appointment data i have and 32,000 rows of appointments executes instantly using this method. Self join ran for 15 seconds before i stopped it.