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 6 years, 4 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...

How can you get a non whole number answer if everything starts and ends on the hour? Because each room is effectively for two meetings at once?

Andy,

I created the table and added the insert scripts... (although the table really needs indexing!)

If you really need help, help us help you. Read this link: How to post code...

• This reply was modified 6 years, 4 months ago by pietlinden. Reason: my code was wrong

Like this?

SELECT dbname
 , [type]
 , CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) ) AS SizeMB
FROM
(SELECT DB_NAME(database_id) as dbname,
  [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
      WHEN Type_Desc =...

StopType is either (P)ick or (D)rop

(That's why I was doing the CASE WHEN StopType = 'D' THEN -1 ELSE 1 END part... to convert those pickups to positive numbers and...

So you just want a total number of passengers picked up during the whole route - doesn't matter when they get on or off? then you'd just count the PassengerIDs...