Get mismatched location

Question

Hi

i have a voucher table where it has a voucher with current location like below
create table #Voucher (code varchar(20), loc varchar(6))
insert into #Voucher values ('1001', '8')
insert into #Voucher values ('1002', '1')
insert into #Voucher values ('1003', '3')
insert into #Voucher values ('1004', '2')
select * from #Voucher;

and Location table have voucher table location history
create table #Location (code varchar(20), loc varchar(6), EnDate date)
insert into #Location values ('1001', '1', '2021-01-01')
insert into #Location values ('1001', '3', '2021-01-02')
insert into #Location values ('1001', '8', '2021-01-03')

insert into #Location values ('1002', '1', '2021-01-01')

insert into #Location values ('1003', '1', '2021-01-01')
insert into #Location values ('1003', '3', '2021-01-02')

insert into #Location values ('1004', '5', '2021-01-01')
insert into #Location values ('1004', '2', '2021-01-02')
select * from #Location;

i need to select the record where the voucher location start from '1'
from location table and current location from Voucher table not match with
initial location of Location table.

i need the result table like below
create table #Result (code varchar(20), Initialloc varchar(6), Currentloc varchar(6))
insert into #Result values ('1001', '1', '8')
insert into #Result values ('1003', '1', '3')

select * from #Result;

drop table #Voucher
drop table #Location
drop table #Result

Thanks

Answer 1

Check theses interpretations:

;
with CTE as
(
    select code, loc, first_value(loc) over (partition by code order by EnDate) as fl
    from #Location
    where loc like '1%'
)
select CTE.code, CTE.loc as Initialloc, v.loc as Currentloc 
from CTE 
inner join #Voucher v on v.code = CTE.code and v.loc <> fl

or:

select code, '1' as Initialloc, loc as Currentloc 
from #Voucher v
where exists (select * from #Location where code = v.code and loc = 1)
intersect
select code, '1', loc
from #Location
where loc <> 1

Answer 2

Hi @kasim mohamed ，

Please also check:

select l.code,l.loc Initialloc,v.loc Currentloc  
from #Location l  
join #Voucher v  
on l.code=v.code  
where l.loc=1 and l.loc<>v.loc  

Or:

select l.code,l.loc Initialloc,v.loc Currentloc  
from #Location l  
join #Voucher v  
on l.code=v.code  
where l.loc like '1%' and l.loc<>v.loc  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.