IF EXISTS (SELECT name FROM sys.tables WHERE name='flights')
DROP TABLE flights
GO
CREATE TABLE flights (
-- KEY columns
flight_date date,
flight_number smallint,
dep char(3), -- Departing airport code. LAX = Los Angeles, MSP = Minnespolis/St. Paul, etc.
reg char(3), -- aka number on the tail of the plane, N810SY, but for this demo only using the three numbers = 810
-- NON-KEY columns along for the ride
arr char(3), -- arriving station.
-- SCHEDULED TIMES: Out means leafing the dep airport gate, in means arriving at the arr airport gate. Always populated.
-- Normally datetime, but shortened to time for this demo.
scheduled_out_tm time(0),
scheduled_in_tm time(0),
-- ACTUAL TIMES: Will have times ONLY when it happeneds, else it's scheduled and NULL.
actual_out_tm time(0),
actual_in_tm time(0),
-- ACTIVE: Used to detect a good flight for 'soft deletes', if you're not allowed to delete rows but can have update a bit column.
is_active char(1),
-- Auditing columns for who last touched the row.
entered_dt datetime NULL,
entered_by varchar(10) NULL,
duplicate_number tinyint NULL)
INSERT INTO flights (flight_date, flight_number, reg, dep, arr, scheduled_out_tm, scheduled_in_tm, actual_out_tm, actual_in_tm, entered_dt, entered_by, duplicate_number)
VALUES
-- Flight 101 MSP-BOS-NY are actual flights. Good to go.
('2014-07-15', '101', '816', 'MSP', 'BOS', '08:00:00', '12:00:00', '08:00:00', '12:00:00', '2014-07-15 12:05:00', 'Jack', NULL),
('2014-07-15', '101', '816', 'BOS', 'NY', '13:00:00', '13:45:00', '13:00:00', '13:45:00', '2014-07-15 14:00:00', 'W.P.', NULL),
-- Flight 102 MSP-MIA-HAV hasn't happened yet, so scheduled flight with no actual times. Good to go.
('2014-07-15', '102', '802', 'MSP', 'MIA', '08:00:00', '12:00:00', NULL, NULL, '2014-06-01 00:00:00', 'Scheduling', NULL),
('2014-07-15', '102', '802', 'MIA', 'HAV', '13:00:00', '14:00:00', NULL, NULL, '2014-06-01 00:00:00', 'Scheduling', NULL),
-- Duplicate #1: Flight 103 has two actuals on the MSP-PHX leg, five minutes apart. Note: Temps rise thirty degrees upon descent to PHX.
('2014-07-15', '103', '801', 'MSP', 'PHX', '04:00:00', '6:00:00', '04:00:00', '6:00:00', '2014-07-15 06:05:00', 'Jack', NULL),
('2014-07-15', '103', '801', 'MSP', 'PHX', '04:00:00', '6:00:00', '04:00:00', '6:00:00', '2014-07-15 06:10:00', 'Jack', 1),
('2014-07-15', '103', '801', 'PHX', 'SJC', '07:00:00', '08:45:00', '07:00:00', '08:45:00', '2014-07-15 09:00:00', 'Miriam', NULL),
-- Duplicate #2: Flight 104 has a SCHEDULED and an ACTUAL row for MSP-DTW. The business only wants to see the actual.
('2014-07-15', '104', '816', 'MSP', 'DTW', '16:00:00', '18:45:00', '16:00:00', '18:45:00', '2014-07-15 18:50:00', 'Jack', NULL),
('2014-07-15', '104', '816', 'MSP', 'DTW', '16:00:00', '18:45:00', NULL, NULL, '2014-06-01 00:00:00', 'Scheduling', 2),
('2014-07-15', '104', '816', 'DTW', 'MSP', '08:00:00', '12:00:00', '12:05:00', '12:00:00', '2014-07-15 18:50:00', 'Bob', NULL),
-- Flight 105 MSP-LAN and back are actual flights. Go Green. Go White!
('2014-07-15', '105', '814', 'MSP', 'LAN', '08:00:00', '12:00:00', '08:00:00', '12:00:00', '2014-07-15 12:05:00', 'Jack', NULL),
('2014-07-15', '105', '814', 'LAN', 'MSP', '13:00:00', '13:30:00', '13:00:00', '13:30:00', '2014-07-15 12:05:00', 'Sparty', NULL),
-- Flight 106 MSP-CZM is an in-progress flight. Good to go.
('2014-07-15', '106', '802', 'MSP', 'CZM', '08:00:00', '12:00:00', '08:00:00', NULL, '2014-07-15 12:05:00', 'Jack', NULL),
-- Duplicate #3: Flight 107 has two scheduled on the MSP-SFO leg, all rows identical
('2014-07-15', '107', '802', 'MSP', 'SFO', '04:00:00', '6:00:00', NULL, NULL, '2014-06-01 00:00:00', 'Scheduling', NULL),
('2014-07-15', '107', '802', 'MSP', 'SFO', '04:00:00', '6:00:00', NULL, NULL, '2014-06-01 00:00:00', 'Scheduling', 3),
-- Duplicate #4: Flight 108 has two scheduled on the MSP-SFO leg, all rows identical except entered_dt
('2014-07-15', '108', '811', 'MSP', 'SFO', '04:00:00', '6:00:00', NULL, NULL, '2014-06-01 00:00:00', 'Scheduling', NULL),
('2014-07-15', '108', '811', 'MSP', 'SFO', '04:00:00', '6:30:00', NULL, NULL, '2014-06-02 00:00:00', 'Scheduling', 4),
-- Flight 109 MSP-LAS-MSP. 10 flights/day in the Minnesota winter, only one in the summer. Good to go.
('2014-07-15', '109', '881', 'MSP', 'LAS', '07:15:00', '9:15:00', '07:15:00', '9:15:00', '2014-07-15 09:30:00', 'Mikey', NULL),
('2014-07-15', '109', '881', 'LAS', 'MSP', '11:15:00', '15:15:00', '11:15:00', '15:15:00', '2014-07-15 15:30:00', 'Jimbo', NULL),
-- Flights 110 and 111 are both MSP-Chicago Midway for the Chicago rush hour. Good to go.
('2014-07-15', '110', '888', 'MSP', 'MDW', '07:00:00', '9:00:00', '07:00:00', '9:00:00', '2014-07-15 09:30:00', 'Flavo', NULL),
('2014-07-15', '111', '889', 'MSP', 'MDW', '07:10:00', '9:10:00', '07:10:00', '9:10:00', '2014-07-15 09:30:00', 'Valentino', NULL),
-- Duplicate #5: Flights 112 is an air return, where they took off, realized they forgot the coffee, went back to MSP, got coffee, and completed the flight.
('2014-07-15', '112', '891', 'MSP', 'MSP', '07:00:00', '07:30:00', '07:00:00', '07:30:00', '2014-07-15 07:50:00', 'Jack', NULL),
('2014-07-15', '112', '891', 'MSP', 'POR', '07:00:00', '09:45:00', '07:45:00', '09:45:00', '2014-07-15 09:30:00', 'Greg', 5),
-- Flight 113 had a 'tail swap' when they changed planes for the MSP-SEA leg, but didn't delete the scheduled row for the first plane. Good to go.
('2014-07-15', '113', '805', 'MSP', 'SEA', '07:00:00', '9:00:00', NULL, NULL, '2014-06-01 00:00:00', 'Scheduling', NULL),
('2014-07-15', '113', '842', 'MSP', 'SEA', '07:15:00', '9:00:00', NULL, NULL, '2014-06-02 00:00:00', 'Scheduling', NULL)
-- Display the table
SELECT * FROM flights
SELECT DISTINCT flight_date, flight_number, scheduled_out_tm, reg
FROM flights
ORDER BY flight_date, flight_number, scheduled_out_tm, reg
SELECT flight_date, flight_number, scheduled_out_tm, reg
FROM flights
GROUP BY flight_date, flight_number, scheduled_out_tm, reg
ORDER BY flight_date, flight_number, scheduled_out_tm, reg
SELECT flight_date, flight_number, dep, reg
FROM flights
GROUP BY flight_date, flight_number, dep, reg
HAVING COUNT(*) > 1
;WITH a as (
SELECT flight_date, flight_number, dep, reg,
row_number() OVER (PARTITION BY flight_date, flight_number, dep, reg ORDER BY entered_dt) as row_number
FROM flights
)
SELECT * FROM a WHERE row_number > 1
Here are the return sets:
;with a as (
SELECT flight_date, flight_number, dep, reg,
row_number() OVER (partition by flight_date, flight_number, dep, reg ORDER BY ISNULL(actual_out_tm, '23:59:59'), entered_dt) as row_number
FROM flights
)
DELETE FROM a WHERE row_number > 1
-- Set all is_active values to 'Y' (just for the demo)
UPDATE flights
SET is_active='Y'
GO
-- Now set all duplicate rows is_active='N'
;with f as (
SELECT *,
row_number() over(partition by flight_date, flight_number, scheduled_out_tm, reg order by ISNULL(actual_out_tm, '23:59:59'), entered_dt) as row_number
FROM flights
)
UPDATE f
SET is_active='N'
WHERE row_number > 1
BEGIN TRAN tr
BEGIN TRY
-- Delete query goes here
SELECT CAST(@@ROWCOUNT as varchar(max)) + ' rows were deleted'
-- Show the rowcount post-delete
SELECT 'After the delete there are ' + CAST(COUNT(*) as varchar(max)) + ' rows in table flights.'
FROM flights
-- Show the table post-delete
SELECT * FROM flights
-- VERY IMPORTANT! If you don't want to actually save the delete, ROLLBACK goes here.
-- If you do want to save then COMMIT goes here.
ROLLBACK TRAN TR
END TRY
BEGIN CATCH
SELECT @@ERROR
ROLLBACK TRAN TR
END CATCH
END
SELECT * INTO z_flights_backup_20140907 FROM flights WHERE 1=0
DELETE f
OUTPUT DELETED.* INTO z_flights_backup_20140907
FROM flights f
JOIN (
SELECT a.flight_date, a.flight_number, a.dep, reg, a.entered_dt, a.rank_order
FROM (
SELECT flight_date, flight_number, dep, reg, entered_dt, RANK() OVER (PARTITION BY flight_date, flight_number, dep, reg ORDER BY entered_dt DESC) as rank_order
FROM flights) a
WHERE rank_order > 1 ) del
-- JOIN on the key columns
ON del.flight_date = f.flight_date AND del.flight_number = f.flight_number AND del.dep = f.dep AND del.reg = f.reg
-- and the non-key column used to define duplicates
AND del.entered_dt = f.entered_dt
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)