[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Help with Sql statement

Posted on 2014-04-07
11
Medium Priority
?
371 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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…
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

650 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