Solved

Help with Sql statement

Posted on 2014-04-07
11
343 Views
Last Modified: 2014-04-07
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

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

Open in new window

0
Comment
Question by:mgmhicks
  • 5
  • 5
11 Comments
 
LVL 6

Expert Comment

by:Dulton
ID: 39983844
select  a.PropertyId
,a.BldgId
,a.UnitId
,a.unitstatus
,a.OccuStatus
,a.ActMadeReadyDate
,MAX(b.MoveOutDate) AS [LastMoveOutDate]
,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'
group by a.PropertyId
,a.BldgId
,a.UnitId
,a.unitstatus
,a.OccuStatus
,a.ActMadeReadyDate
,DATEDIFF(DAY,b.moveoutdate,a.actmadereadydate)
order by PropertyId,BldgId,UnitId,LastMoveOutDate desc 

Open in new window

0
 

Author Comment

by:mgmhicks
ID: 39983851
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
0
 
LVL 6

Expert Comment

by:Dulton
ID: 39983877
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 

Open in new window

0
 

Author Comment

by:mgmhicks
ID: 39983887
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'.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39983907
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
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

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 6

Expert Comment

by:Dulton
ID: 39983898
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.


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  

Open in new window

0
 

Author Comment

by:mgmhicks
ID: 39983904
here is the code that ran  
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 

Open in new window


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
0
 
LVL 6

Expert Comment

by:Dulton
ID: 39983913
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   

Open in new window

0
 

Author Comment

by:mgmhicks
ID: 39983919
now only a single record is returned.  Should I use a cursor?
0
 
LVL 6

Accepted Solution

by:
Dulton earned 500 total points
ID: 39983945
no, I misunderstood after my first post. I then thought you did not want to split by unit. I see you do. This should be it.

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, a.UnitId 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    

Open in new window

0
 

Author Closing Comment

by:mgmhicks
ID: 39983962
thank you worked great!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now