Selecting Most Recent Date Between Two Columns

Selecting most recent date between two columns

CASE is IMHO your best option:

SELECT ID,
CASE WHEN Date1 > Date2 THEN Date1
ELSE Date2
END AS MostRecentDate
FROM Table

If one of the columns is nullable just need to enclose in COALESCE:

.. COALESCE(Date1, '1/1/1973') > COALESCE(Date2, '1/1/1973')

How can I select the most recent date from multiple columns in a pandas df?

Simply apply the max function column-wise over the subset of columns:

cols = [x for x in dates_df.columns if 'billing' in x.lower()]
dates_df['most_recent_date'] = dates_df[cols].max(axis=1)

SQL - Getting Most Recent Date From Multiple Columns

SELECT MAX(CASE WHEN (DateDeleted IS NULL OR DateModified > DateDeleted)
THEN DateModified ELSE DateDeleted END) AS MaxDate
FROM Table

Find most recent (non-future) date from a list of columns

You can filter before aggregation. Here is one way to do it:

select mt.id, max(va.ldate) as maxdate
from my_table as mt
cross apply ( values (mt.my_date_1), (mt.my_date_2), (mt.my_date_3) ) va(ldate)
where va.ldate > getdate()
group by mt.id

Get the last dates from multiple columns

Use a CASE expression:

SELECT
call_case,
CASE WHEN date1 > date2 AND date1 > date3
THEN date1
WHEN date2 > date3
THEN date2
ELSE date3 END AS [Latest Date]
FROM #indebtedness;

Demo

Note that some databases, such as MySQL, SQL Server, and SQLite, support a scalar greatest function. SQL Server does not, so we can use a CASE expression as a workaround.

Edit:

It appears that in your actual table, one or more of the three date columns could have NULL values. We can adapt the above query as follows:

SELECT
call_case,
CASE WHEN (date1 > date2 OR date2 IS NULL) AND (date1 > date3 OR date3 IS NULL)
THEN date1
WHEN date2 > date3 OR date3 IS NULL
THEN date2
ELSE date3 END AS [Latest Date]
FROM #indebtedness;

Demo

Getting most recent date from multiple SQL columns

;WITH X AS 
(
SELECT Customer
,Value [Date]
,ColumnName [CommunicationType]
,ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Value DESC) rn
FROM (
SELECT comp_name AS Customer
, Comp_UpdatedDate AS Last_Change
, CmLi_UpdatedDate AS Last_Communication
, Case_UpdatedDate AS Last_Case
, AdLi_UpdatedDate AS Address_Change
FROM Company
LEFT JOIN Comm_Link on Comp_CompanyId = CmLi_Comm_CompanyId
LEFT JOIN Cases ON Comp_CompanyId = Case_PrimaryCompanyId
LEFT JOIN Address_Link on Comp_CompanyId = AdLi_CompanyID
) t
UNPIVOT (Value FOR ColumnName IN (Last_Change,Last_Communication,
Last_Case,Address_Change))up
)
SELECT Customer
,[Date]
,[CommunicationType]
FROM X
WHERE rn = 1

Getting most recent date values where other column values change

Depending on what version of SQL you use, you may have a function called ROW_NUMBER() OVER() that can help.

WITH x AS
(SELECT id, value, date
, ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS RowNum
FROM table
)
SELECT id, value, date
FROM x
WHERE RowNum = 1

The above would work in SQL Server and anything else that supports CTE and Windowing Functions. You could also write it as a derived table like this:

SELECT id, value, date
FROM
(SELECT id, value, date
, ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS RowNum
FROM table
) AS x
WHERE RowNum = 1

SQL Group two columns and select recent date/timestamp

Just to confirm

  • For each 'pair' of callers (one of who is "22")
  • You would like to get the latest text which went either way (either to or from 22)

The answer here uses the following approach

  • For each row, determine the other party (this is called second_character_id in the query)
  • For each other party, order the 'text' rows based on the date sent
  • Get the latest row for each party
WITH Call_list AS
(SELECT *,
@character_id AS `primary_character_id`,
CASE
WHEN `character_id` = @character_id THEN `target_character_id`
ELSE `character_id` END AS `second_character_id`
FROM
`phone_messages`
WHERE
(`character_id`= @character_id OR `target_character_id`= @character_id)
AND (`type`= @t )
),
Sorted_Call_List AS
(SELECT *,
ROW_NUMBER() OVER
(PARTITION BY `primary_character_id`, `second_character_id`
ORDER BY `Date` DESC
) AS char_rn
FROM Call_list
)
SELECT `character_id`, `target_character_id`, `message`, `type`, `date`
FROM Sorted_Call_List
WHERE char_rn = 1;

With this answer I also included the primary_character_id - which in this case is always 22 - in case you wanted to expand it to have multiple people.

Here is a db<>fiddle with the data setup and approach.

Note that there is an additional query at the bottom there - that was my previous attempt where I misunderstood the requirements. In that answer, it finds the latest 'incoming' and latest 'outgoing' text to/from 22, regardless of who they came from.

SQL select most recent date multiple columns

Try GREATEST

http://www.w3resource.com/mysql/comparision-functions-and-operators/greatest-function.php

ORDER BY GREATEST(created_at, update_at) DESC

EDIT

You should check how handle null. I think using 0 or maybe use a default date '1900-1-1' please test it.

ORDER BY GREATEST(
COALESCE(created_at, 0),
COALESCE(update_at, 0)
) DESC


Related Topics



Leave a reply



Submit