I'll lead off with saying that I am relatively new to databases and SQL and I am trying to make an addition to a
SQL Server that a previous co-worker created.
I am trying to join the
Customer_Snacks table so that I can get the
Snack Quantity for each customer's order. The issue I'm having is when I do a
LEFT OUTER JOIN which, as far as I can tell, should work, some of the other columns data is drastically changed.
I managed to get it to work the way I would like it to by using a subquery but it is DREADFULLY slow. The query takes over a minute to run and in return, the application that is making the call for that view times out.
Here is what I currently have that works (I know, it's messy):
SELECT cl.CustomerID, cl.FirstName + ' ' + c.LastName as CustomerDisplay, cl.InvoiceID, cl.MealPlanID, mp.DeliveryDate1 as DeliveryDate, SUM(CASE WHEN cll.Name = 'Meal' THEN cll.Quantity ELSE 0 END) as RegularQuantity, SUM(CASE WHEN cll.Name = 'Meal' THEN cll.Quantity * Amount ELSE 0 END) as RegularTotal, SUM(CASE WHEN cll.Name = 'EXTRAPROTEINMEAL' THEN cll.Quantity ELSE 0 END) as ProteinQuantity, SUM(CASE WHEN cll.Name = 'EXTRAPROTEINMEAL' THEN cll.Quantity * Amount ELSE 0 END) as ProteinTotal, SUM(CASE WHEN cll.Name = 'TAX' THEN Amount ELSE 0 END) as TotalTax, SUM(CASE WHEN cll.Name = 'SNACKS' THEN Amount ELSE 0 END) AS SnackTotal, -- Here is my subquery that I would like to integrate into this view with a join so that it's much faster (SELECT SUM(cs.Quantity) FROM Fustomer_Snacks as cs WHERE cs.CustomerID = cl.CustomerID and cs.DeliveryDate = mp.DeliveryDate1) as SnackQuantity, ------------------------------------------------------------------ CAST(SUM((CASE WHEN cll.Name = 'home' or cll.Name = 'work' THEN 1 ELSE 0 END)) as bit) as IsDelivery, SUM(CASE WHEN (cll.Name = 'home' or cll.Name = 'work' or cll.Name = 'pickup') and cll.Quantity > 0 THEN cll.Quantity * Amount else 0 END) as DeliveryCharge, cl.Balance as PrePromoTotal, ISNULL(cl.Balance * (pc.Discount * .01), 0) * -1 as PromoDiscount, cl.FinalBalance - ISNULL((cl.FinalBalance * (pc.Discount * .01)),0) Total FROM Customer_Ledger as cl INNER JOIN Customer_Ledger_LineItems as cll on cl.ID = cll.LedgerID INNER JOIN MealPlan as mp on cl.MealPlanID = mp.ID INNER JOIN Customer as c on cl.CustomerId = c.Id LEFT OUTER JOIN PromoCode as pc on cl.BalancePromoId = pc.Id GROUP BY cl.CustomerID, c.FirstName, c.LastName, MealPlanID, InvoiceID, mp.DeliveryDate1, cl.Balance, cl.FinalBalance, pc.Discount
Like I said, the way I currently have it, it works but it's way too slow. As soon as I try a
LEFT OUTER JOIN it's a lot faster but other columns like
RegularQuantity become inflated with other numbers (and I'm not sure how that's even happening).
If anyone can guide me in the right direction, that would be great!