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,WeightAmou nt) as [WeightAmount],
CONVERT(varchar,WaitingTim eAmount) as [WaitingTimeAmount],
CONVERT(varchar,AfterHours Amount) as [AfterHoursAmount],
CONVERT(varchar,FuelAmount ) as [FuelAmount],
CONVERT(varchar,SubTotalAm ount) as [SubTotalAmount],
CONVERT(varchar,Tax1Name) AS [Tax1Name],
CONVERT(varchar,Tax1Amount ) AS [Tax1],
CONVERT(varchar,Tax2Name) AS [Tax2Name],
CONVERT(varchar,Tax2Amount ) AS [Tax2],
CONVERT(varchar,TotalAmoun t) AS [TotalCost],
CONVERT(varchar,InvoiceNum ber) 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,78 65,9500,96 00,9601,96 02,9603,10 012,10013, 10015,3000 1)
order by invoicenumber desc)) foi left join finalizedorders fo on fo.orderno = foi.orderno
WHERE AccountNumber in (45,7428,7429,7432,7433,78 65,9500,96 00,9601,96 02,9603,10 012,10013, 10015,3000 1)
Please see attached Excel, 2 sheets (now, and what I'm trying to accomplish).
Your help is very much appreciated.
Msample.xlsx
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
CONVERT(varchar,WeightAmou
CONVERT(varchar,WaitingTim
CONVERT(varchar,AfterHours
CONVERT(varchar,FuelAmount
CONVERT(varchar,SubTotalAm
CONVERT(varchar,Tax1Name) AS [Tax1Name],
CONVERT(varchar,Tax1Amount
CONVERT(varchar,Tax2Name) AS [Tax2Name],
CONVERT(varchar,Tax2Amount
CONVERT(varchar,TotalAmoun
CONVERT(varchar,InvoiceNum
CONVERT(varchar,FO.OrderNo
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,78
order by invoicenumber desc)) foi left join finalizedorders fo on fo.orderno = foi.orderno
WHERE AccountNumber in (45,7428,7429,7432,7433,78
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great.
ASKER
thanks