On my SQL 2019 instance, the behaviour does not match your explanation

This should get you going in the right direction

;WITH cteBase AS (
  SELECT TABLE1.ID, TABLE2.Processingnumber
    , rn = ROW_NUMBER() OVER (PARTITION BY TABLE1.ID...

The following code will not divide the values exactly.  But since you have a large number of records, it uses the card number to split the records.  It will guarantee...

I believe that you are looking for fuzzy matches.  I have never had to do it before, but this looks like it might get you started

https://www.google.com/search?rlz=1C1GCEA_enZA912ZA912&sxsrf=ALeKk00uYiKdhg1PIAY7XR2lKfHio_Q_cg%3A1603725887965&ei=P-qWX-O5OsuM8gKosZMo&q=sql+fuzzy+match&oq=sql+fuzzy&gs_lcp=CgZwc3ktYWIQAxgBMgUIABDJAzICCAAyAggAMgIIADICCAAyAggAMgIIADIHCAAQFBCHAjICCAAyAggAOgcIABBHELADOgQIIxAnOggIABDJAxCRAjoFCAAQkQI6BAgAEEM6BwgAEMkDEENQnf8EWPCKBWCwsgVoAXAAeACAAccDiAHhDJIBBzItMi4yLjGYAQCgAQGqAQdnd3Mtd2l6yAEIwAEB&sclient=psy-ab

You can take this further and split the rows into columns like this

DECLARE @XMLData xml;

SET @XMLData = '<PlaceDescriptions>
<descriptions>0|Home</descriptions>
<descriptions>1|Office|False</descriptions>
<descriptions>2|Play Ground</descriptions>
<descriptions>3|School|True</descriptions>
<descriptions>4|Movie Hall</descriptions>
</PlaceDescriptions>';

WITH cteXML AS (
  SELECT  descriptions...

Firstly, you don't need to use sp_xml_preparedocument and OPENXML.

Secondly, your xml is not properly formed - There is an extra ">" before "</PlaceDescriptions>"

Below is code that will extract the descriptions...

• This reply was modified 4 years, 12 months ago by DesNorton.

I love the use of math to solve this.  However, I am stumped as to why/how the math works.  An explanation for Dummies would be greatly appreciated.

y /7...

I love the use of math to solve this.  However, I am stumped as to why/how the math works.  An explanation for Dummies would be greatly appreciated.

y /7 -- Full...

It appears that you may have a mismatch between your @ColumnName and your @sql

• @ColumnName returns [April-19], [August-19], etc
• @sql returns MonthYearOf as [April 19], [August 19], etc

            
October 20, 2020 at 5:02 am
            
#3799096

            
            
                                                
        

You will  need to test, but using PATINDEX *might* give you slightly better performance.

SELECT T1.DocumentNo, T2.LongSentence
FROM Table2 T2
INNER JOIN Table1 T1 ON PATINDEX('%' + T1.DocumentNo + '%',...

Since your query is returning no results, it cannot display a yes/no answer.

What you need to do is change it so that it always returns a result

SELECT...

Looks like you are missing a closing quote in the WHEN part of your case statement.

Here's my version, also no recursion, with added alias names for more clarity:

SELECT 
    start_date, end_date,
    full_weeks * 2 +
...

You can use a splitter function, and a cross tab to get what you are looking for

DECLARE @TestData table (JoinedData varchar(50));

INSERT INTO @TestData ( JoinedData )
VALUES (...

In my previous post I stated that you would find more data anomalies - Wonder how I knew that.

This is not something that can be fixed with a single query. ...