Multiple criteria happens

Transact-SQL
1

Sorry, sort of a beginner here

Doc_NO     PartNumber       Line_Type
12345         CH92                01
12345         10-2                  09
12346         CH92                01
12347         CH92                15
12347         10-2                  09

I need a script where I can count how many times:

Doc_No had a PartNumber = 'CH92' and was a Line_type '01' or Line_type '15'
and also had PartNumber = '10-2' in Line_type '09'

The example above answer would be 2.

2 
SELECT COUNT(Doc_NO) AS Count
FROM (
    SELECT Doc_NO
    FROM table_name
    GROUP BY Doc_NO
    HAVING MAX(CASE WHEN PartNumber = 'CH92' AND Line_type IN ('01', '15') THEN 1 ELSE 0 END) = 1 AND
        MAX(CASE WHEN PartNumber = '10-2' AND Line_type IN ('09') THEN 1 ELSE 0 END) = 1
) AS derived
2 Likes
3

Thank you

4

Hello also you could do this
SELECT PartNumber, Line_Type, COUNT(PartNumber)
FROM your_table
GROUP BY Partnumber,Line_Type

5

Btw, for efficiency only, you could add a WHERE clause to the inner query:

SELECT COUNT(Doc_NO) AS Count
FROM (
    SELECT Doc_NO
    FROM table_name
    WHERE PartNumber IN ('10-2', 'CH92') AND Line_type IN ('01', '09', '15')
    GROUP BY Doc_NO
    HAVING MAX(CASE WHEN PartNumber = 'CH92' AND Line_type IN ('01', '15') THEN 1 ELSE 0 END) = 1 AND
        MAX(CASE WHEN PartNumber = '10-2' AND Line_type IN ('09') THEN 1 ELSE 0 END) = 1
) AS derived
6

Ok, I guess what I was trying to do will not work. I assumed if I kept the question easy, I could get an answer and then edit it to fit what I actually need but the way the answer is built, I can't figure it out on my own.

What I actually need is this.
Count the Doc_No that have partnumber '10-38' as line_type 01 or 15 and also has a line_type 9 on the same Doc_No but the partnumber in Line_type 9 cannot be partnumber 'CH92' or 'CH93'

SELECT COUNT(Doc_NO) AS Count
FROM (
    SELECT Doc_NO
    FROM INV_LINE
    WHERE (PartNumber = '10-38' AND Line_type in ('01','15'))
    and not PartNumber in ('CH92','CH93') AND LINE_TYPE = '09'

Thank you for the help and I'm sorry I was not clear in my first question.

7 
SELECT COUNT(Doc_NO) AS Count
FROM (
    SELECT Doc_NO
    FROM table_name
    GROUP BY Doc_NO
    HAVING MAX(CASE WHEN PartNumber = '10-38' AND Line_type IN ('01', '15') THEN 1 ELSE 0 END) = 1 AND
        MAX(CASE WHEN PartNumber IN ('CH92', 'CH93') AND Line_type IN ('09') THEN 1 ELSE 0 END) = 0
) AS derived