Exclude a Cost Center

Question

User1717836741 posted

I have a table that has multiple records of data collection data based on processes used on a job

These processes are assigned to a cost center in another table

When a user complete a process they can up a value in a COMPLETE field indicating that the process is complete, not complete or Cost Center complete

meaning no more work is required in cost center.

I want to exclude a cost enter from the results if any ONE of the records in the data collection table has a C in the complete field.

 bbjobcst holds the data collection records.

ssproces hold the process number and cost center number

This does not work:

SELECT distinct

   dbo.BBJOBCST.LJOB,
    dbo.BBJOBCST.PARTNO,
    dbo.BBJOBCST.COMPLETE,
    dbo.SSPROCES.DEPTNO

FROM dbo.BBJOBCST

INNER JOIN dbo.SSPROCES ON dbo.BBJOBCST.PROCNO = dbo.SSPROCES.PROCNO

WHERE (dbo.BBJOBCST.LJOB > 0)
    AND NOT EXISTS (
        SELECT  COSTCODE
        FROM BBJOBCST AS bbjc
        Join SSPROCES SSP on bbjc.PROCNO=ssp.PROCNO
        WHERE bbjobcst.LJOB = bbjc.LJOB
            AND ssproces.PROCNO = bbjc.PROCNO
            and bbjc.partno=bbjobcst.partno
            and ssp.DEPTNO=ssproces.deptno
            AND bbjc.COMPLETE = 'C'
        )

Any suggestions

Answer 1

User-2057865890 posted

Hi Ruthless23,

SELECT distinct
dbo.BBJOBCST.LJOB,
dbo.BBJOBCST.PARTNO,
dbo.BBJOBCST.COMPLETE,
dbo.SSPROCES.DEPTNO
FROM dbo.BBJOBCST AS bbjc
INNER JOIN dbo.SSPROCES as ssp ON bbjc.PROCNO = ssp.PROCNO
WHERE bbjc.LJOB > 0
AND bbjc.COMPLETE != 'C'

Best Regards,

Chris