SELECT o.Name AS [Item Library], ISNULL(c.Name,'') AS [Collection], ISNULL(sl.Description,'') AS [Shelf Location], ISNULL(mt.Description,'') AS [Material Type], ISNULL(sc.Description,'') AS [Stat Code], COUNT(*) AS [Items Added]
FROM Polaris.Polaris.CircItemRecords AS [cir] WITH (NOLOCK)
INNER JOIN Polaris.Polaris.Organizations AS [o] WITH (NOLOCK)
ON cir.AssignedBranchID = o.OrganizationID
INNER JOIN Polaris.Polaris.MaterialTypes AS [mt] WITH (NOLOCK)
ON cir.MaterialTypeID = mt.MaterialTypeID
LEFT OUTER JOIN Polaris.Polaris.Collections AS [c] WITH (NOLOCK)
ON cir.AssignedCollectionID = c.CollectionID
LEFT OUTER JOIN Polaris.Polaris.ShelfLocations AS [sl] WITH (NOLOCK)
ON cir.ShelfLocationID = sl.ShelfLocationID AND cir.AssignedBranchID = sl.OrganizationID
LEFT OUTER JOIN Polaris.Polaris.StatisticalCodes AS [sc] WITH (NOLOCK)
ON cir.StatisticalCodeID = sc.StatisticalCodeID AND cir.AssignedBranchID = sc.OrganizationID
WHERE cir.FirstAvailableDate BETWEEN @StartDate AND DATEADD(day,1,@EndDate)
AND cir.AssignedBranchID IN (@OrgID)
GROUP BY o.Name, c.Name, sl.Description, mt.Description, sc.Description
ORDER BY o.Name, c.Name, sl.Description, mt.Description, sc.Description