Doing it this way may help. It assumes that twdatum is a datetime column and that there is a suitable index in place (eg, on (twdatum, asis)).

DECLARE...

Try changing ELSE 0 to ELSE NULL.

Otherwise you are just counting zeros rather than ones, which explains the behaviour you are seeing.

Runs fine for me. Maybe now you appreciate that different flavours of SQL have their...

Here's another version.

DROP TABLE IF EXISTS #t;

CREATE TABLE #t
(
    Part INT NOT NULL
   ,lowest_cost DECIMAL(19, 6) NOT NULL
   ,date_received DATE...

No worries. Scott's solution should work for you.

I have not taken the time to analyse your code, but I would suggest leaving the pivoting to SSRS. If you can get results in the form

Username, DBName, Roles

(where Roles...

I often use this site when looking at JSON strings. I pasted your J1 version into it and, as you will see from the image below, it is perfectly...

What is the datatype of the Input variable? If numeric, the values 1.1, 1.10 and 1.100 are indistinguishable.

I suggest you consider writing a stored procedure, with parameters, to return your desired dataset. This sort of thing:

CREATE PROCEDURE dbo.GetSomeData @StartDate DATE, @EndDate DATE
AS
SELECT t.c1, t.c2
FROM...

Your final WHERE clause makes no sense:

WHERE BusinessEntityID >= @BusinessEntityId AND BusinessEntityID <= @BusinessEntityId;

This is equivalent to

WHERE BusinessEntityID = @BusinessEntityId

Is that what you meant?

As you're on 2019, use the TRIM() function instead. Do the trim first.

LEFT(TRIM())

If this works, it's a bit simpler. Can't remember how SSIS handles NULL values in expressions like this, so might not work.

replace(FirstName + " " + ...

Hashing is a one-way process ... you can't get back to the original value if you know only its hashed value.

Thanks for the reply, Phil! I think I could handle a simple query like that as well. Where it starts getting rough for me is when there are [x]...

varchar cause there are letter in it ...