sql server—从事务返回项目的持续时间，多对多，sql

希望我能得到一些帮助。
情况
有两个进站和一个出站。项目被扫描进出。我要知道一件东西在车站里有多长时间。让我们把“in station”看作是它的传入日期扫描和传出日期扫描之间的时间。
问题
一个项目可以（意外地）扫描多次到任何一个站点（为此，我正在考虑确定是否在同一天进行扫描（不看小时数），然后返回扫描的最早时间）
一个项目可以从站点多次进出（多次进出扫描）
如果一个项目被扫描到两个传入位置，需要得到最早的时间
数据样本。。我们开始吧

╔═════════╦════════╦══════════════════╦════════════════╦══════════╗
║ Row_num ║ ItemID ║      Dates       ║  LocationName  ║   Type   ║
╠═════════╬════════╬══════════════════╬════════════════╬══════════╣
║       1 ║ ItemA  ║ 1/7/20 12:49 PM  ║ Outgoing_Loc   ║ Outgoing ║
║       2 ║ ItemA  ║ 1/2/20 7:29 AM   ║ Incoming_Loc_A ║ Incoming ║
║       3 ║ ItemB  ║ 1/3/20 11:01 AM  ║ Outgoing_Loc   ║ Outgoing ║
║       4 ║ ItemB  ║ 1/2/20 4:57 PM   ║ Incoming_Loc_B ║ Incoming ║
║       5 ║ ItemB  ║ 1/2/20 5:01 PM   ║ Incoming_Loc_A ║ Incoming ║
║       6 ║ ItemB  ║ 12/12/19 5:58 PM ║ Outgoing_Loc   ║ Outgoing ║
║       7 ║ ItemB  ║ 12/12/19 5:57 PM ║ Outgoing_Loc   ║ Outgoing ║
║       8 ║ ItemB  ║ 5/20/19 10:19 AM ║ Outgoing_Loc   ║ Outgoing ║
║       9 ║ ItemC  ║ 1/9/20 9:20 AM   ║ Outgoing_Loc   ║ Outgoing ║
║      10 ║ ItemC  ║ 1/2/20 6:42 PM   ║ Incoming_Loc_A ║ Incoming ║
║      11 ║ ItemC  ║ 12/20/19 5:54 AM ║ Outgoing_Loc   ║ Outgoing ║
║      12 ║ ItemC  ║ 10/10/19 6:13 PM ║ Outgoing_Loc   ║ Outgoing ║
║      13 ║ ItemC  ║ 10/5/19 7:00 PM  ║ Incoming_Loc_A ║ Incoming ║
║      14 ║ ItemC  ║ 7/16/19 9:18 AM  ║ Outgoing_Loc   ║ Outgoing ║
╚═════════╩════════╩══════════════════╩════════════════╩══════════╝

我试着把每种类型的问题分到表中的不同项目。
完美的事务是itema，它是如此简单和干净，如果它们都是这样的话，那么我就可以连接表并将它们拉到不同的列上。
itemb，你会注意到这一个被扫描到两个传入的位置，但我只需要返回一个-最早的来自该批。此外，需要返回最早的传出（12/12/19）之后和最后一个传出（1/3/20）之前的传入。
itemc，与itemb的上一个语句类似，这个项从各个位置进出了两次。需要按时间顺序获得最有意义的输入和输出对。
我不知道这有多难搞清楚，但我很难找到解决办法。我不知道该如何在即将到来的日子和即将离去的日子之间挤出时间。
输出示例：
需要知道每件物品在车站的天数。如果项目已经被输入或输出多次，需要按时间顺序将输入和输出配对。例如，itemc有多个传入和传出日期，但我只需要有开始和结束的日期成对出现。

+--------+-----------------+------------------+-----------------+
| ItemID |    Incoming     |     Outgoing     | Days in Station |
+--------+-----------------+------------------+-----------------+
| ItemA  | 1/2/20 7:29 AM  | 1/7/20 12:49 PM  | 5.00            |
| ItemB  | 1/2/20 4:57 PM  | 1/3/20 11:01 AM  | 1.00            |
| ItemC  | 1/2/20 6:42 PM  | 1/9/20 9:20 AM   | 7.00            |
| ItemC  | 10/5/19 7:00 PM | 10/10/19 6:13 PM | 5.00            |
+--------+-----------------+------------------+-----------------+

这是一个缺口和孤岛问题。一种方法是使用对每个传入记录递增的累积和来定义组，并将其用于聚合：

select
    itemID,
    min(dates) incoming,
    max(dates) outgoing,
    datediff(second, min(dates), max(dates)) / 60.0 / 60 / 24 days_in_station
from (
    select
        t.*,
        sum(case when type = 'Incoming' then 1 else 0 end)
            over(partition by itemID order by dates) grp
    from mytable t
) t
group by itemID, grp

您的问题没有指定当传入/传出记录没有为给定项正确交叉时应该发生什么。查询将如何处理：
如果有两个连续的传入记录，这将在结果集中生成一行，其中传入和传出日期相同，并且在站点的天数相同 0 如果有两个或多个连续的传出记录，则只考虑最后一个
如果提供更多关于需求的细节，这些可以进行微调。

sql server—从事务返回项目的持续时间，多对多，sql

1条答案

相关问题

热门标签

最新问答