MSSQL 2005 How can i group these data

Question

I have a table like this in SQL SERVER 2005

No WorkOrder StartDateTime     EndDateTime       
======================================================
1  WO111111  2019-01-01 07:00  2019-01-01 08:00  
2  WO111111  2019-01-01 08:00  2019-01-01 08:30  
3  WO222222  2019-01-01 08:30  2019-01-01 09:30  
4  WO222222  2019-01-01 09:30  2019-01-01 10:00  
6  WO222222  2019-01-01 10:00  2019-01-01 12:00 
7  WO111111  2019-01-01 12:00  2019-01-01 17:00  

How Can I get the table below

WorkOrder StartDateTime     EndDateTime
============================================
WO111111  2019-01-01 07:00  2019-01-01 08:30
WO222222  2019-01-01 08:30  2019-01-01 12:00
WO111111  2019-01-01 12:00  2019-01-01 17:00

I tried row_number() and rank(), and it did't work.

DECLARE @Tmp TABLE (No int, WorkOrder varchar(20), StartDateTime datetime, EndDateTime datetime)
insert into @Tmp values(1,'WO111111','2019-01-01 07:00','2019-01-01 08:00')
insert into @Tmp values(2,'WO111111','2019-01-01 08:00','2019-01-01 08:30')
insert into @Tmp values(3,'WO222222','2019-01-01 08:30','2019-01-01 09:30')
insert into @Tmp values(4,'WO222222','2019-01-01 09:30','2019-01-01 10:00')
insert into @Tmp values(5,'WO222222','2019-01-01 10:00','2019-01-01 12:00')
insert into @Tmp values(6,'WO111111','2019-01-01 12:00','2019-01-01 17:00')
select * from @Tmp;
select g,WorkOrder,min(StartDateTime)StartDateTime,Max(EndDateTime)EndDateTime
From(
  select rank()over(order by WorkOrder)as g,* from @Tmp
)a group by g,WorkOrder

Answer 1

Now knowing you're on SQL Server 2005, you'll need to rely on outer apply to make the arbitrary join required here to determine the relative previous record.

You haven't outlined the predication here. But guessing from the output you're looking for the first WorkOrder of each group, which in this case appears to be a run of times until a different WorkOrder appears.

The approach below uses apply to obtain the previous record using top 1, specifically outer apply to ensure we don't lose the first record (think of it like a left join).

The apply iterator is almost always overlooked and often forgotten. But is a very powerful tool when you need iteration without a concrete join predicate like a key. I've used this approach on big tables to get to solve the "top n problem", finding it sometimes performs better than what's built-in.

Note that I've chosen No as my tie-breaker.

CREATE TABLE #WorkOrders (
   No             INT IDENTITY PRIMARY KEY
  ,WorkOrder      VARCHAR(8) NOT NULL
  ,StartDateTime  DATETIME NOT NULL
  ,EndDateTime    DATETIME NOT NULL);

INSERT INTO #WorkOrders (WorkOrder, StartDateTime, EndDateTime)
VALUES  ('WO111111','20190101 07:00','20190101 08:00')
        ,('WO111111','20190101 08:00','20190101 08:30')
        ,('WO111111','20190101 08:30','20190101 09:30')
        ,('WO222222','20190101 08:30','20190101 09:30')
        ,('WO222222','20190101 09:30','20190101 10:00')
        ,('WO222222','20190101 10:00','20190101 12:30')
        ,('WO111111','20190101 12:00','20190101 12:30')

SELECT  wo.WorkOrder
     ,  wo.StartDateTime
     ,  wo.EndDateTime
  FROM  #WorkOrders AS wo 
        OUTER APPLY (
          SELECT  TOP(1)
                  * 
            FROM  #WorkOrders AS wo2 
           WHERE  wo2.StartDateTime < wo.StartDateTime 
           ORDER  BY wo2.StartDateTime DESC, No DESC
        ) AS prev
 WHERE prev.WorkOrder IS NULL
       OR prev.WorkOrder <> wo.WorkOrder

DROP TABLE #WorkOrders;