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
How to Pass Parameter to Mssql Query in Node Js
Removing Null Value in SQL Join and Union Operators in SQL
How to Get Calendar Quarter from a Date in Tsql
How to Get Only One Row Against Each Id in MySQL
Postgresql Convert Columns to Rows Transpose
Sqlstate[Hy000] [1045] Access Denied for User 'Root'@'Localhost' (Using Password: No)
Jsonb Array Contains Like or and and Operators
Sql Select Radius Search Based on Latitude Longitude
Oracle SQL Get the First and Last Records from an Ordered Dataset
How to Select Only 1 Row from Oracle SQL
Insert If Not Exists Else Update
Adding $ Dollar Sign on My Total Cost in SQL Server
Sql to Generate a List of Numbers from 1 to 100
Sql - Return a Default Value When My Search Returns No Results Along With Search Criteria
Sql Query to Return Only First Occurance of One Column Value
Display Each Department's Number and Name and the Number of Employees Employed in Each Department
How to Find Specific Values in a Table in Oracle
Fetch the Rows Which Have the Max Value for a Column for Each Distinct Value of Another Column