CODEWITHSUNDEEP
sqlwhere-clause
Taniq
Taniq

Reputation: 178

Where clause to restrict rows which are fully matched, show rows which are partially matched

I have the following SQL query:

    SELECT 
        job.EngineeringJobNumber, 
        CASE 
           WHEN line.PMVoucherNumber IS NULL THEN 0 
                                             ELSE 1 
        END AS JobLinesMatched
    FROM 
        Request_HDR hdr
    JOIN 
        Request_LINE line ON hdr.RequestId = line.RequestId
    JOIN 
        EngineeringJob job ON hdr.EngineeringJobId = job.EngineeringJobId
    JOIN 
        GL00105 gl ON line.AccountIndex = gl.ACTINDX
    WHERE 
        line.VendorID = 'Vendor' AND job.ApprovalStatus = 5

Which returns this:

EngineeringJobNumber JobLinesMatched
JOB00023 0
JOB00023 0
JOB00023 1
JOB00023 0
JOB00023 0
JOB00011 1
JOB00011 1
JOB00011 1
JOB00011 1
JOB00011 1

What I'm trying to achieve is to restrict rows where the job lines are fully matched, i.e. all rows for that job are 1.

EDIT: I still need to be able view all the rows for a job which is not fully matched, i.e. all 5 rows for JOB0023.

I'm thinking I possibly need a HAVING clause perhaps? Any help appreciated.

Upvotes: 0

Views: 28

Answers (1)

Popeye
Popeye

Reputation: 35920

You can use analytical function as follows:

select EngineeringJobNumber, JobLinesMatched from
(SELECT job.EngineeringJobNumber,
        CASE WHEN line.PMVoucherNumber IS NULL THEN 0 ELSE 1 END AS JobLinesMatched
        count(CASE WHEN line.PMVoucherNumber is NULL then 0 END) 
            over (partition by job.EngineeringJobNumber) as sm
    FROM Request_HDR hdr
    JOIN Request_LINE line ON hdr.RequestId = line.RequestId
    JOIN EngineeringJob job ON hdr.EngineeringJobId = job.EngineeringJobId
    JOIN GL00105 gl ON line.AccountIndex = gl.ACTINDX
    WHERE line.VendorID = 'Vendor' AND job.ApprovalStatus = 5) t
 WHERE sm > 0

Upvotes: 1

Related Questions