OUTER APPLY is the way to go, but you need to also modify the initial SELECT

SELECT ISNULL(ca1.Extension, a.Extension)
      ,ISNULL(ca1.Name, a.Name)
  ...

When you refer to 'spawning' of packages, is this done via multiple Execute Package tasks, or in some other way?

--Removed

• This reply was modified 3 years, 9 months ago by Phil Parkin.

You've been here long enough to know that your sample data should be provided in a consumable format, for us to cut & paste into SSMS.

Where does 'Extension' 1 come...

• This reply was modified 3 years, 9 months ago by Phil Parkin.

Please try using my modified query and see whether it gives the results you are hoping for:

WITH AffectedVehicles
AS (SELECT DISTINCT
       ...

I should get a chance to look at this again tomorrow regarding the additional engine requirement.

Didn't take long, maybe 30 minutes in total for both solutions.

Here are two duplicate rows: (x,y,z), (x,y,z)

Now tell me what your 'index column' (whatever...

• This reply was modified 3 years, 10 months ago by Phil Parkin.

I had a quick go at this, see whether it helps.

DECLARE @Mechanic VARCHAR(4) = 'Mike';
DECLARE @MessedUpStart DATETIME = '2000-01-03 14:00:00';
DECLARE @MessedUpEnd DATETIME = '2000-01-03 16:00:00';

DROP TABLE IF...

What do you hope to achieve by doing this? You cannot...

You don't.

First you decide on the desired PK. Then you address any dupes. Then you issue an ALTER to set the PK.

No, I only want to make it in OneQuery.

Remember, i only used this first query to get the list of what was changed during a period from a user...

So do you want two queries? One showing the situation before mess-up (which is the one already provided) and one showing the mess-up? That is, two separate resultsets?

Phil, I am sorry, I spoke too quickly.

I see that you DECLARE a fixed time DECLARE @MessedUpAt DATETIME = '2000-01-03 15:00:00';

But the mechanic can mess up for 2 hours.......

Thanks Phil, you did it! Please do you mind, explain high level how this works? You probably see that I do not have your level and I am interested...

Please check this out and let me know what you think. I used a temp table and converted your VARCHAR(1) to CHAR(1). If you want to know why VARCHAR(1) is...