Here is the code produced by ChatGPT

SELECT *
FROM your_table
WHERE date_column >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)
  AND date_column...

Here is a test that shows using a separate UPDATE/INSERT is faster than a MERGE.

SET STATISTICS IO, TIME OFF

if object_id('dbo.t1','U') IS NOT NULL drop table dbo.t1;
if object_id('dbo.t2','U')...

Sorry, but this script won't work well for large tables, especially with heavy use of IN() and NOT IN() operators. MERGE is usually faster than series of consecutive INSERT/UPDATE/DELETE...

Heh... Ya think? 😀

Why is there...

Well it can't go in the Employee table.

I think you just need one table with

Overtime

ID (primary key)

EmployeeId (foreign key referencing the Employee table)

OvertimeDate

OTypeID ("D", "FoN", or "FN")

OvertimeHours

If you want to make it even faster you can add an index to the temporary table:

IF OBJECT_ID('tempdb..#pre_filtering') IS NOT NULL
    DROP TABLE #pre_filtering;

;WITH...

Try this instead, it splits it into 2 separate statements:

IF OBJECT_ID('tempdb..#pre_filtering') IS NOT NULL
    DROP TABLE #pre_filtering;

;WITH pre_filtering AS 
(
    SELECT...

Try this:

;WITH pre_filtering AS 
(
    SELECT DISTINCT 
           a.[DC], 
    ...

You asked the question in the SQL 2019 forum.

Try the code in the last comment I made.

You need a GROUP BY:

SELECT DC, UPC, stuff((select ', ' + MASTERCHAINNAME 
              from...

• This reply was modified 3 years, 6 months ago by Jonathan AC Roberts. Reason: Added where b.DC = a.DC and b.UPC = a.UPC

It's the version of SQL Server...

Create some test data in a consumable format (you should have done this):

DROP TABLE IF EXISTS #pre_filtering;

CREATE TABLE #pre_filtering (
    DC VARCHAR(50),
  ...

You might have to rewrite the query to...

[

Hi Jonathan,

Thanks for the inputs. I would think the last non-clustered idx is better to create. Also, I have a question on this, do we need to mention ROWID_OBJECT...