1

Sorry if this seems like rambling I'm not typically a DB person just trying to work through an issue.

I have two tables cases and legal_entity the data on the two tables looks like this

legal_entity

first_name last_name address_1_zip
Maria Munoz 33122
John Glenn 10003
Melissa Johnson 90210

cases

style case_number create_date
Munoz, Maria SL12345 2021-03-01 11:45:26.657
Glenn, John SL45678 2021-03-02 12:10:14.552
Johnson, Melissa (DNY) SG42561 2021-04-10 01:06:10.214

What I am trying to do is combine the last_name and first_name fields from the legal_entity table with a comma between them, and match (contains) it against the style field in the cases table. For everything in the last month basically. This is the query I'm using

SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    CPSQL.dbo.legal_entity AS LE
INNER JOIN CPSQL.dbo.cases as C ON LE.CombinedName=C.style
WHERE C.create_date >= Convert(datetime, '2021-03-01') AND C.create_date <= Convert(datetime, '2021-04-16')
ORDER BY C.create_date

The issue I run into is that it seems to not like the LE.CombinedName so I'm not sure how to reference the concatenated string against the style column in the cases table.

Any help would be greatly appreciated.

2
2

You can use only columns from the joined tables or in your case concatenated version of it

SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    CPSQL.dbo.legal_entity AS LE
INNER JOIN CPSQL.dbo.cases as C ON CONCAT_WS(', ', LE.last_name,LE.first_name)=C.style
WHERE C.create_date >= Convert(datetime, '2021-03-01') AND C.create_date <= Convert(datetime, '2021-04-16')
ORDER BY C.create_date
CREATE TABLE legal_entity
    ([first_name] varchar(7), [last_name] varchar(7), [address_1_zip] int)
;
    
INSERT INTO legal_entity
    ([first_name], [last_name], [address_1_zip])
VALUES
    ('Maria', 'Munoz', 33122),
    ('John', 'Glenn', 10003),
    ('Melissa', 'Johnson', 90210)
;
CREATE TABLE cases
    ([style] varchar(22), [case_number] varchar(7), [create_date] varchar(23))
;
    
INSERT INTO cases
    ([style], [case_number], [create_date])
VALUES
    ('Munoz, Maria', 'SL12345', '2021-03-01 11:45:26.657'),
    ('Glenn, John', 'SL45678', '2021-03-02 12:10:14.552'),
    ('Johnson, Melissa (DNY)', 'SG42561', '2021-04-10 01:06:10.214')
;
SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    legal_entity AS LE
INNER JOIN cases as C ON CONCAT_WS(', ', LE.last_name,LE.first_name)=C.style
WHERE C.create_date >= Convert(datetime, '2021-03-01') AND C.create_date <= Convert(datetime, '2021-04-16')
ORDER BY C.create_date
GO
first_name | last_name | address_1_zip | CombinedName
:--------- | :-------- | ------------: | :-----------
Maria      | Munoz     |         33122 | Munoz,Maria 
John       | Glenn     |         10003 | Glenn,John  

db<>fiddle here

CREATE TABLE legal_entity
    ([first_name] varchar(7), [last_name] varchar(7), [address_1_zip] int)
;
    
INSERT INTO legal_entity
    ([first_name], [last_name], [address_1_zip])
VALUES
    ('Maria', 'Munoz', 33122),
    ('John', 'Glenn', 10003),
    ('Melissa', 'Johnson', 90210)
;
GO
CREATE TABLE cases
    ([style] varchar(22), [case_number] varchar(7), [create_date] varchar(23))
;
    
INSERT INTO cases
    ([style], [case_number], [create_date])
VALUES
    ('Munoz, Maria', 'SL12345', '2021-03-01 11:45:26.657'),
    ('Glenn, John', 'SL45678', '2021-03-02 12:10:14.552'),
    ('Johnson, Melissa (DNY)', 'SG42561', '2021-04-10 01:06:10.214')
;
GO
SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    legal_entity AS LE
INNER JOIN cases as C ON PATINDEX( CONCAT( LE.last_name,', ',LE.first_name,'%'),C.style) > 0
WHERE C.create_date >= Convert(datetime, '2021-03-01') AND C.create_date <= Convert(datetime, '2021-04-16')
ORDER BY C.create_date
GO
first_name | last_name | address_1_zip | CombinedName   
:--------- | :-------- | ------------: | :--------------
Maria      | Munoz     |         33122 | Munoz,Maria    
John       | Glenn     |         10003 | Glenn,John     
Melissa    | Johnson   |         90210 | Johnson,Melissa

db<>fiddle here

2
  • This looks to be working except for cases where C.style contains more information than just LastName,FirstName (Ex. Johnson, Melissa (DNY)) it doesn't match even if you change the = to LIKE. – Cistoran Apr 16 at 17:36
  • yes but your question was about why is that not working, as the pattern go colpexer you will need much more soffisticated search pattern, and of you want also to compansate for mispellings you will open the next can of worms. – nbk Apr 16 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.