SQL: Return different SUM depending on CASE

I have two tables with a Header/Detail relationship and I am creating a view.
The calculation of one of the columns on the view (OrderQty) depends on the value of one of the columns on the heard table (Status).
So depending on the Status I want the OrderQty to be the sum of different field, this is what I have tried:

       (CASE
             WHEN so.Status = 10 THEN (SELECT SUM(sod.OrderQty - sod.BackOrderQty - sod.CancelQty) AS v
                                       FROM dbo.SalesOrderDetail sod
                                       WHERE sod.SalesOrderID = so.SalesOrderID
                                         AND sod.Status <> 99)
             WHEN so.Status = 20 THEN (SELECT SUM(sod.DespatchQty)
                                       FROM dbo.SalesOrderDetail sod
                                       WHERE sod.SalesOrderID = so.SalesOrderID
                                         AND sod.Status <> 99)
             WHEN so.Status = 30
               OR so.Status = 40 THEN (SELECT SUM(ISNULL(sod.PODQty, sod.DespatchQty))
                                       FROM dbo.SalesOrderDetail sod
                                       WHERE sod.SalesOrderID = so.SalesOrderID
                                         AND sod.Status <> 99)
             WHEN so.Status = 50
               OR so.Status = 60 THEN (SELECT SUM(sod.InvoiceQty)
                                       FROM dbo.SalesOrderDetail sod
                                       WHERE sod.SalesOrderID = so.SalesOrderID
                                         AND sod.Status <> 99)
        END) AS OrderQty;

The whole view is:

SELECT so.SalesOrderID,
       so.SalesOrderJobTypeID,
       so.CarrierCodeID,
       so.SalesOrderTransportStatusID,
       so.CollectionDateTime,
       so.CustomerReference,
       so.FileReference,
       so.RequiredDate,
       so.SpecialInstructions,
       so.DeliveryNotes,
       dbo.ufn_SO_DecodeSOHeaderStatus(so.Status) AS Status,
       so.TransportCost,
       so.TransportRevenue,
       so.CurrencyCode,
       customer.AccountCode,
       customer.CustomerName,
       customer.Address1,
       customer.Address2,
       customer.Address3,
       customer.Address4,
       customer.PostCode,
       (SELECT UserName
        FROM dbo.SecurityUser
        WHERE dbo.SecurityUser.UserID = so.CreatedByUserID) AS CreatedByUser,
       (SELECT WeekNumber
        FROM dbo.Calendars
        WHERE dbo.Calendars.Date = so.RequiredDate) AS WeekNumber,
       (MONTH(so.RequiredDate)) AS MonthNumber,
       (SELECT ShortCode
        FROM dbo.StoreCodes
        WHERE dbo.StoreCodes.StoreCodeID = so.StoreCodeID) AS StoreCode,
       ISNULL((SELECT PickedQty
               FROM dbo.SO_SOAllocatedContainersView
               WHERE dbo.SO_SOAllocatedContainersView.SalesOrderID = so.SalesOrderID),
              0) AS PickedQty,
       (CASE
             WHEN so.Status = 10 THEN (SELECT SUM(sod.OrderQty - sod.BackOrderQty - sod.CancelQty) AS v
                                       FROM dbo.SalesOrderDetail sod
                                       WHERE sod.SalesOrderID = so.SalesOrderID
                                         AND sod.Status <> 99)
             WHEN so.Status = 20 THEN (SELECT SUM(sod.DespatchQty)
                                       FROM dbo.SalesOrderDetail sod
                                       WHERE sod.SalesOrderID = so.SalesOrderID
                                         AND sod.Status <> 99)
             WHEN so.Status = 30
               OR so.Status = 40 THEN (SELECT SUM(ISNULL(sod.PODQty, sod.DespatchQty))
                                       FROM dbo.SalesOrderDetail sod
                                       WHERE sod.SalesOrderID = so.SalesOrderID
                                         AND sod.Status <> 99)
             WHEN so.Status = 50
               OR so.Status = 60 THEN (SELECT SUM(sod.InvoiceQty)
                                       FROM dbo.SalesOrderDetail sod
                                       WHERE sod.SalesOrderID = so.SalesOrderID
                                         AND sod.Status <> 99)
        END) AS OrderQty
FROM dbo.SalesOrderHeader so
     JOIN dbo.Customer customer ON so.CustomerID = customer.CustomerID;

This gives the error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Does anyone understand what I am trying to do and could provide a solution?

Solution 1:

Obviously this is untestable however you should be able to take the following approach:

Join your SalesOrderDetail table once using the common criteria and sum the required columns, then use the results in a simpler case expression.

Using appy()

...
from dbo.SalesOrderHeader so
join dbo.Customer customer on so.CustomerID = customer.CustomerID
outer apply (
    select Sum(OrderQty) OrderQty, Sum(BackOrderQty) BackOrderQty ... etc
    from dbo.SalesOrderDetail sod
    where sod.SalesOrderID = so.SalesOrderID
    and sod.Status <> 99
)sod;

Then your case becomes

case 
  when so.status = 10 then sod.OrderQty - sod.BackOrderQty - sod.CancelQty 
  when so.status = 20 then sod.DespatchQty
  ... etc
end