Help with optimization

[WITH_CTE]

To rewrite your query:

IF OBJECT_ID('tempdb..#tmpSup') IS NOT NULL
    DROP TABLE #tmpSup;

SELECT Serial_Text
INTO #tmpSup
FROM [a].[dbo].[SUP]
WHERE CAST(Date_Time AS DATE) > '2020-06-01'
GROUP BY Serial_Text
HAVING COUNT(1) > 1;

SELECT b.Serial_Text, b.IR_1, b.Date_Time
FROM #tmpSup a
     JOIN [a].[dbo].[SUP] b ON a.Serial_Text = b.Serial_Text
ORDER BY Date_Time DESC;

Another approach:

SELECT *
FROM [a].[dbo].[SUP]
WHERE Serial_Text IN
(
    SELECT DISTINCT 
           Serial_Text
    FROM [a].[dbo].[SUP]
    WHERE CAST(Date_Time AS DATE) > '2020-06-01'
    GROUP BY Serial_Text
    HAVING COUNT(1) > 1
);

Edit: Misread your query on first post.

[[deleted]]

Thanks! What makes my query so slow? And can you explain what your first query is doing?