Select *
FROM
(
Select isnull(lmh.LastReadingDate,0),
isnull(lmh.LastReading,0),
l.*,
ROW_NUMBER() OVER(PARTITION BY l.ID ORDER BY lmh.LastReadingDate DESC) AS Seq
From Location l
LEFT JOIN LocationMeterHistory lmh
ON l.ID = lmn.LocationID
)
WHERE Seq=1

quote:

---

Originally posted by visakh16

Did you try solution i posted?

---

quote:

---

Originally posted by JAdauto

Oh wait..I just looked again and I had been putting the RowNumber() in my select list.. not my From... let me see if i can figure out how to get all of that in my query. This is what is really looks like (I was just trying to trim it down to get only the parts I needed help with). I am getting incorrect syntax near INNER.

SELECT isNull(mlmh.ReadDate,0) as LastReadDate, 
ce.Description as SlipStatus, 
mlevd.AssignedDate, 
mlevd.ReleaseDate , 
e.ID as EntityID, 
(IsNull(E.LastName,'') + IsNull(', ' + E.FirstName,'')) as LastNameFirstName, 
(e.Number) + '-' + (e.NumberSuffix) as NumberSuffix, 
(e.Number) as Number, ml.MeterNumber, 
Isnull(ml.MeterRate,0) as MeterRate, 
ml.Description as SlipNumber, 
Isnull(mlmh.ReadValue,0) as LastReadValue, 
'0' as ChargeAmount, 
ml.ID as MarinaLocationID
FROM 

(Select isnull(submlmh.ReadDate,0),
isnull(submlmh.LastReading,0),
subml.*,
ROW_NUMBER() OVER(PARTITION BY subml.ID ORDER BY submlmh.ReadDate DESC) AS Seq
From MarinaLocation subml
LEFT JOIN MarinaLocationMeterHistory submlmh
ON subml.ID = submlmh.LocationID)tbl


INNER JOIN MarinaLocEntityVehicleDetail mlevd 
ON mlevd.MarinaLocationID = tbl.ID
INNER JOIN MarinaLocation ml ON ml.ID = mlevd.MarinaLocationID 
INNER JOIN EntityVehicle ev ON ev.ID = mlevd.EntityVehicleID 
INNER JOIN Entity e ON ev.EntityID = e.ID 
LEFT JOIN MarinaLocationMeterHistory mlmh 
ON mlmh.MarinaLocationID = ml.ID 
LEFT JOIN CategoryElement as ce 
ON mlevd.MarinaStatusCID = ce.ID

WHERE 
(mlevd.AssignedDate <= 'October 15 2008 12:00:00 AM' or mlevd.AssignedDate is Null)
AND (mlevd.ReleaseDate >= 'October 15 2008 12:00:00 AM' OR mlevd.ReleaseDate Is Null) 
AND Seq = 1
ORDER BY mlmh.ReadDate DESC, ml.MeterNumber 

---

quote:

---

Originally posted by JAdauto

so close.. I got it to work, but I am still getting the records that have already been billed instead of only just the most recent.

---

SELECT  *
FROM
(
Select isnull(submlmh.ReadDate,0) AS LastReadDate,
isnull(submlmh.ReadValue,0) AS LastReadValue,
ce.Description as SlipStatus, 
mlevd.AssignedDate, 
mlevd.ReleaseDate , 
e.ID as EntityID, 
(IsNull(E.LastName,'') + IsNull(', ' + E.FirstName,'')) as LastNameFirstName, 
(e.Number) + '-' + (e.NumberSuffix) as NumberSuffix,
(e.Number) as Number, 
subml.MeterNumber, 
Isnull(subml.MeterRate,0) as MeterRate, 
subml.Description as SlipNumber,  
'0' as ChargeAmount, 
subml.ID as MarinaLocationID ,
ROW_NUMBER() OVER(PARTITION BY subml.ID ORDER BY submlmh.ReadDate DESC) AS Seq
From MarinaLocation subml
INNER JOIN MarinaLocEntityVehicleDetail mlevd 
ON mlevd.MarinaLocationID = subml.ID
INNER JOIN EntityVehicle ev ON ev.ID = mlevd.EntityVehicleID 
INNER JOIN Entity e ON ev.EntityID = e.ID 
LEFT JOIN MarinaLocationMeterHistory submlmh
ON subml.ID = submlmh.LocationID
LEFT JOIN CategoryElement as ce 
ON mlevd.MarinaStatusCID = ce.ID
WHERE 
(mlevd.AssignedDate <= 'October 15 2008 12:00:00 AM' or mlevd.AssignedDate is Null)
AND (mlevd.ReleaseDate >= 'October 15 2008 12:00:00 AM' OR mlevd.ReleaseDate Is Null) 
)t
WHERE Seq = 1
ORDER BY LastReadDate DESC, MeterNumber

quote:

---

Originally posted by JAdauto

Question... is RowNumber only a SQL20005 function? While i use SQL 2005, some of our clients still only have 2000.

---

Select isnull(tmp.ReadDate,0) AS LastReadDate,
isnull(tmp.ReadValue,0) AS LastReadValue,
ce.Description as SlipStatus, 
mlevd.AssignedDate, 
mlevd.ReleaseDate , 
e.ID as EntityID, 
(IsNull(E.LastName,'') + IsNull(', ' + E.FirstName,'')) as LastNameFirstName, 
(e.Number) + '-' + (e.NumberSuffix) as NumberSuffix,
(e.Number) as Number, 
subml.MeterNumber, 
Isnull(subml.MeterRate,0) as MeterRate, 
subml.Description as SlipNumber,  
'0' as ChargeAmount, 
subml.ID as MarinaLocationID
From MarinaLocation subml
INNER JOIN MarinaLocEntityVehicleDetail mlevd 
ON mlevd.MarinaLocationID = subml.ID
INNER JOIN EntityVehicle ev ON ev.ID = mlevd.EntityVehicleID 
INNER JOIN Entity e ON ev.EntityID = e.ID 
LEFT JOIN 
(SELECT submlmh.* FROM MarinaLocationMeterHistory submlmh
INNER JOIN (SELECT LocationID,MAX(ReadDate) AS latest
            FROM MarinaLocationMeterHistory 
            GROUP BY LocationID) submlmh1
ON submlmh1.LocationID=submlmh.LocationID
AND submlmh1.latest=submlmh.ReadDate)tmp
ON subml.ID = tmp.LocationID
LEFT JOIN CategoryElement as ce 
ON mlevd.MarinaStatusCID = ce.ID
WHERE 
(mlevd.AssignedDate <= 'October 15 2008 12:00:00 AM' or mlevd.AssignedDate is Null)
AND (mlevd.ReleaseDate >= 'October 15 2008 12:00:00 AM' OR mlevd.ReleaseDate Is Null) 
ORDER BY tmp.ReadDate DESC, subml.MeterNumber