Use a better splitter function?  I used Jeff Moden's DelimitedSplit8K which is here ...

Setup (this is the part you were supposed to do):

use tempdb;
go
CREATE TABLE #BadData...

• This reply was modified 5 years, 8 months ago by pietlinden.
• This reply was modified 5 years, 8 months ago by pietlinden.
• This reply was modified 5 years, 8 months ago by pietlinden.

are you querying the master or querying a replica?

Ouch... had to reformat all that so it was easier to read...

IF OBJECT_ID('product') IS NOT NULL
DROP TABLE [dbo].[product]

CREATE TABLE [dbo].[product](
[productid] [VARCHAR](20) NULL,
[productEventID] [VARCHAR](50) NULL,
[productTransID] [VARCHAR](50) NULL,
[productEffectiveDate] [DATETIME]...

Post the definition of your Calendar table.  Are you familiar with dimension tables in data warehouse designs? Normally a Calendar dimension will be something like this:

CREATE TABLE...

• This reply was modified 5 years, 8 months ago by pietlinden.

Re-reading this... are you trying to do a running total, or just a normal total? If you want a running total, I'd use a windowing function.

SUM([numeric column]) OVER (PARTITION BY...

WITH (nolock) isn't a magic "go faster" button. It's allowing dirty reads. If you want it to read faster, index the columns in the joins and the filters.

We don't even...

• This reply was modified 5 years, 8 months ago by pietlinden. Reason: additional info

Like this?

use tempdb;
go

CREATE TABLE SomeData (
 VendorID INT NOT NULL,
 PartID INT NOT NULL,
 PODate DATE NOT NULL);
GO

INSERT INTO SomeData VALUES(101,10,'1/1/2020'),(201,10,'2/10/2020');
-- setup is done, now answer the question...

SELECT *
FROM (
  SELECT...

NVARCHAR() fields are text.

How about...

"I have this table...

CREATE TABLE SomeData (…);

which contains data like this...

INSERT INTO SomeData(f1,f2,f3...) VALUES (1, 2,3),(2,3,4)…;"

and maybe when I run this query <paste statement here>, I get this result...

Use ROW_NUMBER() with PARTITION BY?

How about some sample data and expected results?

Is your data really double-byte (from double-byte languages like Arabic and Japanese?) If not, just use VARCHAR instead.

Why not do both and compare the query plans?

SET STATISTICS TIME, IO ON;

and then do it. Which one takes longer?

Thought I had it...but I'm missing a date (because the LAG/LEAD is cutting it off). Anyway, here's the code:

SELECT *
FROM
 (SELECT EmployeeID
  , TranDate
  , DATEADD(day,-1,LEAD(TranDate,1) OVER (PARTITION BY EmployeeID...

.. and we can't help you without any data. How about a thin horizontal slice of the table's data. Enough to reproduce the problem. It's really hard to execute a...

Oh, right! That's what I was missing. The times before 8:30 and after 16:00 don't count. That's where the half hour came from. I didn't chop those off. Explains why...