Função LAG aninhada (tipo isso)

Acho que sua explicação está incompleta. Você deveria ter indicado que, uma vez obtido um "erro" (3), todos os estados não importam, até que se obtenha um "funcionando" (2) novamente.

Neste caso, trata-se de um problema de Lacunas e Ilhas. Você pode usar a solução tradicional:

select z.*,
  case when f = 3 or (
          lag(MachineStateCode) 
            over(partition by MachineCode order by DoCreation) = 3
          and MachineStateCode <> 2
       )
       then 3 
       else MachineStateCode
  end as MachineStateCodeAdjusted
from (
  select y.*,
    first_value(MachineStateCode) over(partition by g order by DoCreation) as f
  from (
    select x.*, sum(i) over(partition by MachineCode order by DoCreation) as g
    from (
      select t.*, case when MachineStateCode in (2, 3) then 1 else 0 end as i
      from MachineStateChanges t
    ) x
  ) y
) z

Resultado:

 MachineCode  DoCreation                   MachineStateCode  i  g  f   MachineStateCodeAdjusted 
 ------------ ---------------------------- ----------------- -- -- --- ------------------------ 
 DM139        2024-04-03 00:32:17.3770000  99                0  0  99  99                       
 DM139        2024-04-03 00:32:49.0800000  0                 0  0  99  0                        
 DM139        2024-04-03 01:51:14.4270000  99                0  0  99  99                       
 DM139        2024-04-03 01:51:45.6430000  0                 0  0  99  0                        
 DM139        2024-04-03 06:07:38.3570000  3                 1  1  3   3                        
 DM139        2024-04-03 06:07:39.0430000  0                 0  1  3   3                        
 DM139        2024-04-03 06:23:37.4330000  100               0  1  3   3                        
 DM139        2024-04-03 06:23:43.6970000  0                 0  1  3   3                        
 DM139        2024-04-03 06:24:46.1530000  1                 0  1  3   3                        
 DM139        2024-04-03 06:25:02.4670000  0                 0  1  3   3                        
 DM139        2024-04-03 06:46:50.4100000  100               0  1  3   3                        
 DM139        2024-04-03 06:47:29.2470000  0                 0  1  3   3                        
 DM139        2024-04-03 06:47:46.3970000  100               0  1  3   3                        
 DM139        2024-04-03 06:53:47.1600000  0                 0  1  3   3                        
 DM139        2024-04-03 06:59:41.6330000  1                 0  1  3   3                        
 DM139        2024-04-03 06:59:54.5500000  0                 0  1  3   3                        
 DM139        2024-04-03 07:00:03.2030000  1                 0  1  3   3                        
 DM139        2024-04-03 07:00:06.4370000  0                 0  1  3   3                        
 DM139        2024-04-03 07:00:14.2470000  1                 0  1  3   3                        
 DM139        2024-04-03 07:00:17.6830000  2                 1  2  2   2                        
 DM139        2024-04-03 07:00:18.1530000  1                 0  2  2   1                        
 DM139        2024-04-03 07:00:18.8400000  2                 1  3  2   2                        
 DM139        2024-04-03 07:00:26.8600000  1                 0  3  2   1                        
 DM139        2024-04-03 07:00:46.3870000  2                 1  4  2   2                        

Veja o exemplo em execução em db<>fiddle .

Provavelmente você deseja aplicar as seguintes regras para a nova coluna MachineStateCodeAdjusted:

• Se MachineStateCode for 2 ou 3, copie-o

• Caso contrário, encontre a primeira linha antes da linha atual (classificada pela data) cujo MachineStateCode é 2 ou 3. Se esta linha anterior tiver o código 2 ou se nenhuma linha anterior puder ser encontrada, copie o MachineStateCode da linha atual; caso contrário, pegue 3.

Uma maneira de implementar essa lógica é esta consulta:

WITH sub AS (
    SELECT *, MachineStateCode AS MachineStateCodeAdjusted
    FROM yourtable
    WHERE MachineStateCode IN (2,3)
)
SELECT *
FROM sub
UNION ALL
SELECT *,  
  COALESCE((SELECT TOP 1 CASE WHEN MachineStateCodeAdjusted = 3 THEN 3 
    ELSE y.MachineStateCode END FROM sub 
    WHERE sub.DoCreation <= y.DoCreation ORDER BY DoCreation DESC), y.MachineStateCode)
FROM yourtable y
WHERE MachineStateCode NOT IN (2,3)
ORDER BY MachineCode, DoCreation;

A ideia por trás:

• Na consulta "sub" (renomeie-a se quiser e souber um nome melhor), todas as linhas com MachineStateCode 2 ou 3 serão selecionadas, seu MachineStateCode será simplesmente copiado como MachineStateCodeAdjusted.

• A consulta principal seleciona todas as linhas cujo MachineStateCode não é 2 ou 3. Para essas linhas, será verificado se existem linhas anteriores (com uma data anterior) com MachineStateCode 2 ou 3.

• Se tais linhas existirem, pegue a primeira (por data) antes da linha atual e verifique se essa linha tinha MachineStateCode 2 ou 3. Se fosse 2, copie o MachineStateCode, caso contrário, pegue 3 como MachineStateCodeAdjusted.

• Se não existirem tais linhas, basta copiar o MachineStateCode como MachineStateCodeAdjusted.

• Classifique todo o resultado por MachineCode e data

Observe que talvez seja necessário estender a consulta se você quiser criar mais casos ou fazer com que a lógica e o resultado dependam do MachineCode atual (primeira coluna). Em seguida, você pode, por exemplo, adicionar uma condição para que o MachineCode na consulta principal e na subconsulta corresponda, se for o caso.

Veja este db<>fiddle com seus dados de exemplo.

No SQL Server 2022 em diante (ou no Azure), você pode usar IGNORE NULLScom LAST_VALUE() (assim como o RedShift) .

Isso significa que você pode ler facilmente o último código 2 ou 3 e usá-lo em uma expressão CASE.

select
  *,
  case when last_23 = 3 then 3 else MachineStateCode END
from
(
  select
    *,
    LAST_VALUE(
      CASE WHEN MachineStateCode IN (2,3) THEN MachineStateCode END
    )
    IGNORE NULLS 
    OVER (
      PARTITION BY MachineCode
          ORDER BY DoCreation
    )
      AS last_23
  from
    MachineStateChanges
)
  AS look_behind

violino