Replicate an iterative process with cross apply in SQL

My data is very basic, it has the Order ID and the time the order is at.

I need to pay employees 2 hours every time there is an order, however if multiple orders are placed within the same 2 hour window they still only get paid for 2 hour shift. If an order comes in outside of the 2 hour window a new 2 hour shift should be created, and any orders within that 2 hour window will be inside that shift.

I tried using cross apply and got close, but I’m missing a piece I think to the logic. Maybe the only way to do this is to loop the data but I’m trying to avoid this method for now.

Any help would be appreciated

I tried using cross apply like mentioned above, but theres no way for me to know that order 5 for example shouldn’t be grouped with a 2 hour window with order 4… It really needs to go top to bottom, create the shifts, then know if the next record is within that shift.

As the others suggested for SQL Server 2022, DATE_BUCKET works a treat but it can also easily be done prior to 2022. Both methods are included in the code below.

Details in the comments in the code.

This creates the test table. It is not a part of the solution:

--===================================================================
--      Create and populate the test table as a Temp Table
--      This is NOT a part of the solution.
--      You should always post your example data as "Readily
--      Consumable" data to help others help you better and faster.
--===================================================================
--===== If it exists, drop the test table to make reruns easier.
   DROP TABLE IF EXISTS #TestTable;
GO
--===== Create the test table.
 CREATE TABLE #TestTable
        (
         OrderID    INT     NOT NULL PRIMARY KEY CLUSTERED
        ,StartTime  TIME(0) NOT NULL
        )
;
--===== Populate the test table with the given test data.
 INSERT INTO #TestTable WITH (TABLOCK)
        (OrderID, StartTime)
 VALUES  (1,'8:00')
        ,(2,'9:30')
        ,(3,'12:00')
        ,(4,'13:00')
        ,(5,'14:00') --<---<<< This was incorrect in the example output
;

This is the 2022 solution that uses the new DATE_BUCKET function:

--===================================================================
--      Solution for SQL Server 2022 with desired format.
--      Note that its normally not a good idea to format the output
--      in SQL Server but that's what was requested.
--      p.s. I don't use FORMAT() because it's horrible for
--      performance.
--===================================================================
 SELECT  OrderID
        ,StartTime  = CONVERT(CHAR(5),StartTime,108)
        ,ShiftStart = CONVERT(CHAR(5),db.DBTime,108)
        ,EndStart   = CONVERT(CHAR(5),DATEADD(hh,2,db.DBTime),108)
        ,Shift#     = DENSE_RANK() OVER (ORDER BY db.DBTime)
   FROM #TestTable --<---<<< LOOK!!! Change this to suit!!!
  CROSS APPLY (VALUES (DATE_BUCKET(hh,2,StartTime)))db(DBTime)
  ORDER BY OrderID
;

This is the solution for prior to 2022 but will also work in 2022. Not too much difference in the code:

--===== The old way before 2022. Not much difference, though.
 SELECT  OrderID
        ,StartTime  = CONVERT(CHAR(5),StartTime,108)
        ,ShiftStart = CONVERT(CHAR(5),db.DBTime,108)
        ,EndStart   = CONVERT(CHAR(5),DATEADD(hh,2,db.DBTime),108)
        ,Shift#     = DENSE_RANK() OVER (ORDER BY db.DBTime)
   FROM #TestTable tt
  CROSS APPLY (VALUES (CONVERT(TIME,DATEADD(hh,DATEDIFF(hh,0,tt.StartTime)/2*2,0))))db(DBTime)
  ORDER BY OrderID
;

Results either way: