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, 3 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...

I guess I answered this question, the one you explicitly asked instead of the one you intended:

I'm trying to get a running total of how many riders are on the...

• This reply was modified 6 years, 3 months ago by pietlinden.

Not a clue what Modulo has to do with this. Sounds like you want a running total of riders boarding minus a running total of riders leaving the bus.  Maybe...

Post your SQL statements here... nobody's going to download files from people they don't know.