Using the sample data DDL Bert generously supplied, here's an alternative that reads the base table 1 time instead of 6:

SELECT Col1,
   Col2=CASE WHEN...

On every server on which I've run your code, I can reproduce the behavior, but only when the resulting string would be more than 8000 bytes (note that since you...

The trick is that for a given concatenation of two strings using the + operator, the result will be truncated at 8000 bytes unless one of the operands has a...

Yeah, since you're dealing with such high precisions and scales in the multiplied types, each multiplication will just end up yielding a DECIMAL (38,6) per the third rule for multiplication...

Here's another approach that only requires reading the table once, but it's not quite as straightforward as doing the FULL OUTER JOIN; whether such an approach will work better for...

Something like this is one way, with a FULL OUTER JOIN.

SELECT ChangeDate =ISNULL(post.SnapshotDate,DATEADD(DAY,7,pre.SnapshotDate)),
       ProjectName=COALESCE(pre.ProjectName,post.ProjectName),
       ChangeType =CASE WHEN post.SnapshotDate IS NULL THEN...

Here's another way:

WITH CTE AS
(
SELECT
id
,grp
,rnk  =RANK() OVER (PARTITION BY ABS(id) ORDER BY ID DESC)
,grp_rnk =RANK() OVER...

Well, you don't have to worry about a leading 0; INTs don't have such things 🙂

.For the month, should be sufficient to check that the column value modulo...

Yeah, since you no longer need the TP_Name column when the CASE expression is commented out, SQL Server doesn't even need to access any tables other than Sent and ErrorLog,...

The TP_Name column isn't used anywhere else in the query, so possibly something as simple as indexes that are covering without the CASE expression no longer are once that column...

Sure!

Because we're doing a left join with the criteria that the employeeid match and that the shipcountry is Mexico, the rows coming out of the join will have...