Fetch the min and max nd_adate from table NDE.

Calculate the number of months between the two dates.

Calculate the second of the month for the min nd_adate

Using this information, construct a lookup table of dates between the min and max nd_adate which contains only the second of each month.

Join your NDE table to this table.

This is how you create the lookup table. I've output extra columns so you can analyse how it works:

SELECT

FirstMonth,

MAXDate,

MonthCount,

n,

DateToMatch = DATEADD(MONTH,n,FirstMonth)

FROM (SELECT MINDate = MIN(nd_adate), MAXDate = MAX(nd_adate) FROM NDE) d

CROSS APPLY (

SELECT MonthCount = DATEDIFF(MONTH,MINDate, MAXDate), FirstMonth = DATEADD(MONTH,DATEDIFF(MONTH,0,MINDate),1)

) x

CROSS APPLY (

SELECT TOP (monthcount+1) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1

FROM

(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) tRows (n),

(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) hRows (n),

(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) kRows (n)

) y

You could run the results of this into a #temp table, use it as a CTE, or incorporate it into your query as a derived table. This is how to configure it as a CTE:

;WITH DateLookup AS (

SELECT

DateToMatch = DATEADD(MONTH,n,FirstMonth)

FROM (SELECT MINDate = MIN(nd_adate), MAXDate = MAX(nd_adate) FROM NDE) d

CROSS APPLY (

SELECT MonthCount = DATEDIFF(MONTH,MINDate, MAXDate), FirstMonth = DATEADD(MONTH,DATEDIFF(MONTH,0,MINDate),1)

) x

CROSS APPLY (

SELECT TOP (monthcount+1) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1

FROM

(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) tRows (n),

(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) hRows (n),

(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) kRows (n)

) y

)

SELECT MAX(SUBSTRING(NDE.nd_num, 12,3))

FROM NDE

INNER JOIN DateLookup r

ON r.DateToMatch = NDE.nd_adate

For optimum performance, you could try an index on the date column nd_adate and INCLUDE column nd_num.