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 5 years, 11 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...

It's the "who's the conductor of the train?" joke, but you really want the number of passengers on the train at any point.

I wouldn't count the bit column, personally. I'd...