I never really nest joins like that but if you do you can only access fields from the join on the next level above.

Create some tables:

DROP TABLE...

The OP explicitly included leading zeroes in the PK.  Those are gone in the table because ChatGPT failed to recognize that as a string.  It would have similar issues...

Then after you wrote this I thought I'd see what ChatGPT could do to create a table with the data:

So I pasted the table in the question into...

• This reply was modified 2 years, 7 months ago by Jonathan AC Roberts. Reason: Except that Date is a valid name for a column without putting square brackets around it

That's nice but it's going to silently fail and produce the incorrect total call duration if it adds up to more than 23:59.  You've also stolen the thunder of...

Create some test data:

DROP TABLE IF EXISTS #temp
GO
SELECT *
INTO #temp
FROM (VALUES 
(001, CONVERT(DATE,'2023-02-23'), '123', 'a@a.com', '10:23:00.000', 500, 'W'),
(002, '2023-02-23', '246', 'b@b.com', '09:36:00.000', 456, 'W'),
(003, '2023-02-22', '123', 'a@a.com',...

Interesting... the first time I clicked on it, it took me to a page selling hair-care products with that picture on it.  I just clicked on it again and,...

I'm thinking that may have been their...

• This reply was modified 2 years, 7 months ago by Jonathan AC Roberts. Reason: I've updated the link so it points to the stock photo site where it came from instead of a site that used it

I don't think ScottPletcher wrote that - lol

Dealing with a super huge table that is partially partitioned can be a challenging task for many database administrators and developers. However, with the right approach and...

Have you checked the error logs?

Ha! I was just coming down to say something similar. I suspect we're going to be...

Dealing with a super huge table that is partially partitioned can be a challenging task for many database administrators and developers. However, with the right approach and tools, you...

• This reply was modified 2 years, 7 months ago by Jonathan AC Roberts. Reason: link updated

You need to first work out which query is taking the longest then look at optimising that.

You could put some logging in the query to write to a table how...

**************************************** cross tab

Table '#TestTable'. Scan count 5, logical reads 40651, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0,...

Thank you all!

Sorry but I made a mistake on my original posting. Revised DATA representation is below

IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL
  ...