DATE DESC LAST RECORD - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DATE DESC LAST RECORD

Author

Topic

robson
Starting Member

22 Posts

Posted - 2010-02-08 : 08:16:48

Hello there,

I have the following sql statement, It does not do exactly what I want. I need to get the last record for each property ORDERED BY PropertyPeriods.PeriodStartDate DESC and each record has to be UNIQUE - i.e. one property. The date (PropertyPeriods.PeriodStartDate) has to be less than or equal to today. Please can you help?

SELECT Properties.ID, MIN(PropertyPrices.SalesPrice) AS FromPrice
FROM PropertySellRooms AS psr
LEFT JOIN PropertyPeriods ON PropertyPeriods.PropertyID = psr.PropertyID
LEFT JOIN PropertyPrices ON PropertyPrices.PropertySellRoomID = psr.ID
AND PropertyPrices.PropertyPeriodID = PropertyPeriods.ID
INNER JOIN Properties ON Properties.ID = PropertyPrices.PropertyID
WHERE PropertyPrices.SalesPrice <> '' AND (PropertyPeriods.PeriodStartDate <= GETDATE())
GROUP BY Properties.ID
ORDER BY MAX(PropertyPeriods.PeriodStartDate)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 08:27:53

may be this


SELECT Properties.ID, MIN(PropertyPrices.SalesPrice) AS FromPrice
FROM PropertySellRooms AS psr 
LEFT JOIN (SELECT pp.* FROM PropertyPeriods pp 
JOIN (SELECT PropertyID,MAX(PeriodStartDate) AS Recent FROM PropertyPeriods 
      GROUP BY PropertyID)pp1
ON pp1.PropertyID=pp.PropertyID
AND pp1.Recent=pp.PeriodStartDate)r
ON r.PropertyID = psr.PropertyID 
LEFT JOIN PropertyPrices ON PropertyPrices.PropertySellRoomID = psr.ID 
AND PropertyPrices.PropertyPeriodID = PropertyPeriods.ID 
INNER JOIN Properties ON Properties.ID = PropertyPrices.PropertyID 
WHERE PropertyPrices.SalesPrice <> '' AND (PropertyPeriods.PeriodStartDate <= GETDATE())
GROUP BY Properties.ID
ORDER BY MAX(PropertyPeriods.PeriodStartDate)

robson
Starting Member

22 Posts

Posted - 2010-02-08 : 09:22:14

I now get the following error:

The multi-part identifier "PropertyPeriods.ID" could not be bound.
The multi-part identifier "PropertyPeriods.PeriodStartDate" could not be bound.
he multi-part identifier "PropertyPeriods.PeriodStartDate" could not be bound

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 10:12:47

[code]SELECT Properties.ID, MIN(PropertyPrices.SalesPrice) AS FromPrice
FROM PropertySellRooms AS psr
LEFT JOIN (SELECT pp.* FROM PropertyPeriods pp
JOIN (SELECT PropertyID,MAX(PeriodStartDate) AS Recent FROM PropertyPeriods
GROUP BY PropertyID)pp1
ON pp1.PropertyID=pp.PropertyID
AND pp1.Recent=pp.PeriodStartDate)r
ON r.PropertyID = psr.PropertyID
LEFT JOIN PropertyPrices ON PropertyPrices.PropertySellRoomID = psr.ID
AND PropertyPrices.PropertyPeriodID = r.ID
INNER JOIN Properties ON Properties.ID = PropertyPrices.PropertyID
WHERE PropertyPrices.SalesPrice <> '' AND (r.PeriodStartDate <= GETDATE())
GROUP BY Properties.ID
ORDER BY MAX(r.PeriodStartDate)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources