CODEWITHSUNDEEP
sql-servert-sql
Eric Klaus
Eric Klaus

Reputation: 955

Insert from Staging to Final only if more recent

I can't come up with a proper query for this insert problem

I have a table Final

And I have a Stg table. I want to insert Stg into Final only if

Stg.Date > MAX(Final.Start) -> placeholder for Pilot (P) for each Destination and Stg.Pilot <> (P) Initial Final table

enter image description here

Assume Stg.Destination is Unique

Ex1: Since both Stg entries Date field > than their corresponding max field in Final, both are inserted

enter image description here

Ex2: Since Stg.Date for Denver < 4/10/2020, we skip it; Atlanta meet contraints, it is added

enter image description here

Ex3: Denver is added; Atlanta not, since Stg.Pilot=E, which equals to MaxDate row in Final

enter image description here

Tables for the first example:

Create table Final
(Destination VARCHAR(55), Pilot VARCHAR(55), Date Date)

Create table Stg
(Destination VARCHAR(55), Pilot VARCHAR(55), Date Date)

INSERT Final Values

('Denver',  'A',    '4/1/2020'   ) ,
('Denver',  'B',    '4/5/2020'   ) ,
('Denver',  'C',    '4/7/2020'   ) ,
('Denver',  'A',    '4/10/2020'  ) ,
('Atlanta', 'C',    '4/2/2020'   ) ,
('Atlanta', 'D',    '4/4/2020'   ) ,
('Atlanta', 'E',    '4/8/2020'   )


INSERT Stg Values
('Denver',  'F',    '4/13/2020'  ) ,
('Atlanta', 'G',    '4/9/2020'   )

Here is my try, but things seem not to work properly

  WITH Test AS (SELECT Destination,Pilot,Date, ROW_NUMBER() OVER (PARTITION BY Pilot, ORder by Date Desc) as rn FROM Final)
  INSERT INTO Final (Destination,Pilot,Date)
  SELECT s.Destination,s.Pilot,s.Date
  FROM   Stg s
  JOIN   Test t on s.Destination = t.Destination AND r.rn = 1 AND s.Date > t.Date and s.Pilot <> t.Pilot

Upvotes: 3

Views: 67

Answers (1)

Dale K
Dale K

Reputation: 27224

I think rather than an inner join you want a not exists. Also you were partitioning over Pilot whereas from your question I understand you to want to partition over Description e.g.

declare @Final table (Destination VARCHAR(55), Pilot VARCHAR(55), [Date] Date);
declare @Stg table (Destination VARCHAR(55), Pilot VARCHAR(55), [Date] Date);

INSERT into @Final Values
('Denver',  'A',    '4/1/2020'   ),
('Denver',  'B',    '4/5/2020'   ),
('Denver',  'C',    '4/7/2020'   ),
('Denver',  'A',    '4/10/2020'  ),
('Atlanta', 'C',    '4/2/2020'   ),
('Atlanta', 'D',    '4/4/2020'   ),
('Atlanta', 'E',    '4/8/2020'   );

INSERT into @Stg Values
('Denver',  'F',    '4/13/2020'  ),
('Atlanta', 'G',    '4/7/2020'   );

--select * from @Final;
--select * from @Stg;

WITH Test AS (
  SELECT Destination, Pilot, [Date], ROW_NUMBER() OVER (PARTITION BY Destination ORDER BY [Date] DESC) AS rn
  FROM @Final
)
--select * from Test where rn = 1
INSERT INTO @Final (Destination, Pilot, [Date])
SELECT S.Destination, S.Pilot, S.[Date]
FROM @Stg S
WHERE NOT EXISTS (
  SELECT 1
  FROM Test T
  WHERE rn = 1
  AND S.Destination = T.Destination
  AND (
    T.Pilot = S.Pilot
    OR T.[Date] >= S.[Date]
  )
);

select * from @Final order by Destination, [Date];

Gives, as required:

Destination | Pilot | Date
------------------------------
Atlanta     | C     | 2020-04-02
Atlanta     | D     | 2020-04-04
Atlanta     | E     | 2020-04-08
Atlanta     | G     | 2020-04-09
Denver      | A     | 2020-04-01
Denver      | B     | 2020-04-05
Denver      | C     | 2020-04-07
Denver      | A     | 2020-04-10
Denver      | F     | 2020-04-13

Upvotes: 2

Related Questions