Modify records to account for correct priority and order between two systems

Question

I have two tables which track items in different systems, for simplicity, lets call them Alpha and Beta systems. I am attempting to merge the two tables into a single table that can be correctly track the location of items.

Items tracked start in Alpha, and can move into Beta. However since the systems are not connected, the start time entered into Beta are not always matched to the end time in Alpha.

An example Item tracked correctly is as follows

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

My issue is because the systems are not linked, I have cases where items appear in both Alpha and Beta at the same time. As follows

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 is the better system, and should always be trusted.

So my desired outcome in the above situation would be to modify record 184 and change it's start time to the end time of record 374.

There is an another case also which should be accounted for, and that's where an Alpha record begins and ends before the last Beta record starts.

Make sense? I hope so, it's been driving me nuts for the last few days.

Thank you for any help you can give.

Answer 1

If you use SQL Server 2012 and later (thanks for the tip Karl) you can use LAG and LEAD, as shown here:

SQL Fiddle

Query 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]

Results:

| 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

I think you want to find the last alpha and the first beta for each item and update the first beta start time with the end time of the last alpha.

This could be simplified and certainly is optimized for performance. I left it this way because it is very explicit.

Note: LAG and LEAD are introduced in SQL Server 2012, so cha's solution certainly works if your have that or later.

--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

We can achieve this by simply joining two tables in the below mentioned query : -

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