Find data from one table based on the calculation done on the columns of another table

Question

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

to find the max period of staying. But how to join the two tables so that i can get relevant data from both?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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

Find data from one table based on the calculation done on the columns of another table

Sign up