2

I am trying to figure out how to filter a data set from SQL Server.

DECLARE @OUTPUT AS TABLE (FILTER INT,PART INT,LOT INT)
INSERT INTO @OUTPUT (FILTER,PART,LOT) VALUES
(1,1377882157,1379436702),
(1,1377882157,1382038239),
(1,1377882157,1382038531),
(3,1373463498,1380792160),
(3,1377882157,1379436702),
(4,1373638746,1382475541),
(4,1377882157,1379436702),
(5,1373463498,1380792160),
(5,1373463959,1380792272),
(5,1373464034,1380791899),
(5,1373638746,1379326622),
(5,1373638746,1382475541),
(5,1373638756,1383222295),
(5,1377882157,1379436702),
(5,1377882157,1382038239),
(5,1377882157,1382038531),
(5,1377882159,1383658054),
(8,1373638746,1379326622),
(8,1377882157,1379436702)

I am looking for PART & LOT that exists in every filter group. Filter groups are dynamically generated based on user selection.

With data provided would only return 1 item (e.g. 1377882157,1379436702) as it is the only one that exists in all filter groups.

This is what I have right now...was wondering if there is a better way to do it?

DECLARE @FILTER_CNT INT
SET @FILTER_CNT = 5

SELECT
    F_PART,
    F_LOT
FROM @FILTERED_LOTS
GROUP BY F_PART,F_LOT
HAVING COUNT(FILTER) = @FILTER_CNT

A method where I don't have to define FILTER_CNT is what I am thinking.

peterm
  • 91,357
  • 15
  • 148
  • 157
jlimited
  • 685
  • 2
  • 11
  • 20

2 Answers2

2

You can replace @FILTER_CNT with a subquery 

SELECT PART, LOT
  FROM output
 GROUP BY PART, LOT
HAVING COUNT(FILTER) = 
(
  SELECT COUNT(DISTINCT FILTER) 
    FROM output
)

Output:

|       PART |        LOT |
|------------|------------|
| 1377882157 | 1379436702 |

Here is SQLFiddle demo

peterm
  • 91,357
  • 15
  • 148
  • 157
0

--DECLARE @FILTER_CNT INT --SET @FILTER_CNT = 5

use this instead

SELECT
    PART,
    LOT,count(FILTER) as FILTER_total_count
FROM @OUTPUT
GROUP BY PART,LOT
HAVING COUNT(FILTER)=(select count(distinct FILTER) from @OUTPUT)
vhadalgi
  • 7,027
  • 6
  • 38
  • 67