leonie6214 (4/16/2013)
See query with groupings below. I have also attached an excel spreadsheet with the sample output.SELECT
Name as StreetName,
MeterId,
CollectionDate,
CollectionAmount,
SpaceUniqueID as MeterNumber,
Blockface
FROM dbo.CollectionSummaries C WITH(NOLOCK)
JOIN Meters M WITH(NOLOCK) ON M.MeterId = C.MeterId
LEFT JOIN RouteAssignments RA WITH(NOLOCK) ON RA.MeterId = M.MeterId
LEFT JOIN [Routes] R WITH(NOLOCK) ON R.RouteId = RA.RouteId
JOIN RouteTypes RT WITH(NOLOCK) ON RT.DisplayName = 'Collection'
JOIN dbo.Blockfaces B WITH(NOLOCK) ON B.BlockFaceId = M.BlockFaceId
JOIN dbo.Streets S WITH(NOLOCK) ON S.StreetId = B.StreetId
WHERE
AND R.RouteTypeId = 1
Group by
Name,
MeterId,
CollectionDate,
SpaceUniqueID ,
Blockface
Order by CollectionDate DESC
Heh... you do make it hard to help you. You keep posting a query that we have no data for, your previous "readily consumable data" code doesn't actually work because you didn't test it before you posted it, you've dramatically changed the requirements of the problem, and you've not explained how you want to list the output if there are more or less than 3 rows per MeterID.
Slow down and line up your ducks. We can't hit the hole of a rolling donut that we can't see. Post back when you're ready. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.