T-SQL Min and Max

Question

I am trying to get the min and max values from the results of the first part of my query:

SELECT DISTINCT
    a.[Recording Date], 
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        TRIM([Document Number]), 
        '0','n'),'1','n'),'2','n'),'3','n'),'4','n'),'5','n'),'6','n'),'7','n'),'8','n'),'9','n') AS pattern
FROM 
    [TPEConversionTeam].[dbo].[KY_Shelby_Recorder_History] a
WHERE 
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        TRIM([Document Number]),
        '0','n'),'1','n'),'2','n'),'3','n'),'4','n'),'5','n'),'6','n'),'7','n'),'8','n'),'9','n') = 'nnnnnn-n'

Expected results should be 1993-01-06 and 2023-12-31:

But I'm getting everything back so looks like my subquery is not doing anything.

My whole query:

SELECT DISTINCT
    a.[Recording Date], 
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        TRIM([Document Number]),
        '0','n'),'1','n'),'2','n'),'3','n'),'4','n'),'5','n'),'6','n'),'7','n'),'8','n'),'9','n') as pattern
FROM 
    [TPEConversionTeam].[dbo].[KY_Shelby_Recorder_History] a
WHERE   
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        TRIM([Document Number]),
        '0','n'),'1','n'),'2','n'),'3','n'),'4','n'),'5','n'),'6','n'),'7','n'),'8','n'),'9','n') = 'nnnnnn-n'
      (SELECT MIN([Recording Date]) AS MIN_DATE
       FROM [TPEConversionTeam].[dbo].[KY_Shelby_Recorder_History]
       UNION
       SELECT MAX([Recording Date]) AS MAX_DATE
       FROM [TPEConversionTeam].[dbo].[KY_Shelby_Recorder_History])

Answer 1

What you are talking about is a very basic grouping query. I've left out all the replacements as that's irrelevant to the way you write the query - using a very simple replace function instead. This is not really about subquery, just about grouping.

declare @KY_Shelby_Recorder_History table (
    Id int identity(1,1),
    [Recording Date] date,
    [Document Number] nvarchar(100),
    primary key (Id))

insert into @KY_Shelby_Recorder_History (
    [Recording Date], [Document Number])
    values
    ('1993-01-08', '111-1'),
    ('1993-01-09', '111-1'),
    ('2023-12-29', '111-1'),
    ('2023-12-30', '111-1'),
    ('1993-01-06', '111111-1'),
    ('1993-01-07', '111111-1'),
    ('2023-12-30', '111111-1'),
    ('2023-12-31', '111111-1')

select
    min(a.[Recording Date]) as MinOfRecordingDate,
    max(a.[Recording Date]) as MaxOfRecordingDate, 
    replace(a.[Document Number], '1', 'n') as [pattern]
from
    @KY_Shelby_Recorder_History a
group by
    replace(a.[Document Number], '1', 'n')

result is:

MinOfRecordingDate	MaxOfRecordingDate	Document Number
1993-01-08	2023-12-30	nnn-n
1993-01-06	2023-12-31	nnnnnn-n

This is very fundamental sql covered in any introduction to sql so probably you need to focus on learning sql basics on your own for a bit so you know the basics.