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))
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