Link to home
Start Free TrialLog in
Avatar of W.E.B
W.E.B

asked on

SQL 2008

Hello,
I have below stored procedure.

I need to add the sum of groups (References)
Example
Select Sum(BaseAmount) group by Reference.
Select Sum(SubTotalAmount) group by Reference.
Select Sum(TotalAmount) group by Reference.


DECLARE @WeightType char(3), @DistanceType char(3)
DECLARE @CompanyID int

      SELECT @CompanyID = CompanyID FROM Clients WHERE AccountNumber = 451
      SELECT @DistanceType = DistanceType, @WeightType = WeightType
      FROM SystemOptions      
      
      SELECT FinalizedOrders.OrderNo AS [Order Number],      ISNULL(FinalizedOrders.Department, '') AS [Department],  ISNULL(FinalizedOrders.Reference, '') AS [Reference],
       FinalizedOrders.BaseAmount AS [BaseAmt],
FinalizedOrders.SubTotalAmount AS [SubTotAmt],
      FinalizedOrders.TotalAmount AS [TotalAmt]
      FROM FinalizedOrders
      INNER JOIN Clients ON FinalizedOrders.AccountNumber = Clients.AccountNumber
      LEFT JOIN InvoiceGroupingTypes ON InvoiceGroupingTypes.InvoiceGroupID = Clients.InvoiceGrouping
      INNER JOIN Companies ON Clients.CompanyID = Companies.CompanyID
      LEFT JOIN Zones ON FinalizedOrders.PickupZoneID = Zones.ZoneID
      LEFT JOIN Zones Zones1 ON FinalizedOrders.DeliveryZoneID = Zones1.ZoneID
      LEFT JOIN ServiceTypes ON FinalizedOrders.ServiceTypeID = ServiceTypes.ServiceTypeID
      LEFT JOIN VehicleTypes ON FinalizedOrders.VehicleTypeID = VehicleTypes.VehicleTypeID
      INNER JOIN FinalizedOrdersInvoices ON FinalizedOrdersInvoices.OrderNo = FinalizedOrders.OrderNo
      WHERE FinalizedOrdersInvoices.InvoiceNumber = (Select Distinct InvoiceNumber From (Select InvoiceNumber, orderno From finalizedordersinvoices
      Where Invoicenumber = (Select top 1 invoicenumber from finalizedordersinvoices i inner join finalizedorders o on o.orderno= i.orderno  
      where Accountnumber in (451)   order by Invoicenumber desc)) foi left join finalizedorders fo on fo.orderno  = foi.orderno
      WHERE AccountNumber in (451))

Any help is appreciated.
ASKER CERTIFIED SOLUTION
Avatar of Jared_S
Jared_S

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of W.E.B
W.E.B

ASKER

Thanks  Jared_S<br />This worked.