Maybe you could add the LAG columns to #MyData.  Then use a combination of LAG and MAX OVER.  If this were SQL Server 2022 you could use the WINDOW clause

Have a look at the Docs on "Multipart Names"

The variable @ObjectName provided as an example was not plural such that it corresponds to an actual system object.  Try it with N'sys.objects' instead of N'sys.object'.  YOU NEED TO PROVIDE...

If it's the code in your picture which produced the error message then your probable issue imo is a type mismatch between the join columns.  Either ar.S1WHS# or wf.L3WHS# is...

You could try using TRY_CAST to determine which row values are causing the CAST to fail

select *
from YourTable
where TRY_CAST(MASTER_BOL_NUMBER as numeric) is null;

What about making datatype specific fnTally-ies?  Many times the maximum requirement is less than BIGINT.  What about fnTallySmalldatetime, fnTallyDatetime?  What about making separate 0+ version and 1+ versions?

Any editorial regarding...

• This reply was modified 3 years, 1 month ago by Steve Collins.

It's only 2 rows of data.  To test with different cases I added some additional rows.  Afaik you're looking to conditionally apply the sum of invoiceid credits from 'creditmemo' to...

It seems you're looking for the COALESCE function.  If the empty values are stored as '' then NULLIF returns NULL.  COALESCE returns the first (left to right of the parameter...

There are a number of issues with the code provided imo.  Minor issues are DISTINCT is not necessary when used in conjunction with GROUP BY and inconsistent or absent table...

To split out the pd_id's you could try STRING_SPLIT to separate based on space delimiter.  Since some 'PD-[0-9]' values are encompassed by other extra characters a conditional substring is done...

• This reply was modified 3 years, 2 months ago by Steve Collins.

Thanks to Jeff Moden in this thread for the function used here

In row 1 of the expected output from which input table does the Rnge value 45 come from? ...

• This reply was modified 3 years, 2 months ago by Steve Collins.

THAT, good Sir, is a beautiful thing!  All you need to do now is give the author of that good function a little credit by citing the article where...

Afaik this actually produces the desired output.  Should work with SQL 2014.  It uses the ordinal splitter DelimitedSplit8K_Lead.  The query: splits the input string on '_' underscore, finds the offset...

Instead of a WHILE loop you could try a numbers table or tally function approach

declare @string varchar(255) = 'EMA_20210526T211254_0000_MRNMM0000001240_PMS110460PAT000001287_PID15235307_OR_Dr';

select string_agg(v.chr, '') within group (order by fn.n)...

These two statements are equivalent afaik.  Beginning with [Edit] 2022 (or compatibility level 150 in Azure SQL) they added the GREATEST function.  Prior ways were UNPIVOT using the built-in verb...

• This reply was modified 3 years, 2 months ago by Steve Collins.