What is the problem? Can you be more specific?

I can't see any easy way of accommodating scenario four in a MERGE, because MERGE does not allow an INSERT in the case of a match.

You could experiment with changing...

No.

CASE is an expression, which returns a result....

The query 'brings all the data' because you are not filtering any of it out. If you wish to filter out data, use a WHERE clause.

I asked you about TrnYear,...

As an aside, as you're using SS 2019, I suggest you start using STRING_AGG() rather than the legacy FOR XML PATH method.

Makes your code much easier to read.

OK, so now you understand why you did not get 'NOTPRIMARY', I assume?

Well spotted, thanks for fixing that up.

If you run just the inside part of the query, as follows, what result do you get:

SELECT CASE
         ...

Can we recap on exactly what the problem...

Try this:

CREATE TABLE T
(
    title VARCHAR(100) NOT NULL
   ,Computed AS
        REPLACE(
    ...

By the way, your query is far easier to read if it is formatted and presented inside a code block, like this:

SELECT DISTINCT
    ...

First, note that your CASE expressions do not filter any data out. To do that requires a WHERE clause.

Let's be more specific and focus on DailycheckHome, for example.

First of all,...

Thanks, Phil for kind words. In my query I have been trying to filter following options but I know the query does not filter when I use a.TRNyear=2022 or...

I have created a query but not filtering properly. for example and I like to show it on the report where I have struggles it so far.

What is your question?...

Those are not INTs!! Change the target datatype to DECIMAL and you should have more success.