Convert multiple self JOINs to window function or subqueries (SQL)

I have a query with several self joins:

SELECT t2.Name, t2.Date as date, t2.Value as value1, t3.Value as value2, t4.Value as value3
FROM table_t t1
    RIGHT JOIN #tempPredefinedIndicators t2
        ON t1.Name = t2.Name
    JOIN #tempPredefinedIndicators t3 
        ON t2.Name = t3.Name AND t2.Date = t3.Date
    JOIN #tempPredefinedIndicators t4 
        ON t2.Name = t4.Name AND t2.Date = t4.Date    
WHERE t1.ValueName = 'one' AND DATEDIFF(year, ISDATE(t1.Value), GETDATE()) >=10
AND (t2.ValueName = 'two' AND TRY_CONVERT(FLOAT, t2.Value) > 0.15)
AND (t3.ValueName = 'three' AND TRY_CONVERT(FLOAT, t3.Value) BETWEEN 0 AND 0.5)
AND (t4.ValueName = 'four' AND TRY_CONVERT(FLOAT, t4.Value) > 0.15)

Here is a sample table where in yellow I marked the record that is within the boundaries of the where condition:

Here is the expected result view:

Basically I filter one table with several consecutive conditions and show the result as a table.

Is there a way, using window function or subquery to transform it to be faster?

Answer

You could improve performance by removing type conversions, adding indicies to the appropriate columns being queried so often, or by replacing a 4-table join with a PIVOT instead.

SELECT * FROM (
  SELECT
    [name],
    [valuename],
    [value]
  FROM table_t
  WHERE 
    (valuename = 'one' and datediff(year, isdate(value), getdate()) > 10)
     or 
    (valuename = 'two' and TRY_CONVERT(FLOAT, Value) > 0.15)
     or 
    (ValueName = 'three' AND TRY_CONVERT(FLOAT, Value) BETWEEN 0 AND 0.5)
     or
    (ValueName = 'four' AND TRY_CONVERT(FLOAT, Value) > 0.15)
) a
PIVOT (
  MAX([value])
  FOR [valuename]
  IN (
    [one], [two], [three], [four]
  )
) AS PivotTable

Teach yourself through examples here: https://www.sqlshack.com/dynamic-pivot-tables-in-sql-server/

Your query took 30ms to run in sql-fiddle; it only took 17ms to run the pivot w/o type-conversions in the WHERE clause, and 20-22ms to run with all the WHERE clause conversions.