SQL如何组合时间戳以显示持续时间

Question

我有以下查询，其中数据是STORAGE_BOX中ITEM在任何给定时间的位置，其中项目的位置可以用分隔符进一步指定，这样您就可以有STORAGE_BOX/SLOT/TRAY/ROW_ID。这就是我为该列执行SUBSTRING的原因。

我要做的是：创建一个视图，显示特定物品在某个存储箱中的START_DATE和END_DATE。

select at.primary_key as 'ITEM_ID', f.name as 'INSTITUTION', SUBSTRING(sc.location +'/',0, CHARINDEX('/', sc.location + '/'))
 as 'STORAGE_BOX', at.db_timestamp as 'TIMESTAMP' from [dbo].AUDIT_TRAIL at 
RIGHT JOIN [dbo].BIOMATERIAL bio on at.primary_key = bio.id
LEFT JOIN [dbo].FACILITY f on bio.at_facility_id = f.id
LEFT JOIN [dbo].STORAGE_CONTAINER sc on at.primary_key = sc.id
where at.table_name = 'Biomaterial' AND sc.location IS NOT NULL 

所以下面的输出来自上面的查询

+---------+-------------+-------------+------------+
| ITEM_ID | INSTITUTION | STORAGE_BOX | TIMESTAMP  |
+---------+-------------+-------------+------------+
|       1 | Building#1  | STORAGE_0   | 2012-03-25 |
|       1 | Building#1  | STORAGE_0   | 2013-12-25 |
|       1 | Building#1  | STORAGE_1   | 2015-03-25 |
|       2 | Building#2  | STORAGE_3   | 2012-03-25 |
|       2 | Building#2  | STORAGE_4   | 2013-03-25 |
|       2 | Building#2  | STORAGE_5   | 2015-03-25 |
+---------+-------------+-------------+------------+

并将其更改为以下结果，其中START_DATE是新STORAGE_BOX的第一个时间戳，END_DATE是下一个STORAGE_BOX的下一个时间戳或当前时间戳（如果它仍然存在）。

我不知道如何计算上面查询中的这些字段以使其显示

+---------+-------------+-------------+------------+---------------------+
| ITEM_ID | INSTITUTION | STORAGE_BOX | START_DATE |      END_DATE       |
+---------+-------------+-------------+------------+---------------------+
|       1 | Building#1  | STORAGE_0   | 2012-03-25 | 2015-03-25          |
|       1 | Building#1  | STORAGE_1   | 2015-03-25 | {Current_TimeStamp} |
|       2 | Building#2  | STORAGE_3   | 2012-03-25 | 2013-03-25          |
|       2 | Building#2  | STORAGE_4   | 2013-03-25 | 2015-03-25          |
|       2 | Building#2  | STORAGE_5   | 2015-03-25 | {Current_TimeStamp} |
+---------+-------------+-------------+------------+---------------------+

编辑

我使用Gordon Linoff提供的答案创建了以下带有sql server 2008限制的查询

with t as (
      select at.transaction_uid,at.primary_key as BIOMATERIAL_ID, f.name as INSTITUTION,
             at.new_value as FREEZER,
             at.db_timestamp as TIMESTAMP
      from [dbo].AUDIT_TRAIL at RIGHT JOIN
           [dbo].BIOMATERIAL bio
           on at.primary_key = bio.id LEFT JOIN
           [dbo].FACILITY f
           on bio.at_facility_id = f.id 
      where at.table_name = 'Biomaterial' AND at.column_name = 'container_id.location' AND at.new_value IS NOT NULL 
     ),
     t1 as (
      select t.*,
             row_number() over (partition by BIOMATERIAL_ID, INSTITUTION,  FREEZER
                                order by timestamp) as seqnum
      from t 
        ),
        t2 as(
            select t1.*,
                ROW_NUMBER() over (partition by BIOMATERIAL_ID order by seqnum) as seqnum_b
              from t1
              where t1.seqnum = 1
            )
    SELECT a.BIOMATERIAL_ID, a.INSTITUTION, a.FREEZER, a.TIMESTAMP as START_DATE,coalesce(b.TIMESTAMP, getdate()) as END_DATE 
        FROM t2 a left join t2 b on a.BIOMATERIAL_ID = b.BIOMATERIAL_ID AND a.seqnum_b = (b.seqnum_b + 1) order by a.BIOMATERIAL_ID 

Answer 1

您可以使用窗口函数来完成此操作。首先使用row_number()只获取每组的第一行：

with t as (
      select at.primary_key as ITEM_ID, f.name as INSTITUTION,
             SUBSTRING(sc.location +'/',0, CHARINDEX('/', sc.location + '/')) as STORAGE_BOX,
             at.db_timestamp as TIMESTAMP
      from [dbo].AUDIT_TRAIL at RIGHT JOIN
           [dbo].BIOMATERIAL bio
           on at.primary_key = bio.id LEFT JOIN
           [dbo].FACILITY f
           on bio.at_facility_id = f.id LEFT JOIN 
           [dbo].STORAGE_CONTAINER sc
           on at.primary_key = sc.id
      where at.table_name = 'Biomaterial' AND sc.location IS NOT NULL
     ),
     t1 as (
      select t.*,
             row_number() over (partition by ITEM_ID, INSTITUTION,  STORAGE_BOX
                                order by timestamp) as seqnum
      from t
     ),
     t2 as (
      select t1.*, lead(timestamp) over (partition by item_id, institution order by timestamp) as next_timestamp
      from t1
      where seqnum = 1
     )
select t2.ITEM_ID, t2.INSTITUTION, t2.STORAGE_BOX,
       t2.timstamp as START_DATE,
       coalesce(t2.next_timestamp, getdate()) as END_DATE
from t2 ;

第一个CTE是您的查询。第二个枚举每个项目、机构和存储箱的行，以消除重复。这似乎是您查询的逻辑，尽管如果同一个物品/位置使用了两次存储箱，则可能需要更复杂的逻辑。

第三个CTE，t2获得下一个时间戳。最后一个查询应用逻辑。

这是假设SQL Server 2012+（基于您的语法，我假设SQL Server）。您可以在早期版本中使用outer apply执行类似的操作。