TSQL - 创建从日期表，同时忽略带条件的中间步骤

我在 MS SQL 服务器中有以下表结构

现在，我想根据以下规则获取每个 [id] 的 [in] 日期：

1. 如果第二天 23:59 [出] 和 00:00 [进] 则忽略这些
2. 如果同一天有[out]和[in]，请忽略这些

例如我应该得到以下结果：

...

...

我不关心[out]，所以它们不是强制性的。有没有一种简单的方法可以实现这一目标？

我带着这样的东西来：

select *from #temp1
EXCEPT
select t1.* from #temp1 t1
INNER join #temp1 t2
ON t1.id =t2.id
and (t1.[in] = DATEADD(minute,1,t2.[out]) or DAY(t1.[in]) = DAY(t2.[out]))

但我丢失了一些数据，例如示例中的第二个 [in] 为 Id 23。

我相信有比这更好的解决方案。我与一个摔跤CTE https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16接近，但我就是无法到达那里。

我的解决方案是使用LAG() https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver16函数来计算特定列的先前值，并将其与日期/时间排除逻辑相结合来定义一个组。最后得到每组的第一个和最后一个值。

SELECT
      a4.id
    , CONVERT (DATE, a4.First_in) AS [in]
    , CONVERT (DATE, a4.[out])    AS [out]
FROM  (
          SELECT
               a3.id
             , a3.[in]
             , a3.[out]
             , a3.id_group
             , FIRST_VALUE (a3.[in]) OVER (PARTITION BY a3.id_group ORDER BY a3.[in]) AS [First_in]
             , ROW_NUMBER () OVER (PARTITION BY a3.id_group ORDER BY a3.[in] DESC)    AS [Row_number]
          FROM (
                   SELECT
                        a2.id
                      , a2.[in]
                      , a2.[out]
                      , a2.New_group
                      , SUM (a2.New_group) OVER (ORDER BY a2.id, a2.[in]) AS [id_group]
                   FROM (
                            SELECT
                                 a1.id
                               , a1.[in]
                               , a1.[out]
                               , IIF((id <> a1.Previous_id)
                                  OR (
                                         id = a1.Previous_id
                                     AND DATEADD (MINUTE, 1, a1.Previous_out) <> a1.[in] -- rule out 23:59 / 00:00 situations
                                     AND DATEDIFF (DAY, a1.Previous_out, a1.[in]) <> 0 -- rule out same day situations

                                     )
                                   , 1
                                   , 0) AS [New_group]
                            FROM (
                                     SELECT
                                          id
                                        , [in]
                                        , [out]
                                        , LAG (id) OVER (ORDER BY id)                      AS [Previous_id]
                                        , LAG ([out]) OVER (PARTITION BY id ORDER BY [in]) AS [Previous_out]
                                     FROM MyTable
                                 ) a1
                        ) a2
               ) a3
      ) a4
WHERE a4.[Row_number] = 1;

这里是.

当然，我的方法可以变得更简洁（即更少的子查询），但我把它们留在那里，这样它们就可以从内到外运行，你可以看到逻辑的进展。我认为如果我可以获得 ROW_NUMBER() 函数，我就可以避免使用最后_值() https://learn.microsoft.com/en-us/sql/t-sql/functions/last-value-transact-sql?view=sql-server-ver16功能来工作。要么我不知道如何使用它，要么它坏了，很可能是前者。

最后，我建议不要使用“in”等关键字作为列名。我只是让事情变得更简单。

Noel