Join is causing other column data to change?
The odds are that your problem is that, when you add in the snacks, you have more than one table that returns more than one row for the columns in your GROUP BY statement - probably Customer_Snacks and Customer_Ledger_LineItems.

When the DB engine performs a join, it matches however many rows in the first table with value ABC with however many rows in the second table have the same value, ABC (outer joins working a little differently, but let's ignore that for the moment).

I'll guess that for a given Customer_Ledger row, you tie to one Customer row, one MealPlan row, one (or zero) PromoCode row, and multipleCustomer_Ledger_LineItems` rows.

I'm also guessing for one Customer_Ledger row and its associated MealPlan row, you can bring back multiple Customer_Snacks rows.

So, when we put all the rows together, let's say we have:


1 Customer row
1 Customer_Ledger row
1 MealPlan row
1 PromoCode row
3 Customer_Ledger_LineItems rows


Match all those together, you get 1 * 1 * 1 * 1 * 3 = 3 rows.

Now, add in 2 Customer_Snacks rows. those are matched against all the rows we already have, so we get 1 * 1 * 1 * 1 * 3 * 2 = 6 rows.

When we go to take our sums, we'll get double the amount for sums from Customer_Ledger_LineItems rows, because each row now shows up twice. And, we'll get triple the amount for the sum from Customer_Snacks, because those rows each now show up three times. Basically, you're getting a CROSS JOIN between the relevant Customer_Ledger_LineItems rows, and the Customer_Snacks rows.

There are two ways to avoid this. You already employing the first option - use a subquery to get the sums from one of the tables returning multiple rows to our grouped values.

The other option is very similar: build a temporary table (or a CTE) with all relevant values that you need, so that you'll just have one row with the sums you need when you go to join the data in. In your case, this would look like:

;WITH SnackSums AS
      (SELECT cs.CustomerID
             ,cs.DeliveryDate
             ,SUM(cs.Quantity) as SnackQuantity
         FROM Customer_Snacks cs
        GROUP BY cs.CustomerID, cs.DeliveryDate
      )


and would go right before the SELECT for your query. Then, you just put ss.SnackQuantity where the subquery was, and add:

LEFT OUTER JOIN
  SnackSums ss
    ON (cl.CustomerId = ss.CustomerId AND ss.DeliveryDate = mp.DeliveryDate1)


right before your GROUP BY. Finally, you'll also need to add ss.SnackQuantity to the GROUP BY, since in the context of your main query this is a stand-alone, non-aggregated value.

Now, I make no promises that this will be faster than the subquery. However, it should be accurate - and it doesn't matter is something is blazing fast, if it isn't accurate.