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

Since you have to execute tablename and datecolumn from a table, you have to use dynamic sql

SELECT * INTO tableN FROM
(
SELECT '01/JAN/2014' EntryDate
UNION ALL
SELECT '24/JAN/2014'
UNION ALL
SELECT '13/MAR/2014'
)TAB


SELECT * INTO tableO FROM
(
SELECT '11/APR/2014' Modified_Date
UNION ALL
SELECT '18/MAY/2014'
UNION ALL
SELECT '22/JUN/2014'
)TAB

SELECT * INTO NEWTBL FROM
(
  SELECT 'tableN' srcTblName,'EntryDate' srcDateColName
  UNION ALL
  SELECT 'tableO' ,'Modified_Date' 
)TAB

Create a temporary table to get your result

CREATE TABLE #TEMP(srcTblName VARCHAR(100),srcDateColName VARCHAR(100),NEWDATE DATE)

Now use a cursor and execute it dynamically

DECLARE @TABLENAME VARCHAR(100)
DECLARE @COLUMNNAME VARCHAR(100)

-- Here you declare which all columns you need to loop in Cursor
DECLARE CUR CURSOR FOR 

-- Use your SELECT here instead of SELECT srcTblName , srcDateColName FROM NEWTBL

--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

SELECT srcTblName , srcDateColName
FROM NEWTBL


OPEN CUR

-- Select each tablename and datecolumn and execute dynamically 
FETCH NEXT FROM CUR 
INTO @TABLENAME,@COLUMNNAME

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @QRY NVARCHAR(MAX) 

    SET @QRY = '
            INSERT INTO #TEMP
            SELECT '''+@TABLENAME+''' , '''+@COLUMNNAME+''',
            (SELECT MAX(CAST('+@COLUMNNAME+' AS DATE))ENDT FROM '+@TABLENAME+')AA                              
            '

    EXEC SP_EXECUTESQL @QRY

    -- Fetches next record and increments the loop
    FETCH NEXT FROM CUR 
    INTO @TABLENAME,@COLUMNNAME
END 

CLOSE CUR;
DEALLOCATE CUR;

• SQL FIDDLE

Answer 2

Use following query, Hope it will help you.

SELECT  a.srcTblName, max(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
group by a.srcTblName

Answer 3

here what you can do

declare @name varchar(50),
        @column varchar(50),
        @query varchar(max)

declare @table table(TableName varchar(50),
                     MaxDate datetime)

declare c cursor for
-- your query here
SELECT DISTINCT a.srcTblName, a.SrcDateColName
FROM t1 a
LEFT JOIN t2 b
ON a.srcFileName LIKE b.FileName
WHERE a.srcTblName is NOT NULL
AND a.srcFileName is NOT NULL

open c
fetch next from c into @name,@column
while @@fetch_status=0
begin
  -- executing the code for each table mentioned in your srcTblName
  set @query='select ''' + @name + ''' as TableName,max(' + @column + ') as Date from ' + @name
  --print @query
  insert into @table(TableName,MaxDate)
  exec(@query)
  fetch next from c into @name,@column
end
close c
deallocate c

select * from @table

here a working DEMO

hope it will help you

Answer 4

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 :)