SELECT DISTINCT GRPID, PLNCD, XPLNE, EXTNO, EffDate, TermDate FROM History WHERE MBRNO IN (SELECT MBRNO FROM dbo.Member_MRN_lkupAllMBRNOs(@strMBRNO)) ORDER BY EffDate ASC

How large (MB/GB) is the "History" table?

How is it...

dbo.Member_MRN_lkupAllMBRNOs is a table valued function.

Can you provide the code?

The execution plan of the query would also be helpful.

Open the script from the control, a new instance of VS will appear with the script in.

Then save the script (ctrl+s) and close the new instance of VS.

Then save the...

SELECT *
  FROM myTable
 WHERE NOT(Sale = 'N' AND Purchase = 'N')

SELECT *
  FROM myTable
 WHERE (Sale <> 'N' OR Purchase <> 'N')

;WITH CTE AS
(
    SELECT t1.Code,
           t1.Name,
        ...

;with cte as 
(
    select N
      from (values (1000),(10001),(100002),(1000003),(1000004),(100000005),(1000000006)) T(N)
)
SELECT N,D+(N-(D+E+F)) D,E,F
from cte
cross apply(values (0.986 * N , 0.012*N,...

You cannot include DECLARE inside a SQL statement.

Your SQL should look more like this:

DECLARE @keysValueToSearch NVARCHAR(4000) = 'from'
DECLARE @untilThisCharAppears NVARCHAR(4000) = 'where'
DECLARE @keysValueToSearchPattern NVARCHAR(4000) = '%' +...

If you install this function: https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function

You can then run this code:

;WITH CTE as 
(
    SELECT * 
      FROM...

I saw that and was totally impressed.  It only took them 5 years to get a fix out... kind of.  As you say, it's only in Azure so far....

SQL Server now, at last, has added an addition (enable_ordinal) parameter to the STRING_SPLIT function.

This returns a column called ordinal the same value as the ItemNumber column in DelimitedSplit8K

Just one more question...

If there are no values, thus no sums in the Pivot Query below, is there a way to have it return zero's?

SELECT pt.[imtPartID],...

Does an estimated execution plan find any missing indexes?

To speed the query up you could try putting an index on the Customer table with the leading edge on columns Customer,...

For this type of design it is normal practice to have a table to join on that relates the 3-character code to the Comments table.

e.g:

CREATE TABLE a3ltrcodeTBL_Comments 
(
 ...

From what you say maybe you could join the tables with a wildcard?:

SELECT a.3ltrcode, 
       b.CommentCode
  FROM 3ltrcodeTBL a
 INNER...

I think this is the equivalent of your original statement.

CREATE PROCEDURE [dbo].[Sel_Transf_Ext_Hist]
(
    @Customer INT, 
    @Session  INT = 0
)
AS
SELECT DISTINCT
...