Query to find three instances the same in one column, but must be have three different results in an-CodePudding

I have a table like the following:

InspectDate | Serial Number | Reference | Error | PartNumber

I need to find the data of errors that occurred in the last 10 days. I can get that, but then I need to find only those problems that occurred on the same reference, but only if they happen to be on three or more different serial numbers.

Please let me know if I need to provide any more info. I have tried using count and filtering by those with more than 3, but that only shows me any one serial number that has more than three errors on that reference.

Sample Data:

    InspectDate     SerialNumber    Reference   Error   PartNumber
Oct 12 2021  1:58PM 50012       A21     1   PL2-001
Oct 12 2021  3:22PM 50013       A21     1   PL2-001
Oct 12 2021  5:59PM 50062       A21     1   PL2-001
Oct 18 2021 11:24AM 50071       A21     1   PL2-001
Oct 18 2021 12:20PM 50071       A21     2   PL2-001
Oct 18 2021 12:36PM 50071       A21     3   PL2-001
Oct 12 2021  5:59PM 50055       B44     5   AL1-440
Oct 18 2021 11:19AM 50062       B72     1   AL1-660
Oct 18 2021 11:22AM 50071       B72     2   AL1-660
Oct 12 2021  5:39PM 50047       B83     5   AL1-550
Oct 12 2021  3:03PM 50013       V310        2   PL3-010
Oct 18 2021 12:00PM 50071       V310        2   PL3-010
Oct 18 2021 12:37PM 50098       V310        4   PL3-010

Expected Results:

InspectDate     SerialNumber    Reference   Error   PartNumber
Oct 12 2021 1:58PM  50012       A21     1   PL2-001
Oct 12 2021 3:22PM  50013       A21     1   PL2-001
Oct 12 2021 5:59PM  50062       A21     1   PL2-001
Oct 18 2021 11:24AM 50071       A21     1   PL2-001
Oct 12 2021 3:03PM  50013       V310        2   PL3-010
Oct 18 2021 12:00PM 50071       V310        2   PL3-010
Oct 18 2021 12:37PM 50098       V310        4   PL3-010

Tempted Code:

Select (all columns), COUNT() AS Instances From (Table) 
Where InspectDate >= DATEADD(day, -10, GETDATE()) 
GROUP BY (all columns) 
HAVING COUNT() >= 3 
Order by CAST (inspectdate as datetime) DESC

CodePudding user response：

What you need here is a windowed COUNT(DISTINCT. Unfortuantely, SQL Server does not allow COUNT(DISTINCT as a window function.

But we can simulate it using DENSE_RANK and MAX, both as window functions

WITH Ranked AS (
    SELECT *,
      rn = DENSE_RANK() OVER (PARTITION BY Reference ORDER BY SerialNumber)
    FROM [Table]
    WHERE InspectDate >= DATEADD(day, -10, GETDATE()) 
),
DistinctCount AS (
    SELECT *,
      maxrn = MAX(rn) OVER (PARTITION BY Reference)
    FROM Ranked
)
SELECT *
FROM DistinctCount
WHERE maxrn >= 3;