antonio.estima 4150 (9/25/2012)
... Now, the filter "where SUBSTRING (nomeint, 1,1) = 'P' ", which is in subquery is precisely to bring unit not equals "PA" ...
This doesn't make sense.
SUBSTRING (nomeint, 1,1) = 'P' would include "PA"
Here's the query, reformatted:
select
w.Unidade,
w.Data,
w.Interface,
w.NomeInterface
from (
select
c.unidade,
c.Data,
c.Interface,
c.NomeInterface
from cad_interface c
inner join sam.dbo.Unidade u
on (c.unidade = u.codigounidade)
where left(interface,6) = 'Inform'
and unidade = 999
and data between 20120701 and 20120702
) as w
left outer join (
select
dataint Data,
left(nomeint,11) COLLATE Latin1_General_CI_AS 'Interface',
SUBSTRING(nomeint,4,2) Unidade
from openquery(BS,'select * from PDTABLE')
where SUBSTRING(nomeint,1,1) = 'P'
) as b
on (w.data= b.data
and w.unidade= b.Unidade
and w.nomeinterface= left(b.interface,8))
order by w.data, w.unidade
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden