mgmhicks
asked on
Help with Sql statement
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.
thank you
thank you
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
ASKER
still showing units multiple times
PropertyId BldgId UnitId unitstatus OccuStatus ActMadeReadyDate LastMoveOutDate TurnDays
NH NH 100 N OC 2012-01-27 00:00:00.000 2012-01-16 00:00:00.000 11
NH NH 100 N OC 2012-01-27 00:00:00.000 2011-05-31 00:00:00.000 241
NH NH 100 N OC 2012-01-27 00:00:00.000 2010-02-28 00:00:00.000 698
NH NH 100 N OC 2012-01-27 00:00:00.000 NULL NULL
NH NH 104 N OC 2013-10-24 00:00:00.000 2013-09-30 00:00:00.000 24
NH NH 104 N OC 2013-10-24 00:00:00.000 2003-09-30 00:00:00.000 3677
NH NH 104 N OC 2013-10-24 00:00:00.000 2003-04-30 00:00:00.000 3830
NH NH 105 N OC 2010-06-16 00:00:00.000 2010-04-30 00:00:00.000 47
NH NH 105 N OC 2010-06-16 00:00:00.000 2004-02-06 00:00:00.000 2322
NH NH 105 N OC 2010-06-16 00:00:00.000 2003-11-30 00:00:00.000 2390
NH NH 105 N OC 2010-06-16 00:00:00.000 2000-04-15 00:00:00.000 3714
NH NH 105 N OC 2010-06-16 00:00:00.000 NULL NULL
PropertyId BldgId UnitId unitstatus OccuStatus ActMadeReadyDate LastMoveOutDate TurnDays
NH NH 100 N OC 2012-01-27 00:00:00.000 2012-01-16 00:00:00.000 11
NH NH 100 N OC 2012-01-27 00:00:00.000 2011-05-31 00:00:00.000 241
NH NH 100 N OC 2012-01-27 00:00:00.000 2010-02-28 00:00:00.000 698
NH NH 100 N OC 2012-01-27 00:00:00.000 NULL NULL
NH NH 104 N OC 2013-10-24 00:00:00.000 2013-09-30 00:00:00.000 24
NH NH 104 N OC 2013-10-24 00:00:00.000 2003-09-30 00:00:00.000 3677
NH NH 104 N OC 2013-10-24 00:00:00.000 2003-04-30 00:00:00.000 3830
NH NH 105 N OC 2010-06-16 00:00:00.000 2010-04-30 00:00:00.000 47
NH NH 105 N OC 2010-06-16 00:00:00.000 2004-02-06 00:00:00.000 2322
NH NH 105 N OC 2010-06-16 00:00:00.000 2003-11-30 00:00:00.000 2390
NH NH 105 N OC 2010-06-16 00:00:00.000 2000-04-15 00:00:00.000 3714
NH NH 105 N OC 2010-06-16 00:00:00.000 NULL NULL
ok, let's try this a different way... this is going to give you the most recent moveout information/row for each property/building.
select PropertyId
,BldgId
,UnitId
,unitstatus
,OccuStatus
,ActMadeReadyDate
,TurnDays
FROM
(
select a.PropertyId
,a.BldgId
,a.UnitId
,a.unitstatus
,a.OccuStatus
,a.ActMadeReadyDate
,b.MoveOutDate
,DATEDIFF(DAY,b.moveoutdate,a.actmadereadydate) as TurnDays
,ROW_NUMBER() OVER(PARTITION BY PropertyId, BldgId ORDER BY MoveOutDate DESC) AS [MoveOutOrder]
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'
group by a.PropertyId
,a.BldgId
,a.UnitId
,a.unitstatus
,a.OccuStatus
,a.ActMadeReadyDate
,DATEDIFF(DAY,b.moveoutdate,a.actmadereadydate)
) AS t1
WHERE MoveOutOrder = 1
order by PropertyId,BldgId,UnitId,MoveOutDate desc
ASKER
ok, this is error message tried change a couple of things still get it.
Msg 8120, Level 16, State 1, Line 22
Column 'leasemoveout.MoveOutDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 209, Level 16, State 1, Line 24
Ambiguous column name 'PropertyId'.
Msg 207, Level 16, State 1, Line 24
Invalid column name 'BldId'.
Msg 8120, Level 16, State 1, Line 22
Column 'leasemoveout.MoveOutDate'
Msg 209, Level 16, State 1, Line 24
Ambiguous column name 'PropertyId'.
Msg 207, Level 16, State 1, Line 24
Invalid column name 'BldId'.
I'm not convinced you should use right join here, it sounds more like a left join (showing all units, and optionally the last moveout date).
One way to get this is
One way to get this is
select
a.PropertyId, a.BldgId, a.UnitId, a.unitstatus, a.OccuStatus, a.ActMadeReadyDate,
b.MaxMoveOutDate,DATEDIFF(DAY,b.MaxMoveOutDate,a.actmadereadydate) as TurnDays
from unit a
left join (select bldgid, unitid, max(MoveOutDate) as MaxMoveOutDate
from leasemoveout
group by bldgid, unitid) 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
Ok, cleaned it up. I needed to remove the groupby stuff I did in the first post.
also, in the order by, you may get some performance gain by removing the MoveoutDate... since we're only listing 1 per Property/building, it's kind of redundant.
also, in the order by, you may get some performance gain by removing the MoveoutDate... since we're only listing 1 per Property/building, it's kind of redundant.
select PropertyId
,BldgId
,UnitId
,unitstatus
,OccuStatus
,ActMadeReadyDate
,TurnDays
FROM
(
select a.PropertyId
,a.BldgId
,a.UnitId
,a.unitstatus
,a.OccuStatus
,a.ActMadeReadyDate
,b.MoveOutDate
,DATEDIFF(DAY,b.moveoutdate,a.actmadereadydate) as TurnDays
,ROW_NUMBER() OVER(PARTITION BY PropertyId, BldgId ORDER BY MoveOutDate DESC) AS [MoveOutOrder]
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'
) AS t1
WHERE MoveOutOrder = 1
order by PropertyId,BldgId,UnitId,MoveOutDate desc
ASKER
here is the code that ran
here are the results
PropertyId BldgId UnitId unitstatus OccuStatus ActMadeReadyDate TurnDays
NH NH 208 N OC 2014-03-05 00:00:00.000 33
thanks
select PropertyId
,BldgId
,UnitId
,unitstatus
,OccuStatus
,ActMadeReadyDate
,TurnDays
FROM
(
select a.PropertyId
,a.BldgId
,a.UnitId
,a.unitstatus
,a.OccuStatus
,a.ActMadeReadyDate
,b.MoveOutDate
,DATEDIFF(DAY,b.moveoutdate,a.actmadereadydate) as TurnDays
,ROW_NUMBER() OVER(PARTITION BY a.PropertyId, a.BldgId ORDER BY MoveOutDate DESC) AS [MoveOutOrder]
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 a.OccuStatus ='oc'
and a.unitstatus='n'
group by a.PropertyId
,a.BldgId
,a.UnitId
,a.unitstatus
,a.OccuStatus
,a.ActMadeReadyDate
,b.MoveOutdate
,DATEDIFF(DAY,b.moveoutdate,a.actmadereadydate)
) AS t1
WHERE MoveOutOrder = 1
order by PropertyId,BldgId,UnitId,MoveOutDate desc
here are the results
PropertyId BldgId UnitId unitstatus OccuStatus ActMadeReadyDate TurnDays
NH NH 208 N OC 2014-03-05 00:00:00.000 33
thanks
select PropertyId
,BldgId
,UnitId
,unitstatus
,OccuStatus
,ActMadeReadyDate
,TurnDays
FROM
(
select a.PropertyId
,a.BldgId
,a.UnitId
,a.unitstatus
,a.OccuStatus
,a.ActMadeReadyDate
,b.MoveOutDate
,DATEDIFF(DAY,b.moveoutdate,a.actmadereadydate) as TurnDays
,ROW_NUMBER() OVER(PARTITION BY a.PropertyId, a.BldgId ORDER BY b.MoveOutDate DESC) AS [MoveOutOrder]
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'
) AS t1
WHERE MoveOutOrder = 1
order by PropertyId,BldgId,UnitId,MoveOutDate desc
ASKER
now only a single record is returned. Should I use a cursor?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you worked great!
Open in new window