I am trying to create a query that will retrieve the Average Cost field from the inventory valuation summary and combine it with price/cost data from the ItemInventory and ItemService tables. I can perform these 2 queries separately into 2 separate excel sheets, then use excel functions to combine them, but I would love to have a dedicated query to do this.
The 2 queries I'm using are:
Valuation summary - sp_report InventoryValuationSummary show Blank, AverageCost (blank is where the part number goes in this report)
Item Queries - SELECT ItemInventory.Name AS PartNum, ItemInventory.SalesDesc, ItemInventory.SalesPrice AS Price, ItemInventory.PurchaseCost AS Cost, ItemInventory.SalesTaxCodeRefFullName AS Taxable FROM ItemInventory UNION SELECT ItemService.Name AS PartNum, ItemService.SalesOrPurchaseDesc AS SalesDesc, ItemService.SalesOrPurchasePrice AS Price, ItemService.SalesAndPurchasePurchaseCost AS Cost, ItemService.SalesTaxCodeRefFullName AS Taxable FROM ItemService
I tried the following:
SELECT (ItemInventory.Name AS PartNum, ItemInventory.SalesDesc, ItemInventory.SalesPrice AS Price, ItemInventory.PurchaseCost AS Cost, ItemInventory.SalesTaxCodeRefFullName AS Taxable FROM ItemInventory UNION SELECT ItemService.Name AS PartNum, ItemService.SalesOrPurchaseDesc AS SalesDesc, ItemService.SalesOrPurchasePrice AS Price, ItemService.SalesAndPurchasePurchaseCost AS Cost, ItemService.SalesTaxCodeRefFullName AS Taxable FROM ItemService) AS t1 INNER JOIN sp_report InventoryValuationSummary show Blank, AverageCost ON t1.PartNum=InventoryValuationSummary.Blank
but got a lexical error with the ( character.
Is it possible to join these into a single query?
|