I have the following sql statement. The unit table on the left holds each unit, the leasemoveout table on the right holds all moveouts. I need the last moveout date. So my query list all the units for each moveout they have rather than a single record with the unit and the last move out date. Any help is appreciated.
select a.PropertyId,a.BldgId,a.UnitId,a.unitstatus,a.OccuStatus,a.ActMadeReadyDate,b.MoveOutDate,DATEDIFF(DAY,b.moveoutdate,a.actmadereadydate) as TurnDays from unit a
right outer join leasemoveout b on a.propertyid=b.propertyid and a.bldgid=b.bldgid and a.unitid=b.unitid
where ActMadeReadyDate is not null and a.PropertyId='nh' and OccuStatus ='oc' and unitstatus='n'
order by PropertyId,BldgId,UnitId,MoveOutDate desc