SELECT d.ID
        , ROUND(SUM(te.Price * te.Quantity), 2)

     FROM Item i

LEFT JOIN Department d ON d.ID = i.DepartmentID
LEFT JOIN TransactionEntry te ON te.ItemID = i.ID
LEFT JOIN [Transaction] t ON te.TransactionNumber = t.TransactionNumber
LEFT JOIN Batch b ON t.BatchNumber = b.BatchNumber

    WHERE b.ClosingTime >= '2006-07-17'
      AND b.ClosingTime < '2006-07-18'

    GROUP BY d.ID