Finding max(date) from the resultant query output

Question

I have below 2 tables:

table1

objName | rptName | srcTblName | srcFileName | srcDateColName
--------------------------------------------------------------
obj1    | rpt1    | srcTbl1    | srcFile1.csv| srcDate

table2

FileName     | FileSize
------------------------
srcFile1.csv | 2009

The below query gives me distinct Table and Date Column names.

SELECT DISTINCT a.srcTblName, a.SrcDateColName
FROM table1 a
LEFT JOIN table2 b
ON a.srcFileName LIKE b.FileName
WHERE a.srcTblName is NOT NULL
AND a.srcFileName is NOT NULL

Output

srcTblName               |  srcDateColName
---------------------------------------------
tableN                   |    EntryDate
tableO                   |    Modified_Date

The second column of the output is a COLUMN_NAME in SrcTblName, which is a date.

I want to find the max(srcDateColName) from the respective srcTblName in the same query. Can anyone help me modify the above query?

Answer 1

The answers given by Hadi and Sarath seem to be working, But the cursor seems to affect the performance. So I did this using TEMP Table as below:

DECLARE @OBJECT_NAME VARCHAR(50) = 'ObjName'      

BEGIN
DECLARE @Query NVARCHAR(1000),@COUNT INT, @MAX_Count INT, @rptName VARCHAR(255)
DECLARE @tblName varchar(100),@dateColName varchar(100), @max_Date Date,@fileName varchar(100),@refID INT

DECLARE @Temp_Table TABLE([RowNumber] INT,rptName VARCHAR(500),SrcTblName VARCHAR(500), DateColName VARCHAR(100), SrcFileName VARCHAR(600), RefID INT)
INSERT INTO @Temp_Table
SELECT
    ROW_NUMBER()OVER(ORDER BY rptName)
    ,rptName
    ,srcTblName
    ,srcDateColName
    ,srcFileName
    ,ID
FROM
    table1
WHERE
    objName  = @OBJECT_NAME

SELECT @MAX_Count = MAX(RowNumber) FROM @Temp_Table
SET @COUNT = 1
WHILE (@COUNT <= @MAX_Count)
BEGIN                   
    SELECT   
        @rptName = rptName,
        @tblName = SrcTblName,
        @fileName = SrcFileName,
        @dateColName = DateColName,
        @refID = RefID    
    FROM   
        @Temp_Table   
    WHERE   
        RowNumber = @COUNT 

    IF @tblName IS NOT NULL AND @fileName IS NOT NULL AND @dateColName IS NOT NULL
    BEGIN
        SET @Query = 'SELECT @max_Date = MAX(CONVERT(DATE, ' + @dateColName + ')) FROM ' + @tblName
            EXEC SP_EXECUTESQL @Query, N'@max_Date DATE OUTPUT', @max_DATE OUTPUT

    END
SET @COUNT = @COUNT + 1
END

END

I hope this will help someone who comes searching for the similar solution. Thanks for your help though :)