How can I include the subquery in this code in Report Studio. The join works fine and I get the correct results only if I leave out the subquery, but that would make sense because I do not know how to include the subquery correctly. This is what I have done so far that works. I drag a query subject to the work area,
then the join, then two new query subjects. I add some fields to query2, some to query3, I select an outer join, then I add the fields I want to display to query1. Where and how to I incorporate the subquery? Here is the SQL code:
SELECT IC.LocationId AS ICLoc,
IC.SkuID AS ICSku,
S.[Description 1] AS SDesc,
S.Size AS SSIze,
SUM(IC.Cases) AS ICCases,
SUM(IC.Bottles) AS ICBottles,
???ISNULL((SELECT TOP 1 LocationID FROM Locations
WHERE SKU = IC.SKUID AND LocationTypeID IN (1,2)),'')
AS BtlLoc,
???ISNULL((SELECT TOP 1 LocationID FROM Locations
WHERE SKU = IC.SKUID AND LocationTypeID IN (3,4,5,6,7)),'')
AS CasLoc
FROM InventoryCatalog IC
LEFT OUTER JOIN [WCS-UNITED].dbo.Sku S ON IC.SkuId = S.Sku
GROUP BY IC.Locationid, IC.SkuId, S.[Description 1], S.Size
ORDER BY ic.locationid, ic.skuid
