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