0

I have two tables, one containing a list of all orders and another containing a list of orders that have been invoiced.

I have the following query which I'm testing in SQL Server Management Studio, but it's not returning the correct data. I just want to see all orders that haven't been invoiced. E.g. Any order that's not in the invoicing table. Both the Job Number and Sub Job Number are crucial. These are known as VixJobSite and VixJobSub in the invoicing table.

SELECT
VixJobSite,
VixJobSub,
dbo.JOBCOSTING_JOBS.JOB_NUMBER,
dbo.JOBCOSTING_JOBS.SUB_JOB_NUMBER,
SummaryRef,
dbo.SERVICERECORDS_CALL_OUTS.DATE_REQUIRED

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    '\\nemesis\O\X\Invoicing Database\BackEnd\TestInvoicing.mdb';
    'admin';'',tblSummaryInfo),dbo.SERVICERECORDS_CALL_OUTS,dbo.JOBCOSTING_JOBS,dbo.SALES_ACCOUNTS 
WHERE dbo.JOBCOSTING_JOBS.INVOICE_TO_ACCOUNT = dbo.SALES_ACCOUNTS.INV_TO_ACCOUNT
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'Q'
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'P'
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'X'
AND SERVICERECORDS_CALL_OUTS.ENGINEER<> '*QR'
AND SERVICERECORDS_CALL_OUTS.DATE_LOGGED like '%2018%'
AND dbo.SERVICERECORDS_CALL_OUTS.STATUS = 'C'
AND dbo.SALES_ACCOUNTS.ACCOUNT_NUMBER = dbo.JOBCOSTING_JOBS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.INVOICE_TO =       dbo.SALES_ACCOUNTS.INV_TO_ACCOUNT
AND dbo.JOBCOSTING_JOBS.SITE_ACCOUNT_NUMBER = dbo.SERVICERECORDS_CALL_OUTS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.JOB_NUMBER = dbo.JOBCOSTING_JOBS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.SUB_JOB_NUMBER = dbo.JOBCOSTING_JOBS.SUB_JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.INVOICE_TO = dbo.JOBCOSTING_JOBS.INVOICE_TO_ACCOUNT
AND dbo.SERVICERECORDS_CALL_OUTS.JOB_NUMBER = VixJobSite COLLATE DATABASE_DEFAULT
AND dbo.SERVICERECORDS_CALL_OUTS.SUB_JOB_NUMBER = VixJobSub COLLATE DATABASE_DEFAULT
AND dbo.JOBCOSTING_JOBS.JOB_NUMBER = VixJobSite COLLATE DATABASE_DEFAULT
AND dbo.JOBCOSTING_JOBS.SUB_JOB_NUMBER = VixJobSub COLLATE DATABASE_DEFAULT
and not exists
( SELECT * 
  FROM JOBCOSTING_JOBS,SERVICERECORDS_CALL_OUTS,SALES_ACCOUNTS
  WHERE dbo.JOBCOSTING_JOBS.INVOICE_TO_ACCOUNT = dbo.SALES_ACCOUNTS.INV_TO_ACCOUNT
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'Q'
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'P'
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'X'
AND SERVICERECORDS_CALL_OUTS.ENGINEER<> '*QR'
AND SERVICERECORDS_CALL_OUTS.DATE_LOGGED like '%2018%'
AND dbo.SERVICERECORDS_CALL_OUTS.STATUS = 'C'
AND dbo.SALES_ACCOUNTS.ACCOUNT_NUMBER = dbo.JOBCOSTING_JOBS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.INVOICE_TO = dbo.SALES_ACCOUNTS.INV_TO_ACCOUNT
AND dbo.JOBCOSTING_JOBS.SITE_ACCOUNT_NUMBER = dbo.SERVICERECORDS_CALL_OUTS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.JOB_NUMBER = dbo.JOBCOSTING_JOBS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.SUB_JOB_NUMBER = dbo.JOBCOSTING_JOBS.SUB_JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.INVOICE_TO = dbo.JOBCOSTING_JOBS.INVOICE_TO_ACCOUNT
)
  • Without knowing what your data looks like, it's hard to determine what the output is supposed to be. If you could add some (anonymised) data for each table and possibly the DDL for the involved tables, then we might be able to provide you with a solution. Otherwise your question might be closed as Unclear what you're asking – John aka hot2use Jul 31 '18 at 8:33
3

I think it's a matter of using EXCEPT ? Instead of NOT EXISTS()

Simple example:

IF EXISTS ( SELECT 1 FROM sys.tables WHERE name = 'OrderLines')
    DROP TABLE [dbo].[OrderLines];

IF EXISTS ( SELECT 1 FROM sys.tables WHERE name = 'InvoiceLines')
    DROP TABLE [dbo].[InvoiceLines];

-- create tables
CREATE TABLE [dbo].[OrderLines](
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [JobNumber] [INT] NULL,
    [SubJobNumber] [INT] NULL,
 CONSTRAINT [PK_OrderLines] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[InvoiceLines](
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [JobNumber] [INT] NULL,
    [SubJobNumber] [INT] NULL,
 CONSTRAINT [PK_InvoiceLines] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)
) ON [PRIMARY]
GO

-- insert sample data
INSERT INTO dbo.OrderLines (Jobnumber, SubJobNumber) values (1,1),(1,2),(1,3),(2,1),(2,2)
INSERT INTO dbo.InvoiceLines (Jobnumber, SubJobNumber) values (1,1),(1,2),(2,1)

-- query result missing jobnumber/subjobnumber records in second table
SELECT JobNumber, SubJobNumber
FROM dbo.OrderLines
EXCEPT
SELECT JobNumber, SubJobNumber
FROM dbo.InvoiceLines

Results:

JobNumber   SubJobNumber
----------- ------------
1           3
2           2

(2 rows affected)

I would work with the key values that make the result set (Jobnumber, Subjobnumber), and then get the other data for those rows.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.