Shanmuga Raj (3/20/2013)
when i run the below query i get errorConversion failed when converting the varchar value ' | | |0|0|0' to data type int.
...
Ah, that's because I used INT constants for product_code, as you did. This query sets up the constants as character type:
SELECT Product_ID
FROM (
SELECT f.Product_ID, agent_id, product_code
FROM #fact_sales f
WHERE (agent_id = 1 AND product_code = '1') -- changed constant from INT to character
OR (agent_id = 2 AND product_code = '3')
OR (agent_id = 3 AND product_code = '2')
OR (agent_id = 4 AND product_code = '1')
GROUP BY f.Product_ID, agent_id, product_code
) d
GROUP BY Product_ID
HAVING COUNT(*) = 4
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden