Have you tried the options: optimize for unknown and recompile?

That is not really consumable data as I don't have all your tables that make up the query.

You could add an identity column to the temporary table then insert values...

not an option i'm afraid. this is what i have to work with.

i have done what you suggest though and used a...

I can't see how your SQL will work with all those right brackets?

Have you thought...

I was wondering how a Dates table would compare to your function.

Here is some code to generate a...

You should provide consumable date like this:

IF OBJECT_ID('tempdb..#temp','U') is not null
    drop table #temp

select *
  into #temp
  from (values
('GIPAQ','2021-09-19 07:30:00.000', '2021-09-19 08:45:00.000'),
('GIPAQ','2021-09-19...

I'd be real careful with that.  It uses the WEEKDAY date part, which is subject to change and most recommend not using it for such calculations. ...

I'd be real careful with that.  It uses the WEEKDAY date part, which is subject to change and most recommend not using it for such calculations.  I avoid its...

It doesn't make any sense to write the SQL as you have.

You have a LEFT JOIN EntityAttributes EA, but you also have WHERE EA.IsArchived = 'F'

That means your LEFT JOIN...

Here's another version which replaces the second dbo.fnTally tvf with an explicit values table (0-6) and which eliminates the outer WHERE clause by adding the row filter to the...

I would use a cursor for each query in a loop and build up the text you want as an nvarchar(MAX) variable.

Then just use the text you have built up...

This way is much more efficient than my previous effort to get the Nth occurrence of any weekday for an entire year:

declare @year varchar(20) = '2021'
declare @Weekday...

Practically, methods like that work just fine.  Technically, it bugs me because the "Tally" sequence generator had to build 366 values  and then were filtered out to just 12...