can be lots of things

• has file been processed - requires another table with list of files already processed (filename, time last written and similar)
• do filenames match expected...

That's probably because you are summing over the joined dataset and the joined dataset contains more rows than the dataset purely from SCPOMGR.FCSTPERFSTATIC – ie, you are probably summing some...

I haven't looked at the tables, but I think a CURSOR may be worth considering here. I've rewritten your query to use a cursor – untested, see what you think:

Something like this?

DECLARE @Today DATE = GETDATE();
DECLARE @Yesterday DATE = DATEADD(DAY, -1, @Today);
DECLARE @TodayLess7 DATE = DATEADD(DAY, -7, @Today);
DECLARE @Hr TINYINT = DATEPART(HOUR, GETDATE());

SELECT @Yesterday
  ...

To make things a little clearer, here is that code again, but reformatted:

USE QPulse5Training;
GO

DECLARE @outPutPath VARCHAR(50) = 'C:\xp'
       ,@i BIGINT
 ...

The first problem that we have in trying to answer this is that we cannot see your database and therefore it is difficult to advise on the validity of JOINs.

Regarding...

With >3,000 points, you should know to provide a few things:

1. Sample DDL for your table
2. Sample data, in the form of INSERT statements for the table created in (1),...

That won't do it. When processing is transferred to your CATCH block, your SELECT merely returns the error message and the error is swallowed. I suggest the following slight modification:

It depends. Can you show us what is in your CATCH block?

SELECT
    [Shelf Location], date /*, ...*/FROM (
    SELECT *, DENSE_RANK() OVER(PARTITION BY [Shelf Location] ORDER BY date DESC) AS rank_num
...

Without DDL and sample data, this is largely an educated guess, but may get you started

With Ordered as (SELECT Grp = DENSE_RANK() OVER (PARTITION BY ShelfLocation ORDER...

Even 12 years ago, as far as I remember, SSIS would have to have been installed to allow the unattended running of SSIS packages, and the same is true today.

However,...

No. If you really need/want to convert the unique index to a primary key, you will have to drop the index and create the primary key - I don't...

If we were to provide the code which produced the desired results, but using your a,b,c,x,y,z data, it would work fine using this sample data, but probably not for your...

Sounds like it's encode problem, if I am honest. Perhaps, for example, you're opening the file expecting it to be encoded in UTF-16 and it's actually in ANSI-1252 or...