?
Solved

Help with Sql statement

Posted on 2014-04-07
11
Medium Priority
?
375 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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 71

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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
Suggested Courses

862 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