sql server - SQL JOIN two tables using two condition and partial match

Question

Let's imagine that there are two tables with independent data.

T1
Name                      Value    Time
----------------------------------------
equipment.hour.01         10       12:00:00.000     
equipment.hour.02         12       13:00:00.000 
equipment.hour.05         20       15:00:00.000 
equipment.hour.07         01       15:00:00.000 

T2
Name                      Value    Time
----------------------------------------
total.count.01         3       12:04:30.456     
total.count.02         5       13:06:01.324 
total.count.05         6       15:20:56.268 
total.count.07         8       15:40:12.570 

I want to get a column as Fault Count from second tables as follows using join clause.

Name    Value    Fault Count
--------------------------------
01      10       3
02      12       5
05      20       6
07      01       8

I want the device value and alarm numbers at the same time. I wrote a query for this, but could not solve the errors that came with not getting what I wanted. I don't know exactly where I went wrong. Can you help me?

select a.Name,a.Value,b.[Fault Count] from
(SELECT
 REVERSE(PARSENAME(REPLACE(REVERSE(t1.Name), ',', '.'), 4)) as 'Name', 
 dbo.t1.Value,
 dbo.t1.Time
   from t1 ) as a
JOIN 
(SELECT
 REVERSE(PARSENAME(REPLACE(REVERSE(t2.Name), ',', '.'), 4)) as 'Name', 
 t2.Value as 'Fault Count',
 t2.Time
   from dbo.t1 ) as b
   ON
   a.Name = b.Name and datepart('hour',a.Time) = DATEPART('hour',b.Time)

question from:https://stackoverflow.com/questions/65887626/sql-join-two-tables-using-two-condition-and-partial-match

Answer 1

Just extract the last two characters and join:

select right(t1.name, 2) as name, t1.value, t2.value as fault_count
from t1 join
     t2
     on right(t1.name, 2) = right(t2.name, 2);

Notes:

• In the sample data, the resulting names are unique. If there are duplicates, you might need aggregation.
• In the sample data, the resulting names are in both tables. This returns only names that are in both. If you want names that are missing from one of the tables, then use an outer join of some sort.