Hi

The 2nd table will only have 1 entry for the person employment status from the first table whether it would be "Manager", "Fired" or "Enjoy Life". It will not...

Don't use OLEDB Command for updates ... unless your number of updates is tiny. It's dog slow.

Instead, direct all of your update rows to (yet) another staging table (as INSERTs),...

Really?

How is COALESCE ever going to take ('Manager', 'Fired', 'Enjoying Life') as its arguments and return 'Manager Fired Enjoying Life'?

It would be interesting to know more about the  process which is taking all the time.

Do you want concatenation or coalescence of the 3 columns?  Can be 1 and only 1 value per row in these 3 columns?  Could all 3 columns have values...

Because you've effectively turned the LEFT...

I would not recommend that approach - combining discrete data elements into a single column will make it much harder to work with that data.

What is the purpose of...

If the values are, as you suggest:

Employed (String) - "Manager"

Non-Employed (String) - "Fired"

Retired (String) - "Enjoying Life"

what would you set Employed Status to?

Looks to me as though you can simplify your WHERE clause to

WHERE j.Code <> 'L3'

and do the whole thing as a single INSERT.

What are the possible values in the three source columns and how do they map to the target column?

It's a subquery within a subquery (a subsubquery??) – it's much easier to read with some formatting applied:

BEGIN
    SELECT COUNT(*)
    FROM...

Since I'm using SSIS to map the data from the flat file to my staging table I prefer to get all the data in the staging table, then try...

• This reply was modified 6 years, 5 months ago by Phil Parkin. Reason: Fix typo

Do you receive any error messages?

Use something like this to get your start and end dates:

DECLARE @StartDate DATE = DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0);
DECLARE @DayNo TINYINT = DAY(GETDATE());

IF @DayNo > 15
 ...

• This reply was modified 6 years, 5 months ago by Phil Parkin. Reason: Fix error in query

Is that a statement or a question?