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

asked on

SQL 2008

Hello,
can you please help me separate the taxes into different Columns.
here is my code.

SELECT
      CONVERT(varchar, FO.OrderDate, 1)  AS [OrderDate],
      CONVERT(varchar,Weight) AS [Weight],
      Replace (Reference,  ',', '') AS[Reference],
    CONVERT(varchar,BaseAmount) as [BaseAmount],
    CONVERT(varchar,WeightAmount) as [WeightAmount],
    CONVERT(varchar,WaitingTimeAmount) as [WaitingTimeAmount],
    CONVERT(varchar,AfterHoursAmount) as [AfterHoursAmount],
      CONVERT(varchar,FuelAmount) as [FuelAmount],
    CONVERT(varchar,SubTotalAmount) as [SubTotalAmount],
    CONVERT(varchar,Tax1Name) AS [Tax1Name],    
    CONVERT(varchar,Tax1Amount) AS [Tax1],
    CONVERT(varchar,Tax2Name) AS [Tax2Name],    
    CONVERT(varchar,Tax2Amount) AS [Tax2],
    CONVERT(varchar,TotalAmount) AS [TotalCost],
      CONVERT(varchar,InvoiceNumber) AS [InvoiceNumber],
      CONVERT(varchar,FO.OrderNo) AS [OrderNumber],
      Replace (PickupCompanyName + '_ '+ PickupStreet,  ',', '') AS [FromAddress],
      PickupCity AS [FromCity],
      PickupProvince AS [FromProv],
      PickupPostalCode AS [FromPostalCode],
      Replace (DeliveryCompanyName + '_ ' + DeliveryStreet,  ',', '')   AS [ToAddress],
      DeliveryCity AS [ToCity],
      DeliveryProvince AS [ToProv],
      DeliveryPostalCode AS [ToPostalCode],
      PickupContact AS [Contact]
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 (45,7428,7429,7432,7433,7865,9500,9600,9601,9602,9603,10012,10013,10015,30001)
      order by invoicenumber desc)) foi left join finalizedorders fo on fo.orderno  = foi.orderno
WHERE AccountNumber in (45,7428,7429,7432,7433,7865,9500,9600,9601,9602,9603,10012,10013,10015,30001)

Please see attached Excel, 2 sheets (now, and what I'm trying to accomplish).

Your help is very much appreciated.
Msample.xlsx
ASKER CERTIFIED SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image

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

Awesome
thanks
Avatar of W.E.B

ASKER

Great.