 alter VIEW [dbo].[qrySOTotals]
AS
SELECT     tblSalesOrder.SOID, tblSalesOrder.SOTranTypeID,
SUM(tblSalesOrderItem.SOIPrice * tblSalesOrderItemLoc.SOILQuantity) AS Subtotal,
(SUM(tblSalesOrderItem.SOIPrice * tblSalesOrderItemLoc.SOILQuantity)-tblSalesOrder.SODiscountAmount) AS SubtotalLessDiscount,
SUM(tblSalesOrderItem.SOIStdCost * tblSalesOrderItemLoc.SOILQuantity) AS StandardCostTotal,
SUM(tblSalesOrderItem.SOIAvgCost * tblSalesOrderItemLoc.SOILQuantity) AS AverageCostTotal,
ROUND((SUM(tblSalesOrderItem.SOIPrice * tblSalesOrderItemLoc.SOILQuantity)-tblSalesOrder.SODiscountAmount)-SUM(tblSalesOrderItem.SOIStdCost * tblSalesOrderItemLoc.SOILQuantity),2) as MarkupMarginAmountStandardCost,
ROUND((SUM(tblSalesOrderItem.SOIPrice * tblSalesOrderItemLoc.SOILQuantity)-tblSalesOrder.SODiscountAmount)-SUM(tblSalesOrderItem.SOIAvgCost * tblSalesOrderItemLoc.SOILQuantity),2) as MarkupMarginAmountAvgCost,


ROUND((case when isnull(SUM(tblSalesOrderItem.SOIStdCost * tblSalesOrderItemLoc.SOILQuantity),0)=0 then 0 else ((((SUM(tblSalesOrderItem.SOIPrice * tblSalesOrderItemLoc.SOILQuantity)-tblSalesOrder.SODiscountAmount)-SUM(tblSalesOrderItem.SOIStdCost * tblSalesOrderItemLoc.SOILQuantity))/SUM(tblSalesOrderItem.SOIStdCost * tblSalesOrderItemLoc.SOILQuantity)) * 100) end),2) as MarkupPercentStandardCost,
ROUND((case when isnull(SUM(tblSalesOrderItem.SOIAvgCost * tblSalesOrderItemLoc.SOILQuantity),0)=0 then 0 else ((((SUM(tblSalesOrderItem.SOIPrice * tblSalesOrderItemLoc.SOILQuantity)-tblSalesOrder.SODiscountAmount)-SUM(tblSalesOrderItem.SOIAvgCost * tblSalesOrderItemLoc.SOILQuantity))/SUM(tblSalesOrderItem.SOIAvgCost * tblSalesOrderItemLoc.SOILQuantity)) * 100) end),2)  as MarkupPercentAvgCost,

ROUND((case when isnull((SUM(tblSalesOrderItem.SOIPrice * tblSalesOrderItemLoc.SOILQuantity)-tblSalesOrder.SODiscountAmount),0)=0 then 0 else ((((SUM(tblSalesOrderItem.SOIPrice * tblSalesOrderItemLoc.SOILQuantity)-tblSalesOrder.SODiscountAmount)-SUM(tblSalesOrderItem.SOIStdCost * tblSalesOrderItemLoc.SOILQuantity))/(SUM(tblSalesOrderItem.SOIPrice * tblSalesOrderItemLoc.SOILQuantity)-tblSalesOrder.SODiscountAmount)) * 100) end),2) as MarginPercentStandardCost,
ROUND((case when isnull((SUM(tblSalesOrderItem.SOIPrice * tblSalesOrderItemLoc.SOILQuantity)-tblSalesOrder.SODiscountAmount),0)=0 then 0 else ((((SUM(tblSalesOrderItem.SOIPrice * tblSalesOrderItemLoc.SOILQuantity)-tblSalesOrder.SODiscountAmount)-SUM(tblSalesOrderItem.SOIAvgCost * tblSalesOrderItemLoc.SOILQuantity))/(SUM(tblSalesOrderItem.SOIPrice * tblSalesOrderItemLoc.SOILQuantity)-tblSalesOrder.SODiscountAmount)) * 100) end),2)  as MarginPercentAvgCost,

ROUND((SUM(tblSalesOrderItem.SOIPrice * (case when tblSalesOrderItem.SOIISCleared=1 then 0 else (tblSalesOrderItemLoc.SOILQuantity-tblSalesOrderItemLoc.SOILShipped) end))-tblSalesOrder.SODiscountAmount)-SUM(tblSalesOrderItem.SOIStdCost * (tblSalesOrderItemLoc.SOILQuantity-tblSalesOrderItemLoc.SOILShipped)),2) as OpenLinesMarkupMarginAmountStandardCost,
ROUND((SUM(tblSalesOrderItem.SOIPrice * (case when tblSalesOrderItem.SOIISCleared=1 then 0 else (tblSalesOrderItemLoc.SOILQuantity-tblSalesOrderItemLoc.SOILShipped) end))-tblSalesOrder.SODiscountAmount)-SUM(tblSalesOrderItem.SOIAvgCost * (tblSalesOrderItemLoc.SOILQuantity-tblSalesOrderItemLoc.SOILShipped)),2) as OpenLinesMarkupMarginAmountAvgCost,
case when SUM(ABS(isnull(tblSalesOrderItemLoc.SOILShipped,0)))=0 or SUM(tblSalesOrderItemLoc.SOILQuantity)=0 then 0 else ROUND((SUM(ABS(isnull(tblSalesOrderItemLoc.SOILShipped,0)))/SUM(tblSalesOrderItemLoc.SOILQuantity))*100,2) end  as ShippedPercentage, 
SUM(tblSalesOrderItemLoc.SOILQuantity) as QuantityOrdered,
SUM(tblSalesOrderItemLoc.SOILShipped) as QuantityShipped,
SUM(case when tblSalesOrderItem.SOIIsCleared =0 then tblSalesOrderItemLoc.SOILQuantity else 0 end)-SUM(case when tblSalesOrderItem.SOIIsCleared=0 then tblSalesOrderItemLoc.SOILShipped else 0 end) as QuantityOpen


FROM         tblSalesOrderItemLoc INNER JOIN
                      tblSalesOrderShipDate ON tblSalesOrderItemLoc.SOILSOID = tblSalesOrderShipDate.SOIDSOID AND 
                      tblSalesOrderItemLoc.SOILSOTranTypeID = tblSalesOrderShipDate.SOIDSOTranTypeID AND 
                      tblSalesOrderItemLoc.SOILSOIItemID = tblSalesOrderShipDate.SOIDSOIItemID AND 
                      tblSalesOrderItemLoc.SOILSOIDisplayOrder = tblSalesOrderShipDate.SOIDSOIDisplayOrder AND 
                      tblSalesOrderItemLoc.SOILSOIDShipDate = tblSalesOrderShipDate.SOIDShipDate INNER JOIN
                      tblSalesOrderItem ON tblSalesOrderShipDate.SOIDSOID = tblSalesOrderItem.SOISOID AND 
                      tblSalesOrderShipDate.SOIDSOTranTypeID = tblSalesOrderItem.SOISOTranTypeID AND 
                      tblSalesOrderShipDate.SOIDSOIItemID = tblSalesOrderItem.SOIItemID AND 
                      tblSalesOrderShipDate.SOIDSOIDisplayOrder = tblSalesOrderItem.SOIDisplayOrder
                      inner join tblSalesOrder on tblSalesOrderItem.SOISOID=tblSalesOrder.SOID and tblSalesOrderItem.SOISOTranTypeID=tblSalesOrder.SOTranTypeID
GROUP BY tblSalesOrder.SOID, tblSalesOrder.SOTranTypeID,tblSalesOrder.SODiscountAmount