Has a consensus been reached and record = row?  I fear you've...

There are a couple of ways.

The most preferred imo would be to create 'count_grp' using a windowing function and exclude in the WHERE clause of the outer query.

            
September 26, 2020 at 12:14 pm
            
#3791080

            
            
                                                
        

Just a note about the table design.  For this purpose all of the rows which are not a holiday are irrelevant.  Meaning if there were a table of just holidays...

select distinct a.KeyId
from applications a
where not exists (select 1
                  from...

Here are two ways depending on whether GETDATE() returns the date you're interested in.  If it does you could use something like this

/* use GETDATE() to determine...

We're in now now

https://www.youtube.com/watch?v=nRGCZh5A8T4

"How do you get the current date and time using T-SQL question that 20 out of 22 candidates couldn't answer before I stopped counting.  It's NOT a trick question,...

There's a documented process for submitting performance issues.  In the footer of Jeff Moden's forum posts there's a link.

Here's some code to test for what works

declare
  @number1 varchar(12)='121',
  @number2 varchar(12)='126';

select top(1) tp.number, tp.username
from (values ('4581265', 'J'), ('14121587', 'K')) tp(number, username)
where tp.number like '%'+@number1+'%'...

Could each user entered number be assigned to a different variable?   If you have number1 and number2 then something like this

select top(1) tp.number, tp.username, ...
from tb_phone tp
where...

Recall the hamburger analogy.  If the OP has eaten McDonalds before my gourmet burger is ready then they won't be hungry when it arrives.

That's not how long the SQL code takes to execute.  Tally based method would crush the OP's kludge especially as their test case is  spanning thousands of days.

19 minutes is...

Yes, sorry.  This is the preferred answer. ...

Here's another one.  It took 19 minutes vs 12 minutes for the rCTE.  There's no sample data and no time for making any up.  Hopefully the query is...

Here's another one.  It took 19 minutes vs 12 minutes for the rCTE.  There's no sample data and no time for making any up.  Hopefully the query is sound.  It...