修改记录以说明两个系统之间正确的优先级和顺序

Question

我有两个表用于跟踪不同系统中的项目，为简单起见，我们称它们为Alpha和Beta系统。我试图将这两个表合并为一个表，可以正确地跟踪项目的位置。

项目跟踪开始于Alpha，并可以移动到Beta。然而，由于系统没有连接，输入Beta的开始时间并不总是与Alpha的结束时间相匹配。

正确跟踪的项目示例如下

System---ID----Item------- Start---------------- End Alpha - 987 - 123 - 20/5/2015 07:00:00 - 20/5/2015 08:00:00 Alpha - 374 - 123 - 20/5/2015 08:00:00 - 20/5/2015 09:00:00 Beta - 184 - 123 - 20/5/2015 09:00:00 - 20/5/2015 10:00:00 Beta - 798 - 123 - 20/5/2015 10:00:00 - 20/5/2015 12:00:00

我的问题是，因为系统没有连接，我有项目出现在Alpha和Beta同时的情况。如下

System---ID----Item------- Start---------------- End Alpha - 987 - 123 - 20/5/2015 07:00:00 - 20/5/2015 08:00:00 Beta - 184 - 123 - 20/5/2015 07:30:00 - 20/5/2015 10:00:00 Alpha - 374 - 123 - 20/5/2015 08:00:00 - 20/5/2015 09:00:00 Beta - 798 - 123 - 20/5/2015 10:00:00 - 20/5/2015 12:00:00

Alpha是更好的系统，应该永远被信任。

因此，在上述情况下，我希望的结果是修改记录184，并将其开始时间更改为记录374的结束时间。

还需要考虑另一种情况，即Alpha记录在最后一个Beta记录开始之前开始和结束。

有意义吗?希望如此，这几天我都快疯了。

谢谢你给我的帮助

Answer 1

如果您使用SQL Server 2012及更新版本(感谢Karl的提示)，您可以使用LAG和LEAD，如下所示:

SQL小提琴

查询1

:

select [System], [ID], [Item], 
[Start], 
CASE WHEN LAG([End]) OVER(ORDER BY [Start]) > [Start] AND 
          LAG([System]) OVER(ORDER BY [Start]) = 'Alpha' AND
          [System] = 'Beta'
     THEN LAG([End]) OVER(ORDER BY [Start]) ELSE [Start] END As [CorrectStart],
[End],
CASE WHEN LEAD([Start]) OVER(ORDER BY [Start]) < [End] AND 
          LEAD([System]) OVER(ORDER BY [Start]) = 'Alpha' AND
          [System] = 'Beta'
     THEN LEAD([Start]) OVER(ORDER BY [Start]) ELSE [End] END As [CorrectEnd]
FROM Table1
order by [Start]

结果

:

| System |  ID | Item |                 Start |          CorrectStart |                   End |            CorrectEnd |
|--------|-----|------|-----------------------|-----------------------|-----------------------|-----------------------|
|  Alpha | 987 |  123 | May, 20 2015 07:00:00 | May, 20 2015 07:00:00 | May, 20 2015 08:00:00 | May, 20 2015 08:00:00 |
|   Beta | 374 |  123 | May, 20 2015 07:30:00 | May, 20 2015 08:00:00 | May, 20 2015 10:00:00 | May, 20 2015 09:00:00 |
|  Alpha | 184 |  123 | May, 20 2015 09:00:00 | May, 20 2015 09:00:00 | May, 20 2015 10:00:00 | May, 20 2015 10:00:00 |
|   Beta | 798 |  123 | May, 20 2015 10:00:00 | May, 20 2015 10:00:00 | May, 20 2015 12:00:00 | May, 20 2015 12:00:00 |

Answer 2

我认为您希望找到每个项目的最后一个alpha测试和第一个beta测试，并将第一个beta测试的开始时间更新为最后一个alpha测试的结束时间。

这可以简化，当然也可以优化性能。我这样做是因为它非常明确。

注意:在SQL Server 2012中引入了LAG和LEAD，所以cha的解决方案当然可以工作，如果你有这个或更高版本。

--create the sample data
DECLARE @Tracking TABLE(Name VARCHAR(10),ID INT,Item INT,StartTime DATETIME2,EndTime DATETIME2)
INSERT INTO @Tracking 
  SELECT * FROM (VALUES
  ('Alpha'  , 987 , 123 , '2015-05-20 07:00:00' , '2015-05-20 08:00:00')
 ,('Beta'   , 184 , 123 , '2015-05-20 07:30:00' , '2015-05-20 10:00:00')
 ,('Alpha'  , 374 , 123 , '2015-05-20 08:00:00' , '2015-05-20 09:00:00')
 ,('Beta'   , 798 , 123 , '2015-05-20 10:00:00' , '2015-05-20 12:00:00')
 ) AS tbl(Name,ID,Item,StartTime,EndTime)  
--get row number for the sample data over sytem name and item
--use a cte for clarity
;WITH 
cte AS (
  SELECT Name,ID,Item,StartTime,EndTime
        ,rn = ROW_NUMBER() OVER (PARTITION BY Item,Name ORDER BY StartTime)
        ,rn_reverse = ROW_NUMBER() OVER (PARTITION BY Item,Name ORDER BY StartTime DESC)
    FROM @Tracking
),
--get only the last alpha
LastAlphas AS (
  SELECT * FROM cte WHERE Name = 'Alpha' AND rn_reverse = 1
),
--and the forst beta
FirstBetas AS (
  SELECT * FROM cte WHERE Name = 'Beta' AND rn = 1
)
--join them all and do the update
UPDATE @Tracking
   SET StartTime = a.EndTime
  FROM @Tracking t
  JOIN FirstBetas b ON t.id = b.id
  JOIN LastAlphas a ON t.item = a.item
SELECT * FROM @Tracking ORDER BY Name, StartTime
--Alpha 987 123 2015-05-20 07:00:00.0000000 2015-05-20 08:00:00.0000000
--Alpha 374 123 2015-05-20 08:00:00.0000000 2015-05-20 09:00:00.0000000
--Beta  184 123 2015-05-20 09:00:00.0000000 2015-05-20 10:00:00.0000000
--Beta  798 123 2015-05-20 10:00:00.0000000 2015-05-20 12:00:00.0000000

Answer 3

我们可以通过下面提到的查询简单地连接两个表来实现这一点:-

with cte1 as 
(
select *,ROW_NUMBER() over (partition by name order by starttime) as rn
from #a
),cte2 as 
(
select a.name,a.id,a.item,min(isnull((case when a.name = 'Beta' then b.endtime else a.starttime end),a.starttime)) as starttime,a.endtime
from cte1 as a
left join cte1 as b
on b.rn = a.rn + 1
group by a.name,a.id,a.item,a.endtime
)
update a
set a.starttime = b.starttime,
    a.endtime = b.endtime 
from #a as a
inner join cte2 as b
on b.id = a.id