Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

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
0
W.E.B
Asked:
W.E.B
  • 2
1 Solution
 
David ToddSenior DBACommented:
Hi,

Sample code

select
    blah
    , blah
    , case Tax1Name
        when 'GST' then Tax1Amount
        else 0
    end as GST
    , case Tax1Name
        when 'HST_ON' then Tax1Amount
        else 0
    end as HST_ON
    , case Tax1Name
        when 'HST_NS'then Tax1Amount
        else 0
    end as HST_NS
    , case Tax1Name
        when 'HST_BC' then Tax1Amount
        else 0
    end as HST_BC
    , case Tax2Name
        when 'TVQ' then Tax2Amount
        else 0
    end as TVQ
from ...

Open in new window

HTH
  David
0
 
W.E.BAuthor Commented:
Awesome
thanks
0
 
W.E.BAuthor Commented:
Great.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now