I have two tables 1. Tenancy_histories 2. Profiles. "Tenancy_histories" contains the move_in_date and move_out_date of the tenants. "Profiles" consists of fisrt_name, last_name. Need to find out the full name of the tenant who stayed for longest time in the past. Both the tables are joined by profile_id.
I tried something like this
SELECT MAX(ISNULL(DATEDIFF(dd, move_in_date, move_out_date),0))
FROM Tenancy_histories
You need to join profiles
table. Use Top 1
and Order by
to find the tenant stayed for longest time.
SELECT TOP 1 Full_Name = first_name+' '+last_name,
no_of_days_stayed = Datediff(dd, move_in_date, move_out_date)
FROM tenancy_histories t
INNER JOIN profiles p
ON t.profile_id = t.profile_id
ORDER BY no_of_days_stayed DESC