How to Get Max Date Value of Date Column in Multiple Tables

How to get Max Date Value of Date column in Multiple tables

SQL to BUILD SQL for you

DECLARE @SQL as nvarchar(max) ='';
select @SQL = @SQL + 'SELECT ' + Column_Name + ' adate, ''' + Column_Name + ''' colname, ''' + Table_name + ''' tabname  FROM ' +  Table_name + ' UNION ' FROM   INFORMATION_SCHEMA.COLUMNS where data_type like '%date%'
select @SQL = 'SELECT TOP 100 * FROM (' + LEFT(@SQL, LEN(@SQL) -6) + ') IQ  WHERE IQ.adate IS NOT null ORDER BY IQ.adate DESC';

 --cut n paste the sql below, see what it does for you
select @SQL

mark II - executes sql for you, and sorts out names with spaces in them

DECLARE @SQL as nvarchar(max) ='';
select @SQL = @SQL + 'SELECT [' + Column_Name + '] adate, ''' + Column_Name + ''' colname, ''' + Table_name + ''' tabname  FROM [' +  Table_name + '] UNION ' FROM   INFORMATION_SCHEMA.COLUMNS where data_type like '%date%'
select @SQL = 'SELECT TOP 100 * FROM (' + LEFT(@SQL, LEN(@SQL) -6) + ') IQ  WHERE IQ.adate IS NOT null ORDER BY IQ.adate DESC';

select @SQL;
EXEC sp_executesql @sql;

SQL: Calculating the Max date across multiple tables

First join you join to table 2 using table 1's ID and table 3's ID. I presume you mean table 1's ID and table 2's ID?

join table2 as t2 on t1.ID = t3.ID

Should be:

join table2 as t2 on t1.ID = t2.ID

Now that this has been changed;

I tried your query and it works fine, are you sure that your joins are bringing back any rows? To test it replace your SELECT statement with SELECT *, if you get no rows back then they are being filtered out somewhere in your joins / WHERE.

sql query to get max date from multiple columns of multiple table in db2

Assuming the id columns are keys of each table you can join them (using full outer join maybe) and then use GREATEST() to get the latest date.

For example:

create view v as 
select
  coalesce(a.id, b.id, c.id) as id,
  b.fiscal_year,
  b.discount_amt,
  a.date_created,
  greatest(a.date_modified, b.date_modified, c.date_modified) as date_modified
from table1 a
full join table2 b on b.id = a.id
full join table3 c on c.id = b.id or c.id = a.id

EDIT: If your version of DB2 doesn't implement GREATEST() you can replace it with the [rather long] CASE clause:

  case when a.date_modified > b.date_modified then
    case when a.date_modified > c.date_modified 
         then a.date_modified else c.date_modified end
  else
    case when b.date_modified > c.date_modified 
         then b.date_modified else c.date_modified end
  end

Alternatively, you can implement the function yourself. It's pretty straightforward. For example:

create function greatest(in a date, in b date) returns date
language sql
begin
  if a > b then return a; end if;
  return b;
end
//

See running example of this custom function at db<>fiddle 1.

EDIT #2: Dealing with nulls

Nulls are not values, and the behavior of the database is correct. In the presence of an unknown date the engine cannot determine which date is greater and returns UNKNOWN -- in the form of a null.

Now, if you want nulls to behave as in programming languages like C, Java, PHP, then you can either 1) Use a lot of CASE/COALESCE clauses or you can simply modify your custom function to wotk as you please. Below is an example of the modified custom function:

create function mygreatest(in a date, in b date) returns date
language sql
begin
  if a is null then return b; end if;
  if b is null then return a; end if;
  if a > b then return a; end if;
  return b;
end

Then, you can see it in action:

with data (x, y) as (
  select date '2021-07-01', date '2021-12-01' from sysibm.sysdummy1
  union all select date '2021-07-01', null from sysibm.sysdummy1
  union all select null, date '2021-12-01' from sysibm.sysdummy1
  union all select null, null from sysibm.sysdummy1
)
select mygreatest(x, y) as g from data;

Result:

G
----------
2021-12-01
2021-07-01
2021-12-01
null

See running example at db<>fiddle 2.

Get the max date time in a table with multiple entries for each date

use corelated subquery

select t1.* from table_name t1
where t1.date= ( select max(date) from table_name t2 where t1.location=te.location and t1.date=t2.date)

you can do it using row_number() if your dbms support

select * from (select *,row_number()over(partition by location,Date order by date desc) rn
from table_name
) a where a.rn=1

How to select max date from table for distinct values

You can do it by first selecting the max dates for each account and then forcing the match between accounts given the date constraints, like in the following query:

SELECT 
    *
FROM 
    (
    SELECT 
        MAX(date) AS date, 
        account
    FROM 
        tab 
    GROUP BY 
        account
    ) max_date_per_account
INNER JOIN 
    tab
ON 
    tab.date = max_date_per_account.date 
AND  
    tab.account = max_date_per_account.account

---

Related Topics