Hi folks,
I'm hoping for some help with a query please. I have some Outbound (From) and Inbound (To) string pairs i.e. ABC (From) DEF (To) with a reversed pair following it i.e. DEF (From) ABC (To). They have sequential Numbers grouped by a Date.
My table has data that looks something like this...
Date Number From To
01/08/2019 100 ABC DEF
01/08/2019 101 DEF ABC
01/08/2019 102 ABC GHI
01/08/2019 103 GHI ABC
01/08/2019 116 ABC JKL
01/08/2019 117 JKL ABC
01/08/2019 306 ABC MNO
01/08/2019 308 ABC DEF
01/08/2019 309 DEF ABC
01/08/2019 328 ABC ABC
I am trying to return only records that are in Bold that do not have a pairing like the other rows do i.e. 306 ABC MNO has no 307 MNO ABC and 328 ABC ABC is invalid.
I have been looking at LEAD and LAG but can't find an example that fits my challenge.
Is anyone able to offer some advice please?
Thank you.
hi
i tried to do this ... hope i am correct ..
if it helps great 
drop create data ...
drop table #data
go
create table #data
(
Date1 date,
Number1 int,
From1 varchar(100),
To1 varchar(100)
)
go
insert into #data select'01/08/2019',100,'ABC','DEF'
insert into #data select'01/08/2019',101,'DEF','ABC'
insert into #data select'01/08/2019',102,'ABC','GHI'
insert into #data select'01/08/2019',103,'GHI','ABC'
insert into #data select'01/08/2019',116,'ABC','JKL'
insert into #data select'01/08/2019',117,'JKL','ABC'
insert into #data select'01/08/2019',306,'ABC','MNO'
insert into #data select'01/08/2019',308,'ABC','DEF'
insert into #data select'01/08/2019',309,'DEF','ABC'
insert into #data select'01/08/2019',328,'ABC','ABC'
go
select * from #data
go
SQL ...
SELECT b.*
FROM (SELECT from1,
to1
FROM #data
EXCEPT
SELECT to1,
from1
FROM #data) a
JOIN #data b
ON a.from1 = b.from1
AND a.to1 = b.to1
1 Like
Thanks very much. I added the date to the SQL and it worked a treat!
SELECT Date1, Number1, From1, To1
FROM (
SELECT *, LEAD(From1, 1) OVER(ORDER BY Number1) AS From1_Next
FROM #data
) AS query1
WHERE To1 <> From1_Next OR From1_Next IS NULL
