I'm currently using this query:

select *
from [dbo].[tf_invoices_sales] v 
inner join dbo.vw_Kalender k on v.[fin.trs.line.matchdate] = k.datum 
where  v.[fin.trs.head.code] = 'VRK' 
and [fin.trs.line.invnumber] not in 
    (select invoicenumber from [dbo].[tf_partial_payments])

The output for invoicenumber 20026 will be blank. That invoicenumber belongs to 2 offices. Office 100 with this invoicenumber is in [dbo].[tf_partial_payments]. Office 200 with this invoicenumber is not in [dbo].[tf_partial_payments]. So the desired output has to be invoicenumber 20026 with office 200

I've already tried this: concat([fin.trs.line.invnumber], [fin.trs.head.office]) not in (select concat(invoicenumber, office) from [dbo].[tf_partial_payments])

This query is way to slow.

Can someone help me?

CodePudding user response：

Use a correlated subquery instead of not in

select
  *
from
  [dbo].[tf_invoices_sales] v 
  inner join dbo.vw_Kalender k on v.[fin.trs.line.matchdate] = k.datum 
where 
  v.[fin.trs.head.code] = 'VRK'
  and not exists (
    select 1 from [dbo].[tf_partial_payments]
    where invoicenumber = v.[fin.trs.line.invnumber] and office = v.office
  )

Make sure that tf_partial_payments has an index over ([fin.trs.line.invnumber], office), i.e. both fields in the same index, not two separate indexes with one field each.

CodePudding user response：

Try left joining on it and adding a discriminator to ensure matching results are excluded.

select 
    v.*,
    k.*
from 
   [dbo].[tf_invoices_sales] v 
inner join 
    dbo.vw_Kalender k on v.[fin.trs.line.matchdate] = k.datum
left join
    [dbo].[tf_partial_payments] pp
ON pp.invoicenumber = [fin.trs.line.invnumber]
where  
    v.[fin.trs.head.code] = 'VRK' 
and pp.invoicenumber IS NULL

CodePudding user response：

select *
from [dbo].[tf_invoices_sales] v 
inner join dbo.vw_Kalender k on v.[fin.trs.line.matchdate] = k.datum 
where  v.[fin.trs.head.code] = 'VRK' and ([fin.trs.line.invnumber]  not in (select invoicenumber from [dbo].[tf_partial_payments]))