Shanmuga Raj (3/20/2013)

---

Getting error :

Conversion failed when converting the varchar value ' | | |0|0|0' to data type int.

ChrisM@Work (3/20/2013)

---

Depending upon existing indexes, this could be a significant improvement:

DROP TABLE #fact_sales

CREATE TABLE #fact_sales (agent_id INT, product_code INT, product_id VARCHAR(5))

INSERT INTO #fact_sales (agent_id, product_code, product_id)

VALUES

(1,1,'1'),

(1,1,'2'),

(1,1,'3'),

(1,1,'4'),

(1,1,'5'),

(2,3,'1'),

(2,3,'1'),

(3,2,'1'),

(3,2,'1'),

(4,1,'1'),

(4,1,'2')

CREATE CLUSTERED INDEX cx_Everything ON #fact_sales (Product_ID, agent_id, product_code)

-- existing version

SELECT product_id

FROM #fact_sales

WHERE (agent_id=1 AND product_code in (1))

AND product_id IN (

SELECT product_id

FROM #fact_sales

WHERE (agent_id=2 AND product_code in (3))

AND product_id IN (

SELECT product_id

FROM #fact_sales

WHERE (agent_id=3 AND product_code in (2))

AND product_id IN (

SELECT product_id

FROM #fact_sales

WHERE (agent_id=4 AND product_code in (1))

)

)

)

-- alternative version

SELECT Product_ID

FROM (

SELECT f.Product_ID, agent_id, product_code

FROM #fact_sales f

WHERE (agent_id = 1 AND product_code = 1)

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