Solved

Help with Sql statement

Posted on 2014-04-07
11
348 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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 69

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

808 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