Stop using maintenance plans - they have too many 'features'.

Try:

https://ola.hallengren.com/

or:

https://www.minionware.net/products/backup/

SQL Server CXPACKET Wait (sqlskills.com)

What is causing waittype CXSYNC_PORT and what to do about it? - Microsoft Q&A

• This reply was modified 1 year, 3 months ago by  Ken McKelvey.

You still have a typo in your test data in #t1! I am going to assume outtime on 2023-06-06 is 2023-06-06 23:38:44 and not 2023-06-05 23:38:44.

The following works with your...

Bing gave me this:

Capture Linked server queries using SQL Extended Events - Stack Overflow

As your latest data seems to have no missing times, try something like:

WITH DateList
AS
(
    SELECT T.EmployeeId, X.PunchTime
       ...

• This reply was modified 1 year, 3 months ago by  Ken McKelvey.
• This reply was modified 1 year, 3 months ago by  Ken McKelvey.
• This reply was modified 1 year, 3 months ago by  Ken McKelvey.
• This reply was modified 1 year, 3 months ago by  Ken McKelvey.
• This reply was modified 1 year, 3 months ago by  Ken McKelvey.
• This reply was modified 1 year, 3 months ago by  Ken McKelvey.

Are you sure this is a table and not a view? A table like this is poor design.

With the given data, just double the number of rows, with CROSS APPLY,...

>>/* *** Test Data - which, if you want answers, you will provide in future */

I would speak to your network people. It smells like DNS and/or Kerberos.

ps Also, double check you have no SQL Server alias on you application servers.

• This reply was modified 1 year, 3 months ago by  Ken McKelvey.

With:

/* *** Test Data - which, if you want answers, you will provide in future */
SET ANSI_NULLS,  QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE TABLE #t
(
    EmployeeId...

• This reply was modified 1 year, 3 months ago by  Ken McKelvey.
• This reply was modified 1 year, 3 months ago by  Ken McKelvey.
• This reply was modified 1 year, 3 months ago by  Ken McKelvey.

I think Azure SQL DB allows External Tables but am unsure if that will get you as far as AWS.

It might be better to do the queries directly from the...

• This reply was modified 1 year, 3 months ago by  Ken McKelvey.
• This reply was modified 1 year, 3 months ago by  Ken McKelvey.
• This reply was modified 1 year, 3 months ago by  Ken McKelvey.

DECLARE     @FilePath                     ...

I play with SQL2022 on Docker Desktop - maybe other people are doing the same....

How to return multiple sequence numbers with sp_sequence_get_range - Simple Talk (red-gate.com)

Try checking the ODBC data sources with odbcad32.exe. The 64bit one will be system32 and the 32bit one will be in syswow64.

The ODBC data sources might be referenced by the...

I think Ola Hallengren's Maitenance...

• This reply was modified 1 year, 4 months ago by  Ken McKelvey.