Períodos contínuos - Lacunas e ilhas

Você pode usar a abordagem recursiva, com a ajuda de um CTE. Para isso, adicionei umRow_Number so , para ter certeza de que é o próximo registro verificado.

Em seguida, criando o CTE recursivo... chamado cte. Aqui é importante o campo rn as rn_init- esta é a base na hora de criar a cadeia de registros.

;
WITH fr AS -- first records
(
SELECT [Asset_Id], [Trailer_Id], [AssignStart], [AssignEnd]
  ,ROW_NUMBER()OVER( PARTITION BY [Asset_Id], [Trailer_Id] ORDER BY 
   [AssignStart], [AssignEnd]) AS rn
FROM  T1
)
,cte AS
(
 SELECT [Asset_Id], [Trailer_Id]
      , [AssignStart] as [AssignStart_init] , [AssignEnd] as [AssignEnd_Init] 
      , [AssignStart] as [AssignStart],  [AssignEnd] as [AssignEnd]
      , 0 as lvl
      , rn as rn
      , rn as rn_init
 FROM fr
 --WHERE [AssignEnd] is not null

 UNION ALL

 SELECT 
     T1.[Asset_Id], T1.[Trailer_Id]
     ,C.[AssignStart_init],C.[AssignEnd_Init]
     ,T1.[AssignStart], T1.[AssignEnd]
     ,C.lvl + 1 
     ,T1.rn
     ,C.rn_init
 FROM fr AS T1
     INNER JOIN cte as C
        ON T1.[Asset_Id] = C.[Asset_Id]
        AND T1.[Trailer_Id] = C.[Trailer_Id]
        AND CONVERT(DATE,C.[AssignEnd]) = CONVERT(DATE,T1.[AssignStart])
        AND C.[AssignEnd] <= T1.[AssignStart]
        AND C.rn + 1 = T1.rn
)

Aqui está lidando com NULLcase para o AssignEnd e também importante, para contar a 'cadeia de registros' count(rn_init) as c_rn_init. Com base nisso, é posteriormente filtrado.

--select * from cte
,myPrecious AS
(
select  --C.* 
   C.[Asset_Id],c.[Trailer_Id]
    ,MIN([AssignStart]) as AssignStart
    --,MAX([AssignEnd]) as AssignEnd
    ,CASE WHEN MAX(isnull([AssignEnd],[AssignStart])) = MAX([AssignStart]) THEN NULL ELSE MAX(isnull([AssignEnd],[AssignStart])) END as [AssignEnd]
    ,count(rn_init) as c_rn_init
 from cte as c
group by C.[Asset_Id],c.[Trailer_Id], C.rn_init

 )

Isso se a consulta final. Adicionado Row_number , para filtrar registros,ORDER BY c_rn_init DESC

 SELECT 
  Asset_Id,Trailer_Id,AssignStart,AssignEnd
  --,c_rn_init
  --,rn
  FROM(
          SELECT * 
               ,row_number() OVER(PARTITION BY [Asset_Id],[Trailer_Id] , AssignEnd ORDER BY c_rn_init DESC) as rn
          FROM myPrecious
      )A
 WHERE A.rn = 1
 order by [Asset_Id],[Trailer_Id]  ,[AssignStart],[AssignEnd]

A saída:

Asset_Id    Trailer_Id  AssignStart         AssignEnd
33000       30000       01/04/2018 15:00:00 15/04/2018 10:30:00
33000       30000       01/05/2018 10:00:00 null
34000       31000       05/04/2018 10:00:00 10/04/2018 09:30:00
34000       32500       10/04/2018 09:31:00 null
37000       32600       19/04/2018 08:55:00 30/04/2018 17:30:00
37000       32600       01/05/2018 18:23:00 null
37124       32607       19/04/2018 08:55:00 null
38000       36000       01/05/2018 10:00:00 10/05/2018 06:30:00
38000       36000       15/05/2018 09:00:00 null

dbfiddle

Minha ideia é pegar o valor AssignStart, do próximo registro e fazer uma diferença de dia entre [AssignEnd] (da linha atual) e AssignStart (da próxima linha) (Step1)

se for sql server 2012, podemos usar LEAD

LEAD([AssignStart],1,[AssignStart]) OVER(PARTITION BY [Asset_Id], [Trailer_Id] ORDER BY [AssignStart]) as next_AssignStart

se for 2008, não podemos usar LEAD , então use OUTER APPLY para obter o registro:

 OUTER APPLY
   (SELECT TOP(1) T2.[AssignStart]
      FROM T1 as T2
      WHERE T1.[Asset_Id] = T2.[Asset_Id]
          AND T1.[Trailer_Id] = T2.[Trailer_Id] 
          AND T1.[AssignStart]<T2.[AssignStart] 
      ORDER BY T2.[AssignStart] ASC 
   )OA

isso vai me dar oAssignStart da próxima linha, com base em[Asset_Id], [Trailer_Id]

Step2: faça essa coluna de diferença:DATEDIFF(DAY,[AssignEnd],next_AssignStart)

SELECT 
  [Asset_Id], [Trailer_Id], [AssignStart], [AssignEnd]
  ,next_AssignStart
  ,IsNUll(DATEDIFF(DAY,[AssignEnd],next_AssignStart),0) as diff_days
FROM
(
SELECT
    [Asset_Id], [Trailer_Id],T1.[AssignStart], [AssignEnd]
    --,LEAD([AssignStart],1,[AssignStart]) OVER(PARTITION BY [Asset_Id], [Trailer_Id] ORDER BY [AssignStart]) as next_AssignStart
    ,OA.[AssignStart] as next_AssignStart
FROM
   T1
   OUTER APPLY
   (SELECT TOP(1) T2.[AssignStart]
      FROM T1 as T2
      WHERE T1.[Asset_Id] = T2.[Asset_Id]
          AND T1.[Trailer_Id] = T2.[Trailer_Id] 
          AND T1.[AssignStart]<T2.[AssignStart] 
      ORDER BY T2.[AssignStart] ASC 
   )OA
)A
ORDER BY [Asset_Id], [Trailer_Id], [AssignStart], [AssignEnd]

Passo 3: faça um agrupamento nesta coluna, do passo 2

SELECT 
  [Asset_Id], [Trailer_Id]
  ,MIN([AssignStart]) as [AssignStart]
  ,CASE WHEN MAX(isnull([AssignEnd],[AssignStart])) = MAX([AssignStart]) THEN NULL ELSE MAX(isnull([AssignEnd],[AssignStart])) END as [AssignEnd]
FROM
(
SELECT
    [Asset_Id], [Trailer_Id], T1.[AssignStart], [AssignEnd]
    --,LEAD([AssignStart],1,[AssignStart]) OVER(PARTITION BY [Asset_Id], [Trailer_Id] ORDER BY [AssignStart]) as next_AssignStart
    ,OA.[AssignStart] as next_AssignStart
FROM
   T1
   OUTER APPLY
   (SELECT TOP(1) T2.[AssignStart]
      FROM T1 as T2
      WHERE T1.[Asset_Id] = T2.[Asset_Id]
          AND T1.[Trailer_Id] = T2.[Trailer_Id] 
          AND T1.[AssignStart]<T2.[AssignStart] 
      ORDER BY T2.[AssignStart] ASC 
   )OA
)A
GROUP BY [Asset_Id], [Trailer_Id], IsNUll(DATEDIFF(DAY,[AssignEnd],next_AssignStart),0)
ORDER BY [Asset_Id], [Trailer_Id],[AssignStart]

resultado:

Asset_Id    Trailer_Id  AssignStart         AssignEnd
33000       30000       01/04/2018 15:00:00 15/04/2018 10:30:00
33000       30000       01/05/2018 10:00:00 null
34000       31000       05/04/2018 10:00:00 10/04/2018 09:30:00
34000       32500       10/04/2018 09:31:00 null
37000       32600       19/04/2018 08:55:00 null
37124       32607       19/04/2018 08:55:00 null
38000       36000       01/05/2018 10:00:00 10/05/2018 06:30:00
38000       36000       15/05/2018 09:00:00 null

ps: adicionado Order by, para que eu possa verificar muito mais facilmente.

ps2: porque , NULLé ignorado no MAX, usei essa expressão para lidar com a parte superior do intervalo

dbfiddle

Apresento minha solução para T-SQL.

Fiddle SQL Server 2017

O problema:

Precisamos empacotar registros por intervalo de datas sobrepostos (unidade de tempo base 'dia') particionados por Ativo e Trailer.

A solução:

O empacotamento de intervalos de datas sobrepostos requer 3 etapas:

Etapa 1 : determinar um GroupStart

    select Asset_Id, Trailer_Id, AssignStart, AssignEnd
                    , case when max(AssignEnd) 
                        over (partition by Asset_Id, Trailer_Id order by AssignEnd, AssignStart rows between unbounded preceding and 1 preceding)
                            >= AssignStart
                        then 0 else 1 end GroupStart
                from  (
                    select Asset_Id, Trailer_Id, 
                           convert(date, AssignStart) AssignStart, 
                           convert(date, coalesce(AssignEnd, '99991231')) AssignEnd
                    from T1
            ) t0

Etapa 2 : determinar os grupos

select Asset_Id, Trailer_Id, AssignStart, AssignEnd, sum(GroupStart) over (partition by Asset_Id, Trailer_Id order by AssignStart) Grp 
        from (...) t0
) p0

Etapa 3 : determinar o intervalo de datas compactado

select Asset_Id, Trailer_Id, min(AssignStart) AssignStart, max(AssignEnd) AssignEnd 
    from (...) p1
    group by Asset_Id, Trailer_Id, Grp

Configurar:

CREATE TABLE T1
    (Asset_Id int, Trailer_Id int, AssignStart datetime, AssignEnd DATETIME)
;
INSERT INTO T1
    (Asset_Id, Trailer_Id, AssignStart, AssignEnd)
VALUES
    (37124, 32607, '2018-04-19 08:55:00', '2018-05-05 10:00:00.000'),
    (37124, 32607, '2018-05-05 11:23:00', NULL),
    (33000, 30000, '2018-04-01 15:00:00', '2018-04-15 10:30:00.000'),
    (34000, 31000, '2018-04-05 10:00:00', '2018-04-10 09:30:00.000'),
    (34000, 32500, '2018-04-10 09:31:00', NULL),
    (37000, 32600, '2018-04-19 08:55:00', '2018-04-25 08:30:00.000'),
    (37000, 32600, '2018-04-25 09:23:00', '2018-04-25 10:00:00.000'),
    (37000, 32600, '2018-04-25 11:23:00', '2018-04-30 15:00:00.000'),
    (37000, 32600, '2018-04-30 16:15:00', '2018-04-30 17:30:00.000'),
    (37000, 32600, '2018-05-01 18:23:00', NULL),
    (38000, 36000, '2018-05-01 10:00:00', '2018-05-10 06:30:00.000'),
    (38000, 36000, '2018-05-15 09:00:00', '2018-05-20 11:00:00.000'),
    (38000, 36000, '2018-05-20 12:00:00', NULL),
    (33000, 30000, '2018-05-01 10:00:00', NULL)

Consulta completa:

 with packed as (
    select Asset_Id, Trailer_Id, min(AssignStart) AssignStart, max(AssignEnd) AssignEnd from (
        select Asset_Id, Trailer_Id, AssignStart, AssignEnd, sum(GroupStart) over (partition by Asset_Id, Trailer_Id order by AssignStart) Grp from (
            select Asset_Id, Trailer_Id, AssignStart, AssignEnd
                , case when max(AssignEnd) 
                    over (partition by Asset_Id, Trailer_Id order by AssignEnd, AssignStart rows between unbounded preceding and 1 preceding)
                        >= AssignStart
                    then 0 else 1 end GroupStart
            from (
                    select Asset_Id, Trailer_Id, 
                           convert(date, AssignStart) AssignStart, 
                           convert(date, coalesce(AssignEnd, '99991231')) AssignEnd
                    from T1
            ) t0
        ) p0
    ) p1
    group by Asset_Id, Trailer_Id, Grp
)
select Asset_Id, Trailer_Id, AssignStart, nullif(AssignEnd, '99991231')
from packed
order by Asset_Id, Trailer_Id, AssignStart;

Você pode tentar a auto-junção como alternativa.

No meu ambiente (com os dados de exemplo) está gerando menos leituras que o OUTER APPLY. e produzir um plano de execução menos dispendioso. Mas quando eu adiciono índices diferentes, o OUTER APPLY às vezes sai no topo.

--SET STATISTICS IO ON
--SET STATISTICS TIME ON

;WITH cteSequence AS
(
SELECT 
    CUR.Asset_Id 
    ,CUR.Trailer_Id
    ,CUR.AssignStart
    ,CUR.AssignEnd
    ,ROW_NUMBER() OVER(PARTITION BY Asset_Id,Trailer_Id ORDER BY AssignStart) AS SEQ
FROM 
    T1 CUR
)
SELECT 
    CUR.Asset_Id 
    ,CUR.Trailer_Id
    ,MIN(CUR.AssignStart) AS AssignStart
    ,CASE 
        WHEN MAX(COALESCE(NXT.[AssignEnd],CUR.[AssignStart])) = MAX(NXT.[AssignStart]) THEN NULL 
        ELSE MAX(COALESCE(CUR.[AssignEnd],NXT.[AssignStart])) END as [AssignEnd]
FROM 
    cteSequence CUR
        LEFT JOIN cteSequence NXT
            ON
            CUR.Asset_Id = NXT.Asset_Id
            AND
            CUR.Trailer_Id = NXT.Trailer_Id
            AND
            CUR.SEQ = NXT.SEQ-1
GROUP BY 
    CUR.Asset_Id 
    ,CUR.Trailer_Id
    ,COALESCE(DATEDIFF(DD,CUR.AssignEnd,NXT.AssignStart),0)