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?
i tried to do this ... hope i am correct ..
if it helps great
drop create data ...
drop table #data
create table #data
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'
select * from #data
FROM (SELECT from1,
FROM #data) a
JOIN #data b
ON a.from1 = b.from1
AND a.to1 = b.to1
Thanks very much. I added the date to the SQL and it worked a treat!
SELECT Date1, Number1, From1, To1
SELECT *, LEAD(From1, 1) OVER(ORDER BY Number1) AS From1_Next
) AS query1
WHERE To1 <> From1_Next OR From1_Next IS NULL