Hello. I would like to ask if anyone can help me with custom SQL script?

My aim is to get:

CategoryName
Order Date,

OrderId,

Source,

SubSource,

Billing Country,

Payment Method,

Currency,

CountryTaxRate,

Postage Costs,

Subtotal,

Subtotal IncShipping,

PostageCostExtra,

Tax,

Total Charge,

Qty,

Item price per unit,

SKU,

Description,

Purchasing Price.

I made this script, but I don't know how to get 'Category' column. Take a look >

SELECT CONVERT(VARCHAR(10), o.dReceievedDate, 103) AS 'Order Date', o.nOrderId  AS 'Order ID', oi.ItemNumber AS 'Item ID', 'SKU' = CASE WHEN pis.pkStockItemID IS NOT  NULL THEN pis.ItemNumber WHEN si.pkStockItemID IS NOT NULL THEN si.ItemNumber  WHEN oi.ChannelSKU IS NOT NULL THEN oi.ChannelSKU ELSE 'SKU Not Supplied' END,  'Product Name' = CASE WHEN pis.pkStockItemID IS NOT NULL THEN pis.ItemTitle WHEN  si.pkStockItemID IS NOT NULL THEN si.ItemTitle WHEN sis.cItemName IS NOT NULL  THEN sis.cItemName ELSE 'Product Name Not Supplied' END,  'Currency' = CASE  o.cCurrency WHEN '' THEN 'GBP' WHEN '$' THEN 'USD' WHEN '' THEN 'EUR' WHEN ''  THEN 'GBP' ELSE o.cCurrency END, oi.fPricePerUnit AS 'Product Price', oi.nQty AS  'Quantity', o.fPostageCost AS 'Carriage Amount', ISNULL(ROUND((oi.fPricePerUnit / 100) *  oi.LineDiscount,2), 0) as 'Discount', ISNULL(b.AccountName, '') AS 'Payment Method',  ps.PostalServiceName AS 'Shipping Method', 'Billing Name' =  REPLACE(ISNULL(oia.BillingName,o.cFullName),'"',''), 'Billing Company Name' =  REPLACE(ISNULL(oia.BillingCompany,o.Company),'"',''), 'Billing Address 1' =  REPLACE(ISNULL(oia.BillingAddress1, o.Address1),'"',''), 'Billing Address 2' =  REPLACE(ISNULL(oia.BillingAddress2, o.Address2),'"',''), 'Billing Address 3' =  REPLACE(ISNULL(oia.BillingTown, o.Town),'"',''), 'Billing Address 4' =  REPLACE(ISNULL(oia.BillingRegion, o.Region),'"',''), 'Billing Address 5' =  REPLACE(ISNULL(oia.BillingPostcode, o.cPostCode),'"',''), 'Billing Country' =  ISNULL(oia.BillingCountryName, lc.cCountry), 'Billing Country Code' = CASE  lc.cCountryCode WHEN 'UNK' THEN 'GB' ELSE  isnull(billcountry.cCountryCode,lc.cCountryCode) END, o.cEmailAddress AS 'Customer  Email', o.BuyerPhoneNumber AS 'Customer Telephone', o.cFullName AS 'Shipping Name',  REPLACE(o.Company,'"','') AS 'Shipping Company Name', REPLACE(o.Address1,'"','') AS  'Shipping Address 1', REPLACE(o.Address2,'"','') AS 'Shipping Address 2',  REPLACE(o.Town,'"','') AS 'Shipping Address 3', REPLACE(o.Region,'"','') AS 'Shipping  Address 4', REPLACE(o.cPostCode,'"','') AS 'Shipping Address 5', lc.cCountry AS 'Shipping  Country', lc.cCountryCode AS 'Shipping Country Code', 'Order Status' = CASE o.nStatus  WHEN '1' THEN 'Complete' END, o.Source + ' ' + o.SubSource AS 'Marketplace'  FROM  [Order]  o INNER JOIN OrderItem oi on oi.fkOrderID = o.pkOrderID inner join StockItems sis  on sis.pkStockID = oi.fkStockID left outer join StockItem si on sis.fkStockControlStockItemId =  si.pkStockItemID left outer join StockItem pis on pis.pkStockItemID =  oi.fkStockItemId_processed INNER JOIN PostalServices ps on o.fkPostalServiceId =  ps.pkPostalServiceId INNER JOIN ListCountries lc on o.fkCountryId = lc.pkCountryId left  outer join Order_AdditionalInfo oia on oia.fkOrderId = o.pkOrderID left outer join  Accounting_Bank b on b.pkBankId = o.fkBankId left outer join (SELECT  cCountry,cCountryCode=MAX(cCountryCode) FROM ListCountries WHERE  bLogicalDelete=0 GROUP BY cCountry) as billcountry on billcountry.cCountry =  oia.BillingCountryName WHERE (oi.fkCompositeParentRowId = '00000000-0000-0000-0000-000000000000' or  oi.fkCompositeParentRowId is null)and o.nStatus = 1 and (o.dReceievedDate BETWEEN  DATEADD(M,-4,GetDATE()) and GETDATE()) AND o.dProcessedOn IS NOT NULL AND  o.bProcessed = 1    ORDER BY o.dReceievedDate, o.nOrderId    

____________________________

*Category is located in [StockItem] si

I will appreciate any help.  Thank you.

 'pis.CategoryId' will get you the categoryid then you can join this to the correct table to get category name using 'LEFT OUTER JOIN ProductCategories t on pis.CategoryId = t.CategoryId'

SELECT  t.CategoryName, CONVERT(VARCHAR(10), o.dReceievedDate, 103) AS 'Order Date', o.nOrderId  AS 'Order ID', oi.ItemNumber AS 'Item ID', 'SKU' = CASE WHEN pis.pkStockItemID IS NOT  NULL THEN pis.ItemNumber WHEN si.pkStockItemID IS NOT NULL THEN si.ItemNumber  WHEN oi.ChannelSKU IS NOT NULL THEN oi.ChannelSKU ELSE 'SKU Not Supplied' END,  'Product Name' = CASE WHEN pis.pkStockItemID IS NOT NULL THEN pis.ItemTitle WHEN  si.pkStockItemID IS NOT NULL THEN si.ItemTitle WHEN sis.cItemName IS NOT NULL  THEN sis.cItemName ELSE 'Product Name Not Supplied' END,  'Currency' = CASE  o.cCurrency WHEN '' THEN 'GBP' WHEN '$' THEN 'USD' WHEN '' THEN 'EUR' WHEN ''  THEN 'GBP' ELSE o.cCurrency END, oi.fPricePerUnit AS 'Product Price', oi.nQty AS  'Quantity', o.fPostageCost AS 'Carriage Amount', ISNULL(ROUND((oi.fPricePerUnit / 100) *  oi.LineDiscount,2), 0) as 'Discount', ISNULL(b.AccountName, '') AS 'Payment Method',  ps.PostalServiceName AS 'Shipping Method', 'Billing Name' =  REPLACE(ISNULL(oia.BillingName,o.cFullName),'"',''), 'Billing Company Name' =  REPLACE(ISNULL(oia.BillingCompany,o.Company),'"',''), 'Billing Address 1' =  REPLACE(ISNULL(oia.BillingAddress1, o.Address1),'"',''), 'Billing Address 2' =  REPLACE(ISNULL(oia.BillingAddress2, o.Address2),'"',''), 'Billing Address 3' =  REPLACE(ISNULL(oia.BillingTown, o.Town),'"',''), 'Billing Address 4' =  REPLACE(ISNULL(oia.BillingRegion, o.Region),'"',''), 'Billing Address 5' =  REPLACE(ISNULL(oia.BillingPostcode, o.cPostCode),'"',''), 'Billing Country' =  ISNULL(oia.BillingCountryName, lc.cCountry), 'Billing Country Code' = CASE  lc.cCountryCode WHEN 'UNK' THEN 'GB' ELSE  isnull(billcountry.cCountryCode,lc.cCountryCode) END, o.cEmailAddress AS 'Customer  Email', o.BuyerPhoneNumber AS 'Customer Telephone', o.cFullName AS 'Shipping Name',  REPLACE(o.Company,'"','') AS 'Shipping Company Name', REPLACE(o.Address1,'"','') AS  'Shipping Address 1', REPLACE(o.Address2,'"','') AS 'Shipping Address 2',  REPLACE(o.Town,'"','') AS 'Shipping Address 3', REPLACE(o.Region,'"','') AS 'Shipping  Address 4', REPLACE(o.cPostCode,'"','') AS 'Shipping Address 5', lc.cCountry AS 'Shipping  Country', lc.cCountryCode AS 'Shipping Country Code', 'Order Status' = CASE o.nStatus  WHEN '1' THEN 'Complete' END, o.Source + ' ' + o.SubSource AS 'Marketplace'  FROM  [Order]  o INNER JOIN OrderItem oi on oi.fkOrderID = o.pkOrderID inner join StockItems sis  on sis.pkStockID = oi.fkStockID left outer join StockItem si on sis.fkStockControlStockItemId =  si.pkStockItemID left outer join StockItem pis on pis.pkStockItemID =  oi.fkStockItemId_processed INNER JOIN PostalServices ps on o.fkPostalServiceId =  ps.pkPostalServiceId INNER JOIN ListCountries lc on o.fkCountryId = lc.pkCountryId
LEFT OUTER JOIN ProductCategories t on pis.CategoryId = t.CategoryId

 left  outer join Order_AdditionalInfo oia on oia.fkOrderId = o.pkOrderID left outer join  Accounting_Bank b on b.pkBankId = o.fkBankId left outer join (SELECT  cCountry,cCountryCode=MAX(cCountryCode)
FROM ListCountries WHERE  bLogicalDelete=0 GROUP BY cCountry) as billcountry on billcountry.cCountry =  oia.BillingCountryName WHERE (oi.fkCompositeParentRowId = '00000000-0000-0000-0000-000000000000' or  oi.fkCompositeParentRowId is null)and o.nStatus = 1 and (o.dReceievedDate BETWEEN  DATEADD(M,-4,GetDATE()) and GETDATE()) AND o.dProcessedOn IS NOT NULL AND  o.bProcessed = 1    ORDER BY o.dReceievedDate, o.nOrderId

Hope that helps?

Jim Banwell

It works!

Thank you very much, your support is greatly appreciated.

Have a good day, Jim