Working on a SQL Server Management Studio query. The goal is to return the Max(CreateDate) and Table Names from a query that returns a set of values from a field called WhenSQL. Each table has its own CreateDate field. The table names are returned within aset of values. Here are two examples of the sets of values returned from the main SQL query:

EXISTS (SELECT 1 FROM escan.dbo.hospInsuranceCodes hic WHERE hic.hospitalFK = source.hospitalFK AND (hic.code = source.Insurancecode1 OR hic.code=source.Insurancecode2 OR hic.code = source.Insurancecode3 ) AND codedesc LIKE '%auto%' and code <>'7240Q') OR EXISTS (SELECT 1 FROM escan.dbo.hospInsuranceCodes hic WHERE hic.hospitalFK = source.hospitalFK AND (hic.code=Source.InsuranceCode1 OR hic.code=source.InsuranceCode2 or hic.code=source.InsuranceCode3) and codedesc LIKE'%do%not%')

So the goal here is to return the Max(CreateDate) from the table esca.dbo.hospInsuranceCodes table, and any other tables that exist in the returned value set. This would require identifying all tables in the returned value set, which are all denoted by ‘%escan.dbo’ to start the table name.

Can this be done?

SELECT h.HCSystemFK
, sw.*
FROM cac.dbo.CACCodeSQLWhen sw
JOIN cac.dbo.CACCodeSQLWhenOrder swo ON sw.CACCodeSQLWhenPK = swo.CACCodeSQLWhenFK
JOIN cac.dbo.CACCodeSQLCase sc ON sc.CACCodeSQLCasePK = swo.CACCodeSQLCaseFK
JOIN cac.dbo.cacpackageversion pv ON pv.CACPackageVersionPK = sc.CACPackageVersionFK
JOIN cac.dbo.CACPackage cp ON cp.CACPackagePK = pv.CACPackageFK
JOIN cac.dbo.CACCodeValue cv ON cv.CACCodeValuePK=sw.CACCodeValueFK
JOIN escan.dbo.hospitals h ON h.hospitalpk = cp.ContextValueFK
--JOIN LatestCreateDates lcd ON sw.CACCodeSQLWhenPK = lcd.CACCodeSQLWhenPK
WHERE (sw.WhenSQL LIKE '%escan.dbo.%')
AND h.Active=1 AND h.EnableReporting=1

— I have the above query but need to fix it, or add on to it to return the Max(CreateDate) and Table Names for all tables that are returned in the result set. The tables names are not separated and need to be searched for within the result set, somehow.