Solved

Help with Sql statement

Posted on 2014-04-07
11
356 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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 70

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

705 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