The optimiser should be able to simplify the query to remove the nesting so it might not make much difference.

One of the big advantages...

The optimiser should be able to simplify the query to remove the nesting so it might not make much difference.

One of the big advantages of putting...

The optimiser should be able to simplify the query to remove the nesting so it might not make much difference.

One of the big advantages of putting you results...

The optimiser should be able to simplify the query to remove the nesting so it might not make much difference.

One of the big advantages of putting you results into a...

You can simplify the query to this:

SELECT COUNT(*)
  FROM BSEG I
  LEFT JOIN BKPF H 
         ON...

• This reply was modified 10 months ago by  Jonathan AC Roberts.

Here is a script for getting the definition of temp tables:

https://www.sqlservercentral.com/scripts/get-temp-table-definition

https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver16

This might be more efficient:

;WITH CTE AS
(
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY (SELECT...

SELECT Color,
       SampleType,
       SUM(SampleValue) SampleValue
  FROM #SampleData
 GROUP BY Color, 
   ...

From ChatGPT 4:

The provided deadlock XML contains a lot of information, but let's focus on the core elements to diagnose and address the deadlock issue.

First, let's break down the XML...

There was a big improvement to the performance of scalar valued functions in SQL Server 2019.

But you need to make sure the improvement is switched on to get the benefit,...

• This reply was modified 10 months ago by  Jonathan AC Roberts.

If you need the count to be NULL when there aren't 5 preceding days then this query will do the trick:

declare @DaysToLookBack int = 5

select a.company, 
...

declare @DaysToLookBack int = 5

select *
  from [dbo].[TestTable1] a
 cross apply (select count(*) Count
            ...

There aren't 3 values less than 88 within 5 days of (including the reference date) 2024-02-28

2024-02-28 minus 4 days is 2024-02-24.

So there are only  2 rows less than that (11,...

• This reply was modified 10 months, 1 week ago by  Jonathan AC Roberts.

The gaps in dates shouldn't be an issue.

The data you provided isn't the data you put in your amendment of the query.

The results from the test data you provided look...