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...

Install the DateRange table valued function here: https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function

IF OBJECT_ID('[dbo].[DateRange]','IF') IS NULL BEGIN
    PRINT 'CREATE FUNCTION [dbo].[DateRange]' 
    EXEC ('CREATE FUNCTION...

I've got just the table valued function you need:

IF OBJECT_ID('[dbo].[DateRange]','IF') IS NULL BEGIN
    PRINT 'CREATE FUNCTION [dbo].[DateRange]' 
    EXEC ('CREATE FUNCTION...

This returns the results you have given:

declare @month1 datetime = '2021-08-01 00:00:00.000',
        @month2 datetime = '2021-09-01 00:00:00.000'

select e.HierMonth, e.empID,empName,'new addition',mgrID
...

depending on number of columns on that table you may need (and it may be advisable) to resort to have a hash column added and make this...

depending on number of columns on that table you may need (and it may be advisable) to resort to have a hash column added and make this column the...