Optimizing MSSQL Queries
MSSQL, I need to pass a set of parameters to query ... is there a way I can do this in one shot!
I have 2 data sources CD and VD. I have data for products: Product A, B, C ... in data source CD I have data for products: Product 1,2,3 ... in data source VD
ProductA is the same as Product1 ProductB is the same as Product2 I need relevant data for ProductA / 1, ProductB / 2 .. from these two different sources If they had the same product names in both sources, I could group by Product name. but they have different product names.
Request 1:
SELECT CD.Description,Count(DISTINCT CD.TweetId) AS MatchingCount
FROM Result_Table_CSData CD
INNER JOIN Result_Table_VData VD
ON CD.TweetId = VD.TweetID
AND CD.Description = 'Product1'
AND VD.Description = 'ProductA'
Request 2:
SELECT CD.Description,Count(DISTINCT CD.TweetId) AS MatchingCount
FROM Result_Table_CData CD
INNER JOIN Result_Table_VData VD
ON CD.TweetId = VD.TweetID
AND CD.Description = 'Product2'
AND VD.Description = 'ProductB'
I have 100 products in the description column of both tables ... please suggest a way so that I can run one query using one query for each product.
source to share
You want to create a query that will allow you to link Product1 to ProductA, but as you said, there is no such link. I don't know how possible this is, but it looks like you will need to create this link by creating a table containing the product name / ID from the source CD and the corresponding product name / ID from the VD source.
CDDesc VDDesc
--------- --------
Product1 ProductA
Product2 ProductB
Then the request will be
SELECT CD.Description,Count(DISTINCT CD.TweetId) AS MatchingCount
FROM Result_Table_CSData CD
INNER JOIN SourceLink SL ON (CD.Description = SL.CDDesc)
INNER JOIN Result_Table_VData VD
ON CD.TweetId = VD.TweetID
AND (SL.VDDesc = VD.Description)
source to share
SELECT CD.Description,(Count(DISTINCT CD.TweetId) + Count(DISTINCT VD.TweetId)) AS MatchingCount
FROM Result_Table_CSData CD
INNER JOIN Result_Table_VData VD
ON CD.TweetId = VD.TweetID
AND charindex(REPLACE(VD.Description,'PRODUCT',''),'ABCDEFGHIJKLMNOPQRSTUVWXYZ')=CAST(REPLACE(CD.Description,'PRODUCT','') AS INT) GROUP BY CD.Description,VD.Description
source to share